From 626cbd562368c3c7718170bcbcdc6071657cc8c5 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Mon, 14 Nov 2016 19:45:35 +0900 Subject: [PATCH 8/9] Add regression tests for passwords --- src/test/regress/expected/password.out | 102 +++++++++++++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/password.sql | 69 ++++++++++++++++++++++ 4 files changed, 173 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 0000000..42c25a1 --- /dev/null +++ b/src/test/regress/expected/password.out @@ -0,0 +1,102 @@ +-- +-- 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 +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + rolname | rolpassword +-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------- + regress_passwd1 | role_pwd1 + regress_passwd2 | md54044304ba511dd062133eb5b4b84a2a3 + regress_passwd3 | md50e5699b6911d87f17a08b8d76a21e8b8 + regress_passwd4 | AAAAAAAAAAAAAA==:4096:c32d0b9681e3d827fe5b5287c0ba9c9e276fe69e611dcc93cddd41f122b82e5b:51c60a9394db319302dc2727e2b8cb6c463a507312dbbf53a09adbc01ec276d3 + 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 'md5deaeed29b1cf796ea981d53e82cd5856'; -- encrypted with MD5 +ALTER ROLE regress_passwd3 ENCRYPTED PASSWORD 'foo'; -- encrypted with MD5 +ALTER ROLE regress_passwd4 ENCRYPTED PASSWORD 'md5deaeed29b1cf796ea981d53e82cd5856'; -- encrypted with MD5 +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + rolname | rolpassword +-----------------+------------------------------------- + regress_passwd1 | foo + regress_passwd2 | md5deaeed29b1cf796ea981d53e82cd5856 + regress_passwd3 | md5530de4c298af94b3b9f7d20305d2a1bf + regress_passwd4 | md5deaeed29b1cf796ea981d53e82cd5856 + regress_passwd5 | +(5 rows) + +-- PASSWORD val USING protocol +ALTER ROLE regress_passwd1 PASSWORD ('foo' USING 'non_existent'); +ERROR: unsupported password method non_existent +ALTER ROLE regress_passwd1 PASSWORD ('md5deaeed29b1cf796ea981d53e82cd5856' USING 'plain'); -- ok, as md5 +ALTER ROLE regress_passwd2 PASSWORD ('foo' USING 'plain'); -- ok, as plain +ALTER ROLE regress_passwd3 PASSWORD ('md5deaeed29b1cf796ea981d53e82cd5856' USING 'scram'); -- ok, as md5 +ALTER ROLE regress_passwd4 PASSWORD ('kfSJjF3tdoxDNA==:4096:c52173111c7354ca17c66ba570e230ccec51c15c9f510b998d28297f723af5fa:a55cacd2a24bc2673c3d4266b8b90fa58231a674ae1b08e02236beba283fc2d5' USING 'plain'); -- ok, as scram +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + rolname | rolpassword +-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------- + regress_passwd1 | md5deaeed29b1cf796ea981d53e82cd5856 + regress_passwd2 | foo + regress_passwd3 | md5deaeed29b1cf796ea981d53e82cd5856 + regress_passwd4 | kfSJjF3tdoxDNA==:4096:c52173111c7354ca17c66ba570e230ccec51c15c9f510b998d28297f723af5fa:a55cacd2a24bc2673c3d4266b8b90fa58231a674ae1b08e02236beba283fc2d5 + regress_passwd5 | +(5 rows) + +DROP ROLE regress_passwd1; +DROP ROLE regress_passwd2; +DROP ROLE regress_passwd3; +DROP ROLE regress_passwd4; +DROP ROLE regress_passwd5; +-- 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 8641769..772e984 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 +test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 835cf35..ce2f5a4 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 0000000..54b10df --- /dev/null +++ b/src/test/regress/sql/password.sql @@ -0,0 +1,69 @@ +-- +-- 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 +SELECT rolname, rolpassword + 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 'md5deaeed29b1cf796ea981d53e82cd5856'; -- encrypted with MD5 +ALTER ROLE regress_passwd3 ENCRYPTED PASSWORD 'foo'; -- encrypted with MD5 +ALTER ROLE regress_passwd4 ENCRYPTED PASSWORD 'md5deaeed29b1cf796ea981d53e82cd5856'; -- encrypted with MD5 +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + +-- PASSWORD val USING protocol +ALTER ROLE regress_passwd1 PASSWORD ('foo' USING 'non_existent'); +ALTER ROLE regress_passwd1 PASSWORD ('md5deaeed29b1cf796ea981d53e82cd5856' USING 'plain'); -- ok, as md5 +ALTER ROLE regress_passwd2 PASSWORD ('foo' USING 'plain'); -- ok, as plain +ALTER ROLE regress_passwd3 PASSWORD ('md5deaeed29b1cf796ea981d53e82cd5856' USING 'scram'); -- ok, as md5 +ALTER ROLE regress_passwd4 PASSWORD ('kfSJjF3tdoxDNA==:4096:c52173111c7354ca17c66ba570e230ccec51c15c9f510b998d28297f723af5fa:a55cacd2a24bc2673c3d4266b8b90fa58231a674ae1b08e02236beba283fc2d5' USING 'plain'); -- ok, as scram +SELECT rolname, rolpassword + 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; + +-- all entries should have been removed +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; -- 2.10.2