1-- 2-- Tests for password types 3-- 4-- Tests for GUC password_encryption 5SET password_encryption = 'novalue'; -- error 6ERROR: invalid value for parameter "password_encryption": "novalue" 7HINT: Available values: md5, scram-sha-256. 8SET password_encryption = true; -- ok 9SET password_encryption = 'md5'; -- ok 10SET password_encryption = 'scram-sha-256'; -- ok 11-- consistency of password entries 12SET password_encryption = 'md5'; 13CREATE ROLE regress_passwd1 PASSWORD 'role_pwd1'; 14SET password_encryption = 'on'; 15CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2'; 16SET password_encryption = 'scram-sha-256'; 17CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3'; 18CREATE ROLE regress_passwd4 PASSWORD NULL; 19-- check list of created entries 20-- 21-- The scram secret will look something like: 22-- SCRAM-SHA-256$4096:E4HxLGtnRzsYwg==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo= 23-- 24-- Since the salt is random, the exact value stored will be different on every test 25-- run. Use a regular expression to mask the changing parts. 26SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked 27 FROM pg_authid 28 WHERE rolname LIKE 'regress_passwd%' 29 ORDER BY rolname, rolpassword; 30 rolname | rolpassword_masked 31-----------------+--------------------------------------------------- 32 regress_passwd1 | md5783277baca28003b33453252be4dbb34 33 regress_passwd2 | md54044304ba511dd062133eb5b4b84a2a3 34 regress_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> 35 regress_passwd4 | 36(4 rows) 37 38-- Rename a role 39ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new; 40NOTICE: MD5 password cleared because of role rename 41-- md5 entry should have been removed 42SELECT rolname, rolpassword 43 FROM pg_authid 44 WHERE rolname LIKE 'regress_passwd2_new' 45 ORDER BY rolname, rolpassword; 46 rolname | rolpassword 47---------------------+------------- 48 regress_passwd2_new | 49(1 row) 50 51ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2; 52-- Change passwords with ALTER USER. With plaintext or already-encrypted 53-- passwords. 54SET password_encryption = 'md5'; 55-- encrypt with MD5 56ALTER ROLE regress_passwd2 PASSWORD 'foo'; 57-- already encrypted, use as they are 58ALTER ROLE regress_passwd1 PASSWORD 'md5cd3578025fe2c3d7ed1b9a9b26238b70'; 59ALTER ROLE regress_passwd3 PASSWORD 'SCRAM-SHA-256$4096:VLK4RMaQLCvNtQ==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo='; 60SET password_encryption = 'scram-sha-256'; 61-- create SCRAM secret 62ALTER ROLE regress_passwd4 PASSWORD 'foo'; 63-- already encrypted with MD5, use as it is 64CREATE ROLE regress_passwd5 PASSWORD 'md5e73a4b11df52a6068f8b39f90be36023'; 65-- This looks like a valid SCRAM-SHA-256 secret, but it is not 66-- so it should be hashed with SCRAM-SHA-256. 67CREATE ROLE regress_passwd6 PASSWORD 'SCRAM-SHA-256$1234'; 68-- These may look like valid MD5 secrets, but they are not, so they 69-- should be hashed with SCRAM-SHA-256. 70-- trailing garbage at the end 71CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz'; 72-- invalid length 73CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz'; 74SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked 75 FROM pg_authid 76 WHERE rolname LIKE 'regress_passwd%' 77 ORDER BY rolname, rolpassword; 78 rolname | rolpassword_masked 79-----------------+--------------------------------------------------- 80 regress_passwd1 | md5cd3578025fe2c3d7ed1b9a9b26238b70 81 regress_passwd2 | md5dfa155cadd5f4ad57860162f3fab9cdb 82 regress_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> 83 regress_passwd4 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> 84 regress_passwd5 | md5e73a4b11df52a6068f8b39f90be36023 85 regress_passwd6 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> 86 regress_passwd7 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> 87 regress_passwd8 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> 88(8 rows) 89 90-- An empty password is not allowed, in any form 91CREATE ROLE regress_passwd_empty PASSWORD ''; 92NOTICE: empty string is not a valid password, clearing password 93ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a'; 94NOTICE: empty string is not a valid password, clearing password 95ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4='; 96NOTICE: empty string is not a valid password, clearing password 97SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty'; 98 rolpassword 99------------- 100 101(1 row) 102 103-- Test with invalid stored and server keys. 104-- 105-- The first is valid, to act as a control. The others have too long 106-- stored/server keys. They will be re-hashed. 107CREATE ROLE regress_passwd_sha_len0 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI='; 108CREATE ROLE regress_passwd_sha_len1 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96RqwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI='; 109CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA='; 110-- Check that the invalid secrets were re-hashed. A re-hashed secret 111-- should not contain the original salt. 112SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed 113 FROM pg_authid 114 WHERE rolname LIKE 'regress_passwd_sha_len%' 115 ORDER BY rolname; 116 rolname | is_rolpassword_rehashed 117-------------------------+------------------------- 118 regress_passwd_sha_len0 | f 119 regress_passwd_sha_len1 | t 120 regress_passwd_sha_len2 | t 121(3 rows) 122 123DROP ROLE regress_passwd1; 124DROP ROLE regress_passwd2; 125DROP ROLE regress_passwd3; 126DROP ROLE regress_passwd4; 127DROP ROLE regress_passwd5; 128DROP ROLE regress_passwd6; 129DROP ROLE regress_passwd7; 130DROP ROLE regress_passwd8; 131DROP ROLE regress_passwd_empty; 132DROP ROLE regress_passwd_sha_len0; 133DROP ROLE regress_passwd_sha_len1; 134DROP ROLE regress_passwd_sha_len2; 135-- all entries should have been removed 136SELECT rolname, rolpassword 137 FROM pg_authid 138 WHERE rolname LIKE 'regress_passwd%' 139 ORDER BY rolname, rolpassword; 140 rolname | rolpassword 141---------+------------- 142(0 rows) 143 144