Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1fff35d

Browse files
committed
Add regression tests for passwords.
Michael Paquier.
1 parent818fd4a commit1fff35d

File tree

4 files changed

+169
-1
lines changed

4 files changed

+169
-1
lines changed
Lines changed: 94 additions & 0 deletions
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

Lines changed: 1 addition & 1 deletion
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

Lines changed: 1 addition & 0 deletions
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

Lines changed: 73 additions & 0 deletions
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 PASSWORDNULL;
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 rolnameLIKE'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 rolnameLIKE'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 rolnameLIKE'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 rolnameLIKE'regress_passwd%'
73+
ORDER BY rolname, rolpassword;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp