| blob | 9bb3ab2818bfbb4212a23b8cc232802c3302f5f2 |
1 --
2 -- Tests for password types
3 --
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; -- error
9 ERROR: invalid value for parameter "password_encryption": "true"
10 HINT: Available values: md5, scram-sha-256.
11 SET password_encryption = 'md5'; -- ok
12 SET password_encryption = 'scram-sha-256'; -- ok
13 -- consistency of password entries
14 SET password_encryption = 'md5';
15 CREATE ROLE regress_passwd1;
16 ALTER ROLE regress_passwd1 PASSWORD 'role_pwd1';
17 WARNING: setting an MD5-encrypted password
18 DETAIL: MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
19 HINT: Refer to the PostgreSQL documentation for details about migrating to another password type.
20 CREATE ROLE regress_passwd2;
21 ALTER ROLE regress_passwd2 PASSWORD 'role_pwd2';
22 WARNING: setting an MD5-encrypted password
23 DETAIL: MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
24 HINT: Refer to the PostgreSQL documentation for details about migrating to another password type.
25 SET password_encryption = 'scram-sha-256';
26 CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3';
27 CREATE ROLE regress_passwd4 PASSWORD NULL;
28 -- check list of created entries
29 --
30 -- The scram secret will look something like:
31 -- SCRAM-SHA-256$4096:E4HxLGtnRzsYwg==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo=
32 --
33 -- Since the salt is random, the exact value stored will be different on every test
34 -- run. Use a regular expression to mask the changing parts.
35 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
36 FROM pg_authid
37 WHERE rolname LIKE 'regress_passwd%'
38 ORDER BY rolname, rolpassword;
39 rolname | rolpassword_masked
40 -----------------+---------------------------------------------------
41 regress_passwd1 | md5783277baca28003b33453252be4dbb34
42 regress_passwd2 | md54044304ba511dd062133eb5b4b84a2a3
43 regress_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
44 regress_passwd4 |
45 (4 rows)
47 -- Rename a role
48 ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new;
49 NOTICE: MD5 password cleared because of role rename
50 -- md5 entry should have been removed
51 SELECT rolname, rolpassword
52 FROM pg_authid
53 WHERE rolname LIKE 'regress_passwd2_new'
54 ORDER BY rolname, rolpassword;
55 rolname | rolpassword
56 ---------------------+-------------
57 regress_passwd2_new |
58 (1 row)
60 ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2;
61 -- Change passwords with ALTER USER. With plaintext or already-encrypted
62 -- passwords.
63 SET password_encryption = 'md5';
64 -- encrypt with MD5
65 ALTER ROLE regress_passwd2 PASSWORD 'foo';
66 WARNING: setting an MD5-encrypted password
67 DETAIL: MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
68 HINT: Refer to the PostgreSQL documentation for details about migrating to another password type.
69 -- already encrypted, use as they are
70 ALTER ROLE regress_passwd1 PASSWORD 'md5cd3578025fe2c3d7ed1b9a9b26238b70';
71 WARNING: setting an MD5-encrypted password
72 DETAIL: MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
73 HINT: Refer to the PostgreSQL documentation for details about migrating to another password type.
74 ALTER ROLE regress_passwd3 PASSWORD 'SCRAM-SHA-256$4096:VLK4RMaQLCvNtQ==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo=';
75 SET password_encryption = 'scram-sha-256';
76 -- create SCRAM secret
77 ALTER ROLE regress_passwd4 PASSWORD 'foo';
78 -- already encrypted with MD5, use as it is
79 CREATE ROLE regress_passwd5 PASSWORD 'md5e73a4b11df52a6068f8b39f90be36023';
80 WARNING: setting an MD5-encrypted password
81 DETAIL: MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
82 HINT: Refer to the PostgreSQL documentation for details about migrating to another password type.
83 -- This looks like a valid SCRAM-SHA-256 secret, but it is not
84 -- so it should be hashed with SCRAM-SHA-256.
85 CREATE ROLE regress_passwd6 PASSWORD 'SCRAM-SHA-256$1234';
86 -- These may look like valid MD5 secrets, but they are not, so they
87 -- should be hashed with SCRAM-SHA-256.
88 -- trailing garbage at the end
89 CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz';
90 -- invalid length
91 CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz';
92 -- Changing the SCRAM iteration count
93 SET scram_iterations = 1024;
94 CREATE ROLE regress_passwd9 PASSWORD 'alterediterationcount';
95 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
96 FROM pg_authid
97 WHERE rolname LIKE 'regress_passwd%'
98 ORDER BY rolname, rolpassword;
99 rolname | rolpassword_masked
100 -----------------+---------------------------------------------------
101 regress_passwd1 | md5cd3578025fe2c3d7ed1b9a9b26238b70
102 regress_passwd2 | md5dfa155cadd5f4ad57860162f3fab9cdb
103 regress_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
104 regress_passwd4 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
105 regress_passwd5 | md5e73a4b11df52a6068f8b39f90be36023
106 regress_passwd6 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
107 regress_passwd7 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
108 regress_passwd8 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
109 regress_passwd9 | SCRAM-SHA-256$1024:<salt>$<storedkey>:<serverkey>
110 (9 rows)
112 -- An empty password is not allowed, in any form
113 CREATE ROLE regress_passwd_empty PASSWORD '';
114 NOTICE: empty string is not a valid password, clearing password
115 ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a';
116 NOTICE: empty string is not a valid password, clearing password
117 ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4=';
118 NOTICE: empty string is not a valid password, clearing password
119 SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty';
120 rolpassword
121 -------------
123 (1 row)
125 -- Test with invalid stored and server keys.
126 --
127 -- The first is valid, to act as a control. The others have too long
128 -- stored/server keys. They will be re-hashed.
129 CREATE ROLE regress_passwd_sha_len0 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI=';
130 CREATE ROLE regress_passwd_sha_len1 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96RqwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI=';
131 CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=';
132 -- Check that the invalid secrets were re-hashed. A re-hashed secret
133 -- should not contain the original salt.
134 SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
135 FROM pg_authid
136 WHERE rolname LIKE 'regress_passwd_sha_len%'
137 ORDER BY rolname;
138 rolname | is_rolpassword_rehashed
139 -------------------------+-------------------------
140 regress_passwd_sha_len0 | f
141 regress_passwd_sha_len1 | t
142 regress_passwd_sha_len2 | t
143 (3 rows)
145 -- Test that valid hashes that are too long are rejected
146 CREATE ROLE regress_passwd10 PASSWORD 'SCRAM-SHA-256$000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004096:wNFxNSk1hAXBkgub8py3bg==$65zC6E+R0U7tiYTC9+Wtq4Thw6gUDj3eDCINij8TflU=:rC1I7tcVugrHEY2DT0iPjGyjM4aJxkMM9n8WBxtUtHU=';
147 ERROR: encrypted password is too long
148 DETAIL: Encrypted passwords must be no longer than 512 bytes.
149 ALTER ROLE regress_passwd9 PASSWORD 'SCRAM-SHA-256$000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004096:wNFxNSk1hAXBkgub8py3bg==$65zC6E+R0U7tiYTC9+Wtq4Thw6gUDj3eDCINij8TflU=:rC1I7tcVugrHEY2DT0iPjGyjM4aJxkMM9n8WBxtUtHU=';
150 ERROR: encrypted password is too long
151 DETAIL: Encrypted passwords must be no longer than 512 bytes.
152 DROP ROLE regress_passwd1;
153 DROP ROLE regress_passwd2;
154 DROP ROLE regress_passwd3;
155 DROP ROLE regress_passwd4;
156 DROP ROLE regress_passwd5;
157 DROP ROLE regress_passwd6;
158 DROP ROLE regress_passwd7;
159 DROP ROLE regress_passwd8;
160 DROP ROLE regress_passwd9;
161 DROP ROLE regress_passwd_empty;
162 DROP ROLE regress_passwd_sha_len0;
163 DROP ROLE regress_passwd_sha_len1;
164 DROP ROLE regress_passwd_sha_len2;
165 -- all entries should have been removed
166 SELECT rolname, rolpassword
167 FROM pg_authid
168 WHERE rolname LIKE 'regress_passwd%'
169 ORDER BY rolname, rolpassword;
170 rolname | rolpassword
171 ---------+-------------
172 (0 rows)