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