1-- 2-- Tests for password types 3-- 4 5-- Tests for GUC password_encryption 6SET password_encryption = 'novalue'; -- error 7SET password_encryption = true; -- ok 8SET password_encryption = 'md5'; -- ok 9SET password_encryption = 'scram-sha-256'; -- ok 10 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 20-- check list of created entries 21-- 22-- The scram secret will look something like: 23-- SCRAM-SHA-256$4096:E4HxLGtnRzsYwg==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo= 24-- 25-- Since the salt is random, the exact value stored will be different on every test 26-- run. Use a regular expression to mask the changing parts. 27SELECT 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 28 FROM pg_authid 29 WHERE rolname LIKE 'regress_passwd%' 30 ORDER BY rolname, rolpassword; 31 32-- Rename a role 33ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new; 34-- md5 entry should have been removed 35SELECT rolname, rolpassword 36 FROM pg_authid 37 WHERE rolname LIKE 'regress_passwd2_new' 38 ORDER BY rolname, rolpassword; 39ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2; 40 41-- Change passwords with ALTER USER. With plaintext or already-encrypted 42-- passwords. 43SET password_encryption = 'md5'; 44 45-- encrypt with MD5 46ALTER ROLE regress_passwd2 PASSWORD 'foo'; 47-- already encrypted, use as they are 48ALTER ROLE regress_passwd1 PASSWORD 'md5cd3578025fe2c3d7ed1b9a9b26238b70'; 49ALTER ROLE regress_passwd3 PASSWORD 'SCRAM-SHA-256$4096:VLK4RMaQLCvNtQ==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo='; 50 51SET password_encryption = 'scram-sha-256'; 52-- create SCRAM secret 53ALTER ROLE regress_passwd4 PASSWORD 'foo'; 54-- already encrypted with MD5, use as it is 55CREATE ROLE regress_passwd5 PASSWORD 'md5e73a4b11df52a6068f8b39f90be36023'; 56 57-- This looks like a valid SCRAM-SHA-256 secret, but it is not 58-- so it should be hashed with SCRAM-SHA-256. 59CREATE ROLE regress_passwd6 PASSWORD 'SCRAM-SHA-256$1234'; 60-- These may look like valid MD5 secrets, but they are not, so they 61-- should be hashed with SCRAM-SHA-256. 62-- trailing garbage at the end 63CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz'; 64-- invalid length 65CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz'; 66 67SELECT 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 68 FROM pg_authid 69 WHERE rolname LIKE 'regress_passwd%' 70 ORDER BY rolname, rolpassword; 71 72-- An empty password is not allowed, in any form 73CREATE ROLE regress_passwd_empty PASSWORD ''; 74ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a'; 75ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4='; 76SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty'; 77 78-- Test with invalid stored and server keys. 79-- 80-- The first is valid, to act as a control. The others have too long 81-- stored/server keys. They will be re-hashed. 82CREATE ROLE regress_passwd_sha_len0 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI='; 83CREATE ROLE regress_passwd_sha_len1 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96RqwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI='; 84CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA='; 85 86-- Check that the invalid secrets were re-hashed. A re-hashed secret 87-- should not contain the original salt. 88SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed 89 FROM pg_authid 90 WHERE rolname LIKE 'regress_passwd_sha_len%' 91 ORDER BY rolname; 92 93DROP ROLE regress_passwd1; 94DROP ROLE regress_passwd2; 95DROP ROLE regress_passwd3; 96DROP ROLE regress_passwd4; 97DROP ROLE regress_passwd5; 98DROP ROLE regress_passwd6; 99DROP ROLE regress_passwd7; 100DROP ROLE regress_passwd8; 101DROP ROLE regress_passwd_empty; 102DROP ROLE regress_passwd_sha_len0; 103DROP ROLE regress_passwd_sha_len1; 104DROP ROLE regress_passwd_sha_len2; 105 106-- all entries should have been removed 107SELECT rolname, rolpassword 108 FROM pg_authid 109 WHERE rolname LIKE 'regress_passwd%' 110 ORDER BY rolname, rolpassword; 111