From 1fff35d872321015a87ca55042384b83f51dedcf Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Tue, 7 Mar 2017 14:25:52 +0200 Subject: [PATCH] Add regression tests for passwords. Michael Paquier. --- src/test/regress/expected/password.out | 94 ++++++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/password.sql | 73 ++++++++++++++++++++ 4 files changed, 169 insertions(+), 1 deletion(-) create mode 100644 src/test/regress/expected/password.out create mode 100644 src/test/regress/sql/password.sql diff --git a/src/test/regress/expected/password.out b/src/test/regress/expected/password.out new file mode 100644 index 0000000000..c503e43abe --- /dev/null +++ b/src/test/regress/expected/password.out @@ -0,0 +1,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, off, on. +SET password_encryption = true; -- ok +SET password_encryption = 'md5'; -- ok +SET password_encryption = 'plain'; -- ok +SET password_encryption = 'scram'; -- 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'; +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:E4HxLGtnRzsYwg==:4096:5ebc825510cb7862efd87dfa638d8337179e6913a724441dc9e888a856fbc10c:e966b1c72fad89d69aaebb156eae04edc9581286f92207c044711e79cd461bee +-- +-- 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):([a-zA-Z0-9+/]+==):(\d+):(\w+):(\w+)', '\1::\3::') 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:: + 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:VLK4RMaQLCvNtQ==:4096:3ded2376f7aafa93b1bdbd71bcc18b7d6ee50ed018029cc583d152ef3fc7d430:a6dd36dfc94c181956a6ae95f05e01b1864f0a22a2657d1de4ba84d2a24dc438'; -- client-supplied SCRAM verifier, use as it is +SET password_encryption = 'scram'; +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):([a-zA-Z0-9+/]+==):(\d+):(\w+):(\w+)', '\1::\3::') 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:: + regress_passwd5 | scram-sha-256::4096:: + 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) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 1f2fb597c4..13bf49445b 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -84,7 +84,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi # ---------- # Another group of parallel tests # ---------- -test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator large_object +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 # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 9ffceff5e0..645ab9b2dc 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -112,6 +112,7 @@ test: matview test: lock test: replica_identity test: rowsecurity +test: password test: object_address test: tablesample test: groupingsets diff --git a/src/test/regress/sql/password.sql b/src/test/regress/sql/password.sql new file mode 100644 index 0000000000..f4b3a9ac3a --- /dev/null +++ b/src/test/regress/sql/password.sql @@ -0,0 +1,73 @@ +-- +-- Tests for password verifiers +-- + +-- Tests for GUC password_encryption +SET password_encryption = 'novalue'; -- error +SET password_encryption = true; -- ok +SET password_encryption = 'md5'; -- ok +SET password_encryption = 'plain'; -- ok +SET password_encryption = 'scram'; -- 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'; +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:E4HxLGtnRzsYwg==:4096:5ebc825510cb7862efd87dfa638d8337179e6913a724441dc9e888a856fbc10c:e966b1c72fad89d69aaebb156eae04edc9581286f92207c044711e79cd461bee +-- +-- 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):([a-zA-Z0-9+/]+==):(\d+):(\w+):(\w+)', '\1::\3::') as rolpassword_masked + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + +-- Rename a role +ALTER ROLE regress_passwd3 RENAME TO regress_passwd3_new; +-- md5 entry should have been removed +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd3_new' + ORDER BY rolname, rolpassword; +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:VLK4RMaQLCvNtQ==:4096:3ded2376f7aafa93b1bdbd71bcc18b7d6ee50ed018029cc583d152ef3fc7d430:a6dd36dfc94c181956a6ae95f05e01b1864f0a22a2657d1de4ba84d2a24dc438'; -- client-supplied SCRAM verifier, use as it is + +SET password_encryption = 'scram'; +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):([a-zA-Z0-9+/]+==):(\d+):(\w+):(\w+)', '\1::\3::') as rolpassword_masked + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + +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; -- 2.40.0