2 -- Tests for password verifiers
4 -- Tests for GUC password_encryption
5 SET password_encryption = 'novalue'; -- error
6 ERROR: invalid value for parameter "password_encryption": "novalue"
7 HINT: Available values: md5, scram-sha-256.
8 SET password_encryption = true; -- ok
9 SET password_encryption = 'md5'; -- ok
10 SET password_encryption = 'scram-sha-256'; -- ok
11 -- consistency of password entries
12 SET password_encryption = 'md5';
13 CREATE ROLE regress_passwd1 PASSWORD 'role_pwd1';
14 SET password_encryption = 'on';
15 CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2';
16 SET password_encryption = 'scram-sha-256';
17 CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3';
18 CREATE ROLE regress_passwd4 PASSWORD NULL;
19 -- check list of created entries
21 -- The scram verifier will look something like:
22 -- SCRAM-SHA-256$4096:E4HxLGtnRzsYwg==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo=
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.
26 SELECT 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 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>
39 ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new;
40 NOTICE: MD5 password cleared because of role rename
41 -- md5 entry should have been removed
42 SELECT rolname, rolpassword
44 WHERE rolname LIKE 'regress_passwd2_new'
45 ORDER BY rolname, rolpassword;
47 ---------------------+-------------
51 ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2;
52 -- Change passwords with ALTER USER. With plaintext or already-encrypted
54 SET password_encryption = 'md5';
56 ALTER ROLE regress_passwd2 PASSWORD 'foo';
57 -- already encrypted, use as they are
58 ALTER ROLE regress_passwd1 PASSWORD 'md5cd3578025fe2c3d7ed1b9a9b26238b70';
59 ALTER ROLE regress_passwd3 PASSWORD 'SCRAM-SHA-256$4096:VLK4RMaQLCvNtQ==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo=';
60 SET password_encryption = 'scram-sha-256';
61 -- create SCRAM verifier
62 ALTER ROLE regress_passwd4 PASSWORD 'foo';
63 -- already encrypted with MD5, use as it is
64 CREATE ROLE regress_passwd5 PASSWORD 'md5e73a4b11df52a6068f8b39f90be36023';
65 SELECT 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
67 WHERE rolname LIKE 'regress_passwd%'
68 ORDER BY rolname, rolpassword;
69 rolname | rolpassword_masked
70 -----------------+---------------------------------------------------
71 regress_passwd1 | md5cd3578025fe2c3d7ed1b9a9b26238b70
72 regress_passwd2 | md5dfa155cadd5f4ad57860162f3fab9cdb
73 regress_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
74 regress_passwd4 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
75 regress_passwd5 | md5e73a4b11df52a6068f8b39f90be36023
78 -- An empty password is not allowed, in any form
79 CREATE ROLE regress_passwd_empty PASSWORD '';
80 NOTICE: empty string is not a valid password, clearing password
81 ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a';
82 NOTICE: empty string is not a valid password, clearing password
83 ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4=';
84 NOTICE: empty string is not a valid password, clearing password
85 SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty';
91 DROP ROLE regress_passwd1;
92 DROP ROLE regress_passwd2;
93 DROP ROLE regress_passwd3;
94 DROP ROLE regress_passwd4;
95 DROP ROLE regress_passwd5;
96 DROP ROLE regress_passwd_empty;
97 -- all entries should have been removed
98 SELECT rolname, rolpassword
100 WHERE rolname LIKE 'regress_passwd%'
101 ORDER BY rolname, rolpassword;
102 rolname | rolpassword
103 ---------+-------------