Skip to content

Commit 1fff35d

Browse files
hlinnakahlinnaka
committed
Add regression tests for passwords.
Michael Paquier.
1 parent 818fd4a commit 1fff35d

File tree

4 files changed

+169
-1
lines changed

4 files changed

+169
-1
lines changed

‎src/test/regress/expected/password.out

+94
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
--
2+
-- Tests for password verifiers
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: plain, md5, scram, off, on.
8+
SET password_encryption = true; -- ok
9+
SET password_encryption = 'md5'; -- ok
10+
SET password_encryption = 'plain'; -- ok
11+
SET password_encryption = 'scram'; -- ok
12+
-- consistency of password entries
13+
SET password_encryption = 'plain';
14+
CREATE ROLE regress_passwd1 PASSWORD 'role_pwd1';
15+
SET password_encryption = 'md5';
16+
CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2';
17+
SET password_encryption = 'on';
18+
CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3';
19+
SET password_encryption = 'scram';
20+
CREATE ROLE regress_passwd4 PASSWORD 'role_pwd4';
21+
SET password_encryption = 'plain';
22+
CREATE ROLE regress_passwd5 PASSWORD NULL;
23+
-- check list of created entries
24+
--
25+
-- The scram verifier will look something like:
26+
-- scram-sha-256:E4HxLGtnRzsYwg==:4096:5ebc825510cb7862efd87dfa638d8337179e6913a724441dc9e888a856fbc10c:e966b1c72fad89d69aaebb156eae04edc9581286f92207c044711e79cd461bee
27+
--
28+
-- Since the salt is random, the exact value stored will be different on every test
29+
-- run. Use a regular expression to mask the changing parts.
30+
SELECT rolname, regexp_replace(rolpassword, '(scram-sha-256):([a-zA-Z0-9+/]+==):(\d+):(\w+):(\w+)', '\1:<salt>:\3:<storedkey>:<serverkey>') as rolpassword_masked
31+
FROM pg_authid
32+
WHERE rolname LIKE 'regress_passwd%'
33+
ORDER BY rolname, rolpassword;
34+
rolname | rolpassword_masked
35+
-----------------+---------------------------------------------------
36+
regress_passwd1 | role_pwd1
37+
regress_passwd2 | md54044304ba511dd062133eb5b4b84a2a3
38+
regress_passwd3 | md50e5699b6911d87f17a08b8d76a21e8b8
39+
regress_passwd4 | scram-sha-256:<salt>:4096:<storedkey>:<serverkey>
40+
regress_passwd5 |
41+
(5 rows)
42+
43+
-- Rename a role
44+
ALTER ROLE regress_passwd3 RENAME TO regress_passwd3_new;
45+
NOTICE: MD5 password cleared because of role rename
46+
-- md5 entry should have been removed
47+
SELECT rolname, rolpassword
48+
FROM pg_authid
49+
WHERE rolname LIKE 'regress_passwd3_new'
50+
ORDER BY rolname, rolpassword;
51+
rolname | rolpassword
52+
---------------------+-------------
53+
regress_passwd3_new |
54+
(1 row)
55+
56+
ALTER ROLE regress_passwd3_new RENAME TO regress_passwd3;
57+
-- ENCRYPTED and UNENCRYPTED passwords
58+
ALTER ROLE regress_passwd1 UNENCRYPTED PASSWORD 'foo'; -- unencrypted
59+
ALTER ROLE regress_passwd2 UNENCRYPTED PASSWORD 'md5dfa155cadd5f4ad57860162f3fab9cdb'; -- encrypted with MD5
60+
SET password_encryption = 'md5';
61+
ALTER ROLE regress_passwd3 ENCRYPTED PASSWORD 'foo'; -- encrypted with MD5
62+
ALTER ROLE regress_passwd4 ENCRYPTED PASSWORD 'scram-sha-256:VLK4RMaQLCvNtQ==:4096:3ded2376f7aafa93b1bdbd71bcc18b7d6ee50ed018029cc583d152ef3fc7d430:a6dd36dfc94c181956a6ae95f05e01b1864f0a22a2657d1de4ba84d2a24dc438'; -- client-supplied SCRAM verifier, use as it is
63+
SET password_encryption = 'scram';
64+
ALTER ROLE regress_passwd5 ENCRYPTED PASSWORD 'foo'; -- create SCRAM verifier
65+
CREATE ROLE regress_passwd6 ENCRYPTED PASSWORD 'md53725413363ab045e20521bf36b8d8d7f'; -- encrypted with MD5, use as it is
66+
SELECT rolname, regexp_replace(rolpassword, '(scram-sha-256):([a-zA-Z0-9+/]+==):(\d+):(\w+):(\w+)', '\1:<salt>:\3:<storedkey>:<serverkey>') as rolpassword_masked
67+
FROM pg_authid
68+
WHERE rolname LIKE 'regress_passwd%'
69+
ORDER BY rolname, rolpassword;
70+
rolname | rolpassword_masked
71+
-----------------+---------------------------------------------------
72+
regress_passwd1 | foo
73+
regress_passwd2 | md5dfa155cadd5f4ad57860162f3fab9cdb
74+
regress_passwd3 | md5530de4c298af94b3b9f7d20305d2a1bf
75+
regress_passwd4 | scram-sha-256:<salt>:4096:<storedkey>:<serverkey>
76+
regress_passwd5 | scram-sha-256:<salt>:4096:<storedkey>:<serverkey>
77+
regress_passwd6 | md53725413363ab045e20521bf36b8d8d7f
78+
(6 rows)
79+
80+
DROP ROLE regress_passwd1;
81+
DROP ROLE regress_passwd2;
82+
DROP ROLE regress_passwd3;
83+
DROP ROLE regress_passwd4;
84+
DROP ROLE regress_passwd5;
85+
DROP ROLE regress_passwd6;
86+
-- all entries should have been removed
87+
SELECT rolname, rolpassword
88+
FROM pg_authid
89+
WHERE rolname LIKE 'regress_passwd%'
90+
ORDER BY rolname, rolpassword;
91+
rolname | rolpassword
92+
---------+-------------
93+
(0 rows)
94+

‎src/test/regress/parallel_schedule

+1-1
Original file line numberDiff line numberDiff line change
@@ -84,7 +84,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
8484
# ----------
8585
# Another group of parallel tests
8686
# ----------
87-
test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator large_object
87+
test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator large_object password
8888

8989
# ----------
9090
# Another group of parallel tests

‎src/test/regress/serial_schedule

+1
Original file line numberDiff line numberDiff line change
@@ -112,6 +112,7 @@ test: matview
112112
test: lock
113113
test: replica_identity
114114
test: rowsecurity
115+
test: password
115116
test: object_address
116117
test: tablesample
117118
test: groupingsets

‎src/test/regress/sql/password.sql

+73
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,73 @@
1+
--
2+
-- Tests for password verifiers
3+
--
4+
5+
-- Tests for GUC password_encryption
6+
SET password_encryption = 'novalue'; -- error
7+
SET password_encryption = true; -- ok
8+
SET password_encryption = 'md5'; -- ok
9+
SET password_encryption = 'plain'; -- ok
10+
SET password_encryption = 'scram'; -- ok
11+
12+
-- consistency of password entries
13+
SET password_encryption = 'plain';
14+
CREATE ROLE regress_passwd1 PASSWORD 'role_pwd1';
15+
SET password_encryption = 'md5';
16+
CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2';
17+
SET password_encryption = 'on';
18+
CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3';
19+
SET password_encryption = 'scram';
20+
CREATE ROLE regress_passwd4 PASSWORD 'role_pwd4';
21+
SET password_encryption = 'plain';
22+
CREATE ROLE regress_passwd5 PASSWORD NULL;
23+
24+
-- check list of created entries
25+
--
26+
-- The scram verifier will look something like:
27+
-- scram-sha-256:E4HxLGtnRzsYwg==:4096:5ebc825510cb7862efd87dfa638d8337179e6913a724441dc9e888a856fbc10c:e966b1c72fad89d69aaebb156eae04edc9581286f92207c044711e79cd461bee
28+
--
29+
-- Since the salt is random, the exact value stored will be different on every test
30+
-- run. Use a regular expression to mask the changing parts.
31+
SELECT rolname, regexp_replace(rolpassword, '(scram-sha-256):([a-zA-Z0-9+/]+==):(\d+):(\w+):(\w+)', '\1:<salt>:\3:<storedkey>:<serverkey>') as rolpassword_masked
32+
FROM pg_authid
33+
WHERE rolname LIKE 'regress_passwd%'
34+
ORDER BY rolname, rolpassword;
35+
36+
-- Rename a role
37+
ALTER ROLE regress_passwd3 RENAME TO regress_passwd3_new;
38+
-- md5 entry should have been removed
39+
SELECT rolname, rolpassword
40+
FROM pg_authid
41+
WHERE rolname LIKE 'regress_passwd3_new'
42+
ORDER BY rolname, rolpassword;
43+
ALTER ROLE regress_passwd3_new RENAME TO regress_passwd3;
44+
45+
-- ENCRYPTED and UNENCRYPTED passwords
46+
ALTER ROLE regress_passwd1 UNENCRYPTED PASSWORD 'foo'; -- unencrypted
47+
ALTER ROLE regress_passwd2 UNENCRYPTED PASSWORD 'md5dfa155cadd5f4ad57860162f3fab9cdb'; -- encrypted with MD5
48+
SET password_encryption = 'md5';
49+
ALTER ROLE regress_passwd3 ENCRYPTED PASSWORD 'foo'; -- encrypted with MD5
50+
51+
ALTER ROLE regress_passwd4 ENCRYPTED PASSWORD 'scram-sha-256:VLK4RMaQLCvNtQ==:4096:3ded2376f7aafa93b1bdbd71bcc18b7d6ee50ed018029cc583d152ef3fc7d430:a6dd36dfc94c181956a6ae95f05e01b1864f0a22a2657d1de4ba84d2a24dc438'; -- client-supplied SCRAM verifier, use as it is
52+
53+
SET password_encryption = 'scram';
54+
ALTER ROLE regress_passwd5 ENCRYPTED PASSWORD 'foo'; -- create SCRAM verifier
55+
CREATE ROLE regress_passwd6 ENCRYPTED PASSWORD 'md53725413363ab045e20521bf36b8d8d7f'; -- encrypted with MD5, use as it is
56+
57+
SELECT rolname, regexp_replace(rolpassword, '(scram-sha-256):([a-zA-Z0-9+/]+==):(\d+):(\w+):(\w+)', '\1:<salt>:\3:<storedkey>:<serverkey>') as rolpassword_masked
58+
FROM pg_authid
59+
WHERE rolname LIKE 'regress_passwd%'
60+
ORDER BY rolname, rolpassword;
61+
62+
DROP ROLE regress_passwd1;
63+
DROP ROLE regress_passwd2;
64+
DROP ROLE regress_passwd3;
65+
DROP ROLE regress_passwd4;
66+
DROP ROLE regress_passwd5;
67+
DROP ROLE regress_passwd6;
68+
69+
-- all entries should have been removed
70+
SELECT rolname, rolpassword
71+
FROM pg_authid
72+
WHERE rolname LIKE 'regress_passwd%'
73+
ORDER BY rolname, rolpassword;

0 commit comments

Comments
 (0)