On 04.10.22 17:45, Peter Eisentraut wrote:
While working on the column encryption patch, I wanted to check that
what is implemented also works in OpenSSL FIPS mode. I tried running
the normal test suites after switching the OpenSSL installation to FIPS
mode, but that failed all over the place. So I embarked on fixing that.
Attached is a first iteration of a patch.
Continuing this, we have fixed many issues since. Here is a patch set
to fix all remaining issues.
v4-0001-citext-Allow-tests-to-pass-in-OpenSSL-FIPS-mode.patch
v4-0002-pgcrypto-Allow-tests-to-pass-in-OpenSSL-FIPS-mode.patch
These two are pretty straightforward.
v4-0003-Allow-tests-to-pass-in-OpenSSL-FIPS-mode-TAP-test.patch
This one does some delicate surgery and could use some thorough review.
v4-0004-Allow-tests-to-pass-in-OpenSSL-FIPS-mode-rest.patch
This just adds alternative expected files. The question is mainly just
whether there are better ways to organize this.
v4-0005-WIP-Use-fipshash-in-brin_multi-test.patch
Here, some previously fixed md5() uses have snuck back in. I will need
to track down the origin of this and ask for a proper fix there. This
is just included here for completeness.
From 7faeec85be6d445eca21e8132b4bf151ee6f8ee2 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 5 Oct 2023 14:45:35 +0200
Subject: [PATCH v4 1/5] citext: Allow tests to pass in OpenSSL FIPS mode
citext doesn't define an md5() function, so the value of using it in
its tests is dubious. At best this shows in an indirect way that the
cast from citext to text works. Avoid the issue and remove the test.
---
contrib/citext/expected/citext.out | 9 ---------
contrib/citext/expected/citext_1.out | 9 ---------
contrib/citext/sql/citext.sql | 1 -
3 files changed, 19 deletions(-)
diff --git a/contrib/citext/expected/citext.out
b/contrib/citext/expected/citext.out
index 1c55598136..8c0bf54f0f 100644
--- a/contrib/citext/expected/citext.out
+++ b/contrib/citext/expected/citext.out
@@ -1744,15 +1744,6 @@ SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim'
AS t;
t
(1 row)
-SELECT md5( name ) = md5( name::text ) AS t FROM srt;
- t
----
- t
- t
- t
- t
-(4 rows)
-
-- pg_client_encoding() takes no args and returns name.
SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt;
t
diff --git a/contrib/citext/expected/citext_1.out
b/contrib/citext/expected/citext_1.out
index 4a979d7a0d..c5e5f180f2 100644
--- a/contrib/citext/expected/citext_1.out
+++ b/contrib/citext/expected/citext_1.out
@@ -1744,15 +1744,6 @@ SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim'
AS t;
t
(1 row)
-SELECT md5( name ) = md5( name::text ) AS t FROM srt;
- t
----
- t
- t
- t
- t
-(4 rows)
-
-- pg_client_encoding() takes no args and returns name.
SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt;
t
diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql
index b329253d37..aa1cf9abd5 100644
--- a/contrib/citext/sql/citext.sql
+++ b/contrib/citext/sql/citext.sql
@@ -574,7 +574,6 @@ CREATE TABLE caster (
SELECT ltrim('zzzytrim'::text, 'xyz'::citext) = 'trim' AS t;
SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim' AS t;
-SELECT md5( name ) = md5( name::text ) AS t FROM srt;
-- pg_client_encoding() takes no args and returns name.
SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt;
SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt;
base-commit: 4f2994647ff1e1209829a0085ca0c8d237dbbbb4
--
2.42.0
From 693baf3cc2d2dfa6399ddb8d8c874d1ef56df86d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 5 Oct 2023 14:45:35 +0200
Subject: [PATCH v4 2/5] pgcrypto: Allow tests to pass in OpenSSL FIPS mode
This adds several alternative expected files for when md5 is not
available. This is similar to the alternative expected files for when
the legacy provider is disabled. In fact, running the pgcrypto tests
in FIPS mode makes use of some of these existing alternative expected
files as well (e.g., for blowfish).
---
contrib/pgcrypto/expected/crypt-md5_1.out | 16 ++
contrib/pgcrypto/expected/hmac-md5_1.out | 44 +++++
contrib/pgcrypto/expected/md5_1.out | 17 ++
contrib/pgcrypto/expected/pgp-encrypt_1.out | 204 ++++++++++++++++++++
4 files changed, 281 insertions(+)
create mode 100644 contrib/pgcrypto/expected/crypt-md5_1.out
create mode 100644 contrib/pgcrypto/expected/hmac-md5_1.out
create mode 100644 contrib/pgcrypto/expected/md5_1.out
create mode 100644 contrib/pgcrypto/expected/pgp-encrypt_1.out
diff --git a/contrib/pgcrypto/expected/crypt-md5_1.out
b/contrib/pgcrypto/expected/crypt-md5_1.out
new file mode 100644
index 0000000000..0ffda34ab4
--- /dev/null
+++ b/contrib/pgcrypto/expected/crypt-md5_1.out
@@ -0,0 +1,16 @@
+--
+-- crypt() and gen_salt(): md5
+--
+SELECT crypt('', '$1$Szzz0yzz');
+ERROR: crypt(3) returned NULL
+SELECT crypt('foox', '$1$Szzz0yzz');
+ERROR: crypt(3) returned NULL
+CREATE TABLE ctest (data text, res text, salt text);
+INSERT INTO ctest VALUES ('password', '', '');
+UPDATE ctest SET salt = gen_salt('md5');
+UPDATE ctest SET res = crypt(data, salt);
+ERROR: crypt(3) returned NULL
+SELECT res = crypt(data, res) AS "worked"
+FROM ctest;
+ERROR: invalid salt
+DROP TABLE ctest;
diff --git a/contrib/pgcrypto/expected/hmac-md5_1.out
b/contrib/pgcrypto/expected/hmac-md5_1.out
new file mode 100644
index 0000000000..56875b0f63
--- /dev/null
+++ b/contrib/pgcrypto/expected/hmac-md5_1.out
@@ -0,0 +1,44 @@
+--
+-- HMAC-MD5
+--
+SELECT hmac(
+'Hi There',
+'\x0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b'::bytea,
+'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+-- 2
+SELECT hmac(
+'Jefe',
+'what do ya want for nothing?',
+'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+-- 3
+SELECT hmac(
+'\xdddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'::bytea,
+'\xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'::bytea,
+'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+-- 4
+SELECT hmac(
+'\xcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd'::bytea,
+'\x0102030405060708090a0b0c0d0e0f10111213141516171819'::bytea,
+'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+-- 5
+SELECT hmac(
+'Test With Truncation',
+'\x0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c'::bytea,
+'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+-- 6
+SELECT hmac(
+'Test Using Larger Than Block-Size Key - Hash Key First',
+'\xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'::bytea,
+'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+-- 7
+SELECT hmac(
+'Test Using Larger Than Block-Size Key and Larger Than One Block-Size Data',
+'\xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'::bytea,
+'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
diff --git a/contrib/pgcrypto/expected/md5_1.out
b/contrib/pgcrypto/expected/md5_1.out
new file mode 100644
index 0000000000..decb215c48
--- /dev/null
+++ b/contrib/pgcrypto/expected/md5_1.out
@@ -0,0 +1,17 @@
+--
+-- MD5 message digest
+--
+SELECT digest('', 'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+SELECT digest('a', 'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+SELECT digest('abc', 'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+SELECT digest('message digest', 'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+SELECT digest('abcdefghijklmnopqrstuvwxyz', 'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+SELECT
digest('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', 'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
+SELECT
digest('12345678901234567890123456789012345678901234567890123456789012345678901234567890',
'md5');
+ERROR: Cannot use "md5": Cipher cannot be initialized
diff --git a/contrib/pgcrypto/expected/pgp-encrypt_1.out
b/contrib/pgcrypto/expected/pgp-encrypt_1.out
new file mode 100644
index 0000000000..b0536f5ceb
--- /dev/null
+++ b/contrib/pgcrypto/expected/pgp-encrypt_1.out
@@ -0,0 +1,204 @@
+--
+-- PGP encrypt
+--
+select pgp_sym_decrypt(pgp_sym_encrypt('Secret.', 'key'), 'key');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- check whether the defaults are ok
+select pgp_sym_decrypt(pgp_sym_encrypt('Secret.', 'key'),
+ 'key', 'expect-cipher-algo=aes128,
+ expect-disable-mdc=0,
+ expect-sess-key=0,
+ expect-s2k-mode=3,
+ expect-s2k-digest-algo=sha1,
+ expect-compress-algo=0
+ ');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- maybe the expect- stuff simply does not work
+select pgp_sym_decrypt(pgp_sym_encrypt('Secret.', 'key'),
+ 'key', 'expect-cipher-algo=bf,
+ expect-disable-mdc=1,
+ expect-sess-key=1,
+ expect-s2k-mode=0,
+ expect-s2k-digest-algo=md5,
+ expect-compress-algo=1
+ ');
+NOTICE: pgp_decrypt: unexpected cipher_algo: expected 4 got 7
+NOTICE: pgp_decrypt: unexpected s2k_mode: expected 0 got 3
+NOTICE: pgp_decrypt: unexpected s2k_digest_algo: expected 1 got 2
+NOTICE: pgp_decrypt: unexpected use_sess_key: expected 1 got 0
+NOTICE: pgp_decrypt: unexpected disable_mdc: expected 1 got 0
+NOTICE: pgp_decrypt: unexpected compress_algo: expected 1 got 0
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- bytea as text
+select pgp_sym_decrypt(pgp_sym_encrypt_bytea('Binary', 'baz'), 'baz');
+ERROR: Not text data
+-- text as bytea
+select encode(pgp_sym_decrypt_bytea(pgp_sym_encrypt('Text', 'baz'), 'baz'),
'escape');
+ encode
+--------
+ Text
+(1 row)
+
+-- algorithm change
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 'cipher-algo=bf'),
+ 'key', 'expect-cipher-algo=bf');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 'cipher-algo=aes'),
+ 'key', 'expect-cipher-algo=aes128');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 'cipher-algo=aes192'),
+ 'key', 'expect-cipher-algo=aes192');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- s2k change
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-mode=0'),
+ 'key', 'expect-s2k-mode=0');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-mode=1'),
+ 'key', 'expect-s2k-mode=1');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-mode=3'),
+ 'key', 'expect-s2k-mode=3');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- s2k count change
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-count=1024'),
+ 'key', 'expect-s2k-count=1024');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- s2k_count rounds up
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-count=65000000'),
+ 'key', 'expect-s2k-count=65000000');
+NOTICE: pgp_decrypt: unexpected s2k_count: expected 65000000 got 65011712
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- s2k digest change
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=md5'),
+ 'key', 'expect-s2k-digest-algo=md5');
+ERROR: Unsupported digest algorithm
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=sha1'),
+ 'key', 'expect-s2k-digest-algo=sha1');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- sess key
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 'sess-key=0'),
+ 'key', 'expect-sess-key=0');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 'sess-key=1'),
+ 'key', 'expect-sess-key=1');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 'sess-key=1, cipher-algo=bf'),
+ 'key', 'expect-sess-key=1, expect-cipher-algo=bf');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 'sess-key=1, cipher-algo=aes192'),
+ 'key', 'expect-sess-key=1, expect-cipher-algo=aes192');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 'sess-key=1, cipher-algo=aes256'),
+ 'key', 'expect-sess-key=1, expect-cipher-algo=aes256');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- no mdc
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 'disable-mdc=1'),
+ 'key', 'expect-disable-mdc=1');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
+-- crlf
+select pgp_sym_decrypt_bytea(
+ pgp_sym_encrypt(E'1\n2\n3\r\n', 'key', 'convert-crlf=1'),
+ 'key');
+ pgp_sym_decrypt_bytea
+------------------------
+ \x310d0a320d0a330d0d0a
+(1 row)
+
+-- conversion should be lossless
+select digest(pgp_sym_decrypt(
+ pgp_sym_encrypt(E'\r\n0\n1\r\r\n\n2\r', 'key', 'convert-crlf=1'),
+ 'key', 'convert-crlf=1'), 'sha1') as result,
+ digest(E'\r\n0\n1\r\r\n\n2\r', 'sha1') as expect;
+ result | expect
+--------------------------------------------+--------------------------------------------
+ \x47bde5d88d6ef8770572b9cbb4278b402aa69966 |
\x47bde5d88d6ef8770572b9cbb4278b402aa69966
+(1 row)
+
--
2.42.0
From 8feace1abca7aad6b9a9a58f464d571649e2d1e2 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 5 Oct 2023 14:45:35 +0200
Subject: [PATCH v4 3/5] Allow tests to pass in OpenSSL FIPS mode (TAP tests)
Some tests using md5 authentication have to be skipped. In other
cases, we can rewrite the tests to use a different authentication
method.
---
src/test/authentication/t/001_password.pl | 121 ++++++++++++----------
src/test/ssl/t/002_scram.pl | 32 +++---
2 files changed, 86 insertions(+), 67 deletions(-)
diff --git a/src/test/authentication/t/001_password.pl
b/src/test/authentication/t/001_password.pl
index 891860886a..884f44d45d 100644
--- a/src/test/authentication/t/001_password.pl
+++ b/src/test/authentication/t/001_password.pl
@@ -66,24 +66,26 @@ sub test_conn
$node->append_conf('postgresql.conf', "log_connections = on\n");
$node->start;
+my $md5_works = ($node->psql('postgres', "select md5('')") == 0);
+
# Create 3 roles with different password methods for each one. The same
# password is used for all of them.
-$node->safe_psql('postgres',
+is($node->psql('postgres',
"SET password_encryption='scram-sha-256'; CREATE ROLE scram_role LOGIN
PASSWORD 'pass';"
-);
-$node->safe_psql('postgres',
+), 0, 'created user with scram password');
+is($node->psql('postgres',
"SET password_encryption='md5'; CREATE ROLE md5_role LOGIN PASSWORD
'pass';"
-);
+), $md5_works ? 0 : 3, 'created user with md5 password');
# Set up a table for tests of SYSTEM_USER.
$node->safe_psql(
'postgres',
"CREATE TABLE sysuser_data (n) AS SELECT NULL FROM generate_series(1,
10);
- GRANT ALL ON sysuser_data TO md5_role;");
+ GRANT ALL ON sysuser_data TO scram_role;");
$ENV{"PGPASSWORD"} = 'pass';
# Create a role that contains a comma to stress the parsing.
$node->safe_psql('postgres',
- q{SET password_encryption='md5'; CREATE ROLE "md5,role" LOGIN PASSWORD
'pass';}
+ q{SET password_encryption='scram-sha-256'; CREATE ROLE "scram,role"
LOGIN PASSWORD 'pass';}
);
# Create a role with a non-default iteration count
@@ -141,8 +143,11 @@ sub test_conn
test_conn($node, 'user=scram_role', 'trust', 0,
log_like =>
[qr/connection authenticated: user="scram_role" method=trust/]);
-test_conn($node, 'user=md5_role', 'trust', 0,
- log_like => [qr/connection authenticated: user="md5_role"
method=trust/]);
+SKIP: {
+ skip "MD5 not supported" unless $md5_works;
+ test_conn($node, 'user=md5_role', 'trust', 0,
+ log_like => [qr/connection authenticated: user="md5_role"
method=trust/]);
+}
# SYSTEM_USER is null when not authenticated.
$res = $node->safe_psql('postgres', "SELECT SYSTEM_USER IS NULL;");
@@ -157,7 +162,7 @@ sub test_conn
SET max_parallel_workers_per_gather TO 2;
SELECT bool_and(SYSTEM_USER IS NOT DISTINCT FROM n) FROM
sysuser_data;),
- connstr => "user=md5_role");
+ connstr => "user=scram_role");
is($res, 't',
"users with trust authentication use SYSTEM_USER = NULL in parallel
workers"
);
@@ -275,9 +280,12 @@ sub test_conn
test_conn($node, 'user=scram_role', 'password', 0,
log_like =>
[qr/connection authenticated: identity="scram_role"
method=password/]);
-test_conn($node, 'user=md5_role', 'password', 0,
- log_like =>
- [qr/connection authenticated: identity="md5_role" method=password/]);
+SKIP: {
+ skip "MD5 not supported" unless $md5_works;
+ test_conn($node, 'user=md5_role', 'password', 0,
+ log_like =>
+ [qr/connection authenticated: identity="md5_role"
method=password/]);
+}
# require_auth succeeds here with a plaintext password.
$node->connect_ok("user=scram_role require_auth=password",
@@ -393,59 +401,62 @@ sub test_conn
test_conn($node, 'user=scram_role', 'md5', 0,
log_like =>
[qr/connection authenticated: identity="scram_role" method=md5/]);
-test_conn($node, 'user=md5_role', 'md5', 0,
- log_like =>
- [qr/connection authenticated: identity="md5_role" method=md5/]);
+SKIP: {
+ skip "MD5 not supported" unless $md5_works;
+ test_conn($node, 'user=md5_role', 'md5', 0,
+ log_like =>
+ [qr/connection authenticated: identity="md5_role"
method=md5/]);
+}
-# require_auth succeeds with MD5 required.
-$node->connect_ok("user=md5_role require_auth=md5",
- "MD5 authentication required, works with MD5 auth");
-$node->connect_ok("user=md5_role require_auth=!none",
- "any authentication required, works with MD5 auth");
+# require_auth succeeds with SCRAM required.
+$node->connect_ok("user=scram_role require_auth=scram-sha-256",
+ "SCRAM authentication required, works with SCRAM auth");
+$node->connect_ok("user=scram_role require_auth=!none",
+ "any authentication required, works with SCRAM auth");
$node->connect_ok(
- "user=md5_role require_auth=md5,scram-sha-256,password",
- "multiple authentication types required, works with MD5 auth");
+ "user=scram_role require_auth=md5,scram-sha-256,password",
+ "multiple authentication types required, works with SCRAM auth");
# Authentication fails if other types are required.
$node->connect_fails(
- "user=md5_role require_auth=password",
- "password authentication required, fails with MD5 auth",
+ "user=scram_role require_auth=password",
+ "password authentication required, fails with SCRAM auth",
expected_stderr =>
- qr/authentication method requirement "password" failed: server
requested a hashed password/
+ qr/authentication method requirement "password" failed: server
requested SASL authentication/
);
$node->connect_fails(
- "user=md5_role require_auth=scram-sha-256",
- "SCRAM authentication required, fails with MD5 auth",
+ "user=scram_role require_auth=md5",
+ "MD5 authentication required, fails with SCRAM auth",
expected_stderr =>
- qr/authentication method requirement "scram-sha-256" failed: server
requested a hashed password/
+ qr/authentication method requirement "md5" failed: server requested
SASL authentication/
);
$node->connect_fails(
- "user=md5_role require_auth=none",
- "all authentication types forbidden, fails with MD5 auth",
+ "user=scram_role require_auth=none",
+ "all authentication types forbidden, fails with SCRAM auth",
expected_stderr =>
- qr/authentication method requirement "none" failed: server requested
a hashed password/
+ qr/authentication method requirement "none" failed: server requested
SASL authentication/
);
-# Authentication fails if MD5 is forbidden.
+# Authentication fails if SCRAM is forbidden.
$node->connect_fails(
- "user=md5_role require_auth=!md5",
- "password authentication forbidden, fails with MD5 auth",
+ "user=scram_role require_auth=!scram-sha-256",
+ "password authentication forbidden, fails with SCRAM auth",
expected_stderr =>
- qr/authentication method requirement "!md5" failed: server requested
a hashed password/
+ qr/authentication method requirement "!scram-sha-256" failed: server
requested SASL authentication/
);
$node->connect_fails(
- "user=md5_role require_auth=!password,!md5,!scram-sha-256",
- "multiple authentication types forbidden, fails with MD5 auth",
+ "user=scram_role require_auth=!password,!md5,!scram-sha-256",
+ "multiple authentication types forbidden, fails with SCRAM auth",
expected_stderr =>
- qr/authentication method requirement "!password,!md5,!scram-sha-256"
failed: server requested a hashed password/
+ qr/authentication method requirement "!password,!md5,!scram-sha-256"
failed: server requested SASL authentication/
);
# Test SYSTEM_USER <> NULL with parallel workers.
$node->safe_psql(
'postgres',
"TRUNCATE sysuser_data;
-INSERT INTO sysuser_data SELECT 'md5:md5_role' FROM generate_series(1, 10);",
- connstr => "user=md5_role");
+INSERT INTO sysuser_data SELECT 'md5:scram_role' FROM generate_series(1, 10);",
+ connstr => "user=scram_role");
$res = $node->safe_psql(
'postgres', qq(
SET min_parallel_table_scan_size TO 0;
@@ -454,7 +465,7 @@ sub test_conn
SET max_parallel_workers_per_gather TO 2;
SELECT bool_and(SYSTEM_USER IS NOT DISTINCT FROM n) FROM
sysuser_data;),
- connstr => "user=md5_role");
+ connstr => "user=scram_role");
is($res, 't',
"users with md5 authentication use SYSTEM_USER = md5:role in parallel
workers"
);
@@ -490,49 +501,49 @@ sub test_conn
append_to_file(
$pgpassfile, qq!
-*:*:*:md5_role:p\\ass
-*:*:*:md5,role:p\\ass
+*:*:*:scram_role:p\\ass
+*:*:*:scram,role:p\\ass
!);
-test_conn($node, 'user=md5_role', 'password from pgpass', 0);
+test_conn($node, 'user=scram_role', 'password from pgpass', 0);
# Testing with regular expression for username. The third regexp matches.
-reset_pg_hba($node, 'all', '/^.*nomatch.*$, baduser, /^md.*$', 'password');
-test_conn($node, 'user=md5_role', 'password, matching regexp for username', 0,
+reset_pg_hba($node, 'all', '/^.*nomatch.*$, baduser, /^scr.*$', 'password');
+test_conn($node, 'user=scram_role', 'password, matching regexp for username',
0,
log_like =>
- [qr/connection authenticated: identity="md5_role" method=password/]);
+ [qr/connection authenticated: identity="scram_role"
method=password/]);
# The third regex does not match anymore.
-reset_pg_hba($node, 'all', '/^.*nomatch.*$, baduser, /^m_d.*$', 'password');
-test_conn($node, 'user=md5_role',
+reset_pg_hba($node, 'all', '/^.*nomatch.*$, baduser, /^sc_r.*$', 'password');
+test_conn($node, 'user=scram_role',
'password, non matching regexp for username',
2, log_unlike => [qr/connection authenticated:/]);
# Test with a comma in the regular expression. In this case, the use of
# double quotes is mandatory so as this is not considered as two elements
# of the user name list when parsing pg_hba.conf.
-reset_pg_hba($node, 'all', '"/^.*5,.*e$"', 'password');
-test_conn($node, 'user=md5,role', 'password, matching regexp for username', 0,
+reset_pg_hba($node, 'all', '"/^.*m,.*e$"', 'password');
+test_conn($node, 'user=scram,role', 'password, matching regexp for username',
0,
log_like =>
- [qr/connection authenticated: identity="md5,role" method=password/]);
+ [qr/connection authenticated: identity="scram,role"
method=password/]);
# Testing with regular expression for dbname. The third regex matches.
reset_pg_hba($node, '/^.*nomatch.*$, baddb, /^regex_t.*b$', 'all',
'password');
test_conn(
$node,
- 'user=md5_role dbname=regex_testdb',
+ 'user=scram_role dbname=regex_testdb',
'password, matching regexp for dbname',
0,
log_like =>
- [qr/connection authenticated: identity="md5_role" method=password/]);
+ [qr/connection authenticated: identity="scram_role"
method=password/]);
# The third regexp does not match anymore.
reset_pg_hba($node, '/^.*nomatch.*$, baddb, /^regex_t.*ba$',
'all', 'password');
test_conn(
$node,
- 'user=md5_role dbname=regex_testdb',
+ 'user=scram_role dbname=regex_testdb',
'password, non matching regexp for dbname',
2, log_unlike => [qr/connection authenticated:/]);
diff --git a/src/test/ssl/t/002_scram.pl b/src/test/ssl/t/002_scram.pl
index 27abd02abf..d187f532de 100644
--- a/src/test/ssl/t/002_scram.pl
+++ b/src/test/ssl/t/002_scram.pl
@@ -64,6 +64,8 @@ sub switch_server_cert
$ENV{PGPORT} = $node->port;
$node->start;
+my $md5_works = ($node->psql('postgres', "select md5('')") == 0);
+
# Configure server for SSL connections, with password handling.
$ssl_server->configure_test_server_for_ssl(
$node, $SERVERHOSTADDR, $SERVERHOSTCIDR,
@@ -91,12 +93,15 @@ sub switch_server_cert
"SCRAM with SSL and channel_binding=require");
# Now test when the user has an MD5-encrypted password; should fail
-$node->connect_fails(
- "$common_connstr user=md5testuser channel_binding=require",
- "MD5 with SSL and channel_binding=require",
- expected_stderr =>
- qr/channel binding required but not supported by server's
authentication request/
-);
+SKIP: {
+ skip "MD5 not supported" unless $md5_works;
+ $node->connect_fails(
+ "$common_connstr user=md5testuser channel_binding=require",
+ "MD5 with SSL and channel_binding=require",
+ expected_stderr =>
+ qr/channel binding required but not supported by server's
authentication request/
+ );
+}
# Now test with auth method 'cert' by connecting to 'certdb'. Should fail,
# because channel binding is not performed. Note that ssl/client.key may
@@ -130,12 +135,15 @@ sub switch_server_cert
"$common_connstr user=ssltestuser channel_binding=disable
require_auth=scram-sha-256",
"SCRAM with SSL, channel_binding=disable, and
require_auth=scram-sha-256"
);
-$node->connect_fails(
- "$common_connstr user=md5testuser require_auth=md5
channel_binding=require",
- "channel_binding can fail even when require_auth succeeds",
- expected_stderr =>
- qr/channel binding required but not supported by server's
authentication request/
-);
+SKIP: {
+ skip "MD5 not supported" unless $md5_works;
+ $node->connect_fails(
+ "$common_connstr user=md5testuser require_auth=md5
channel_binding=require",
+ "channel_binding can fail even when require_auth succeeds",
+ expected_stderr =>
+ qr/channel binding required but not supported by server's
authentication request/
+ );
+}
$node->connect_ok(
"$common_connstr user=ssltestuser channel_binding=require
require_auth=scram-sha-256",
"SCRAM with SSL, channel_binding=require, and
require_auth=scram-sha-256"
--
2.42.0
From d1470936ab5784b1dafc5fdc777dd8004c5f57ba Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 5 Oct 2023 14:45:35 +0200
Subject: [PATCH v4 4/5] Allow tests to pass in OpenSSL FIPS mode (rest)
This adds alternative expected files for various tests.
XXX maybe some of these could be reorgnized to make the patch smaller?
---
.../expected/passwordcheck_1.out | 18 +++
contrib/uuid-ossp/expected/uuid_ossp_1.out | 135 ++++++++++++++++
src/test/regress/expected/md5_1.out | 35 ++++
src/test/regress/expected/password_1.out | 150 ++++++++++++++++++
4 files changed, 338 insertions(+)
create mode 100644 contrib/passwordcheck/expected/passwordcheck_1.out
create mode 100644 contrib/uuid-ossp/expected/uuid_ossp_1.out
create mode 100644 src/test/regress/expected/md5_1.out
create mode 100644 src/test/regress/expected/password_1.out
diff --git a/contrib/passwordcheck/expected/passwordcheck_1.out
b/contrib/passwordcheck/expected/passwordcheck_1.out
new file mode 100644
index 0000000000..5d8d5dcc1c
--- /dev/null
+++ b/contrib/passwordcheck/expected/passwordcheck_1.out
@@ -0,0 +1,18 @@
+LOAD 'passwordcheck';
+CREATE USER regress_passwordcheck_user1;
+-- ok
+ALTER USER regress_passwordcheck_user1 PASSWORD 'a_nice_long_password';
+-- error: too short
+ALTER USER regress_passwordcheck_user1 PASSWORD 'tooshrt';
+ERROR: password is too short
+-- error: contains user name
+ALTER USER regress_passwordcheck_user1 PASSWORD
'xyzregress_passwordcheck_user1';
+ERROR: password must not contain user name
+-- error: contains only letters
+ALTER USER regress_passwordcheck_user1 PASSWORD 'alessnicelongpassword';
+ERROR: password must contain both letters and nonletters
+-- encrypted ok (password is "secret")
+ALTER USER regress_passwordcheck_user1 PASSWORD
'md592350e12ac34e52dd598f90893bb3ae7';
+-- error: password is user name
+ALTER USER regress_passwordcheck_user1 PASSWORD
'md507a112732ed9f2087fa90b192d44e358';
+DROP USER regress_passwordcheck_user1;
diff --git a/contrib/uuid-ossp/expected/uuid_ossp_1.out
b/contrib/uuid-ossp/expected/uuid_ossp_1.out
new file mode 100644
index 0000000000..58104dbe18
--- /dev/null
+++ b/contrib/uuid-ossp/expected/uuid_ossp_1.out
@@ -0,0 +1,135 @@
+CREATE EXTENSION "uuid-ossp";
+SELECT uuid_nil();
+ uuid_nil
+--------------------------------------
+ 00000000-0000-0000-0000-000000000000
+(1 row)
+
+SELECT uuid_ns_dns();
+ uuid_ns_dns
+--------------------------------------
+ 6ba7b810-9dad-11d1-80b4-00c04fd430c8
+(1 row)
+
+SELECT uuid_ns_url();
+ uuid_ns_url
+--------------------------------------
+ 6ba7b811-9dad-11d1-80b4-00c04fd430c8
+(1 row)
+
+SELECT uuid_ns_oid();
+ uuid_ns_oid
+--------------------------------------
+ 6ba7b812-9dad-11d1-80b4-00c04fd430c8
+(1 row)
+
+SELECT uuid_ns_x500();
+ uuid_ns_x500
+--------------------------------------
+ 6ba7b814-9dad-11d1-80b4-00c04fd430c8
+(1 row)
+
+-- some quick and dirty field extraction functions
+-- this is actually timestamp concatenated with clock sequence, per RFC 4122
+CREATE FUNCTION uuid_timestamp_bits(uuid) RETURNS varbit AS
+$$ SELECT ('x' || substr($1::text, 15, 4) || substr($1::text, 10, 4) ||
+ substr($1::text, 1, 8) || substr($1::text, 20, 4))::bit(80)
+ & x'0FFFFFFFFFFFFFFF3FFF' $$
+LANGUAGE SQL STRICT IMMUTABLE;
+CREATE FUNCTION uuid_version_bits(uuid) RETURNS varbit AS
+$$ SELECT ('x' || substr($1::text, 15, 2))::bit(8) & '11110000' $$
+LANGUAGE SQL STRICT IMMUTABLE;
+CREATE FUNCTION uuid_reserved_bits(uuid) RETURNS varbit AS
+$$ SELECT ('x' || substr($1::text, 20, 2))::bit(8) & '11000000' $$
+LANGUAGE SQL STRICT IMMUTABLE;
+CREATE FUNCTION uuid_multicast_bit(uuid) RETURNS bool AS
+$$ SELECT (('x' || substr($1::text, 25, 2))::bit(8) & '00000001') !=
'00000000' $$
+LANGUAGE SQL STRICT IMMUTABLE;
+CREATE FUNCTION uuid_local_admin_bit(uuid) RETURNS bool AS
+$$ SELECT (('x' || substr($1::text, 25, 2))::bit(8) & '00000010') !=
'00000000' $$
+LANGUAGE SQL STRICT IMMUTABLE;
+CREATE FUNCTION uuid_node(uuid) RETURNS text AS
+$$ SELECT substr($1::text, 25) $$
+LANGUAGE SQL STRICT IMMUTABLE;
+-- Ideally, the multicast bit would never be set in V1 output, but the
+-- UUID library may fall back to MC if it can't get the system MAC address.
+-- Also, the local-admin bit might be set (if so, we're probably inside a VM).
+-- So we can't test either bit here.
+SELECT uuid_version_bits(uuid_generate_v1()),
+ uuid_reserved_bits(uuid_generate_v1());
+ uuid_version_bits | uuid_reserved_bits
+-------------------+--------------------
+ 00010000 | 10000000
+(1 row)
+
+-- Although RFC 4122 only requires the multicast bit to be set in V1MC style
+-- UUIDs, our implementation always sets the local-admin bit as well.
+SELECT uuid_version_bits(uuid_generate_v1mc()),
+ uuid_reserved_bits(uuid_generate_v1mc()),
+ uuid_multicast_bit(uuid_generate_v1mc()),
+ uuid_local_admin_bit(uuid_generate_v1mc());
+ uuid_version_bits | uuid_reserved_bits | uuid_multicast_bit |
uuid_local_admin_bit
+-------------------+--------------------+--------------------+----------------------
+ 00010000 | 10000000 | t | t
+(1 row)
+
+-- timestamp+clock sequence should be monotonic increasing in v1
+SELECT uuid_timestamp_bits(uuid_generate_v1()) <
uuid_timestamp_bits(uuid_generate_v1());
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_timestamp_bits(uuid_generate_v1mc()) <
uuid_timestamp_bits(uuid_generate_v1mc());
+ ?column?
+----------
+ t
+(1 row)
+
+-- Ideally, the node value is stable in V1 addresses, but OSSP UUID
+-- falls back to V1MC behavior if it can't get the system MAC address.
+SELECT CASE WHEN uuid_multicast_bit(uuid_generate_v1()) AND
+ uuid_local_admin_bit(uuid_generate_v1()) THEN
+ true -- punt, no test
+ ELSE
+ uuid_node(uuid_generate_v1()) = uuid_node(uuid_generate_v1())
+ END;
+ case
+------
+ t
+(1 row)
+
+-- In any case, V1MC node addresses should be random.
+SELECT uuid_node(uuid_generate_v1()) <> uuid_node(uuid_generate_v1mc());
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_node(uuid_generate_v1mc()) <> uuid_node(uuid_generate_v1mc());
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_generate_v3(uuid_ns_dns(), 'www.widgets.com');
+ERROR: could not initialize MD5 context: unsupported
+SELECT uuid_generate_v5(uuid_ns_dns(), 'www.widgets.com');
+ uuid_generate_v5
+--------------------------------------
+ 21f7f8de-8051-5b89-8680-0195ef798b6a
+(1 row)
+
+SELECT uuid_version_bits(uuid_generate_v4()),
+ uuid_reserved_bits(uuid_generate_v4());
+ uuid_version_bits | uuid_reserved_bits
+-------------------+--------------------
+ 01000000 | 10000000
+(1 row)
+
+SELECT uuid_generate_v4() <> uuid_generate_v4();
+ ?column?
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/expected/md5_1.out
b/src/test/regress/expected/md5_1.out
new file mode 100644
index 0000000000..174b70bafb
--- /dev/null
+++ b/src/test/regress/expected/md5_1.out
@@ -0,0 +1,35 @@
+--
+-- MD5 test suite - from IETF RFC 1321
+-- (see: https://www.rfc-editor.org/rfc/rfc1321)
+--
+-- (The md5() function will error in OpenSSL FIPS mode. By keeping
+-- this test in a separate file, it is easier to manage variant
+-- results.)
+select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5('a') = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5('abc') = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5('message digest') = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5('abcdefghijklmnopqrstuvwxyz') = 'c3fcd3d76192e4007dfb496cca67e13b'
AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') =
'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select
md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890')
= '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5(''::bytea) = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5('a'::bytea) = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5('abc'::bytea) = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5('message digest'::bytea) = 'f96b697d7cb7938d525a2f31aaf161d0' AS
"TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select md5('abcdefghijklmnopqrstuvwxyz'::bytea) =
'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select
md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'::bytea) =
'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
+select
md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea)
= '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE";
+ERROR: could not compute MD5 hash: unsupported
diff --git a/src/test/regress/expected/password_1.out
b/src/test/regress/expected/password_1.out
new file mode 100644
index 0000000000..3bb411949e
--- /dev/null
+++ b/src/test/regress/expected/password_1.out
@@ -0,0 +1,150 @@
+--
+-- Tests for password types
+--
+-- Tests for GUC password_encryption
+SET password_encryption = 'novalue'; -- error
+ERROR: invalid value for parameter "password_encryption": "novalue"
+HINT: Available values: md5, scram-sha-256.
+SET password_encryption = true; -- error
+ERROR: invalid value for parameter "password_encryption": "true"
+HINT: Available values: md5, scram-sha-256.
+SET password_encryption = 'md5'; -- ok
+SET password_encryption = 'scram-sha-256'; -- ok
+-- consistency of password entries
+SET password_encryption = 'md5';
+CREATE ROLE regress_passwd1 PASSWORD 'role_pwd1';
+ERROR: password encryption failed: unsupported
+CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2';
+ERROR: password encryption failed: unsupported
+SET password_encryption = 'scram-sha-256';
+CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3';
+CREATE ROLE regress_passwd4 PASSWORD NULL;
+-- check list of created entries
+--
+-- The scram secret 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_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
+ regress_passwd4 |
+(2 rows)
+
+-- Rename a role
+ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new;
+ERROR: role "regress_passwd2" does not exist
+-- md5 entry should have been removed
+SELECT rolname, rolpassword
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_passwd2_new'
+ ORDER BY rolname, rolpassword;
+ rolname | rolpassword
+---------+-------------
+(0 rows)
+
+ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2;
+ERROR: role "regress_passwd2_new" does not exist
+-- Change passwords with ALTER USER. With plaintext or already-encrypted
+-- passwords.
+SET password_encryption = 'md5';
+-- encrypt with MD5
+ALTER ROLE regress_passwd2 PASSWORD 'foo';
+ERROR: role "regress_passwd2" does not exist
+-- already encrypted, use as they are
+ALTER ROLE regress_passwd1 PASSWORD 'md5cd3578025fe2c3d7ed1b9a9b26238b70';
+ERROR: role "regress_passwd1" does not exist
+ALTER ROLE regress_passwd3 PASSWORD
'SCRAM-SHA-256$4096:VLK4RMaQLCvNtQ==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo=';
+SET password_encryption = 'scram-sha-256';
+-- create SCRAM secret
+ALTER ROLE regress_passwd4 PASSWORD 'foo';
+-- already encrypted with MD5, use as it is
+CREATE ROLE regress_passwd5 PASSWORD 'md5e73a4b11df52a6068f8b39f90be36023';
+-- This looks like a valid SCRAM-SHA-256 secret, but it is not
+-- so it should be hashed with SCRAM-SHA-256.
+CREATE ROLE regress_passwd6 PASSWORD 'SCRAM-SHA-256$1234';
+-- These may look like valid MD5 secrets, but they are not, so they
+-- should be hashed with SCRAM-SHA-256.
+-- trailing garbage at the end
+CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz';
+-- invalid length
+CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz';
+-- Changing the SCRAM iteration count
+SET scram_iterations = 1024;
+CREATE ROLE regress_passwd9 PASSWORD 'alterediterationcount';
+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_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
+ regress_passwd4 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
+ regress_passwd5 | md5e73a4b11df52a6068f8b39f90be36023
+ regress_passwd6 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
+ regress_passwd7 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
+ regress_passwd8 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
+ regress_passwd9 | SCRAM-SHA-256$1024:<salt>$<storedkey>:<serverkey>
+(7 rows)
+
+-- An empty password is not allowed, in any form
+CREATE ROLE regress_passwd_empty PASSWORD '';
+NOTICE: empty string is not a valid password, clearing password
+ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a';
+ALTER ROLE regress_passwd_empty PASSWORD
'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4=';
+NOTICE: empty string is not a valid password, clearing password
+SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty';
+ rolpassword
+-------------
+
+(1 row)
+
+-- Test with invalid stored and server keys.
+--
+-- The first is valid, to act as a control. The others have too long
+-- stored/server keys. They will be re-hashed.
+CREATE ROLE regress_passwd_sha_len0 PASSWORD
'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI=';
+CREATE ROLE regress_passwd_sha_len1 PASSWORD
'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96RqwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI=';
+CREATE ROLE regress_passwd_sha_len2 PASSWORD
'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=';
+-- Check that the invalid secrets were re-hashed. A re-hashed secret
+-- should not contain the original salt.
+SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as
is_rolpassword_rehashed
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_passwd_sha_len%'
+ ORDER BY rolname;
+ rolname | is_rolpassword_rehashed
+-------------------------+-------------------------
+ regress_passwd_sha_len0 | f
+ regress_passwd_sha_len1 | t
+ regress_passwd_sha_len2 | t
+(3 rows)
+
+DROP ROLE regress_passwd1;
+ERROR: role "regress_passwd1" does not exist
+DROP ROLE regress_passwd2;
+ERROR: role "regress_passwd2" does not exist
+DROP ROLE regress_passwd3;
+DROP ROLE regress_passwd4;
+DROP ROLE regress_passwd5;
+DROP ROLE regress_passwd6;
+DROP ROLE regress_passwd7;
+DROP ROLE regress_passwd8;
+DROP ROLE regress_passwd9;
+DROP ROLE regress_passwd_empty;
+DROP ROLE regress_passwd_sha_len0;
+DROP ROLE regress_passwd_sha_len1;
+DROP ROLE regress_passwd_sha_len2;
+-- 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)
+
--
2.42.0
From 65b287b111fef67abed492c805519eb5c6b96efa Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 5 Oct 2023 14:45:35 +0200
Subject: [PATCH v4 5/5] WIP: Use fipshash in brin_multi test
---
src/test/regress/expected/brin_multi.out | 24 ++++++++++++------------
src/test/regress/sql/brin_multi.sql | 4 ++--
2 files changed, 14 insertions(+), 14 deletions(-)
diff --git a/src/test/regress/expected/brin_multi.out
b/src/test/regress/expected/brin_multi.out
index 9f46934c9b..6773701c7e 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -740,19 +740,19 @@ RESET enable_seqscan;
-- do some inequality tests for varlena data types
CREATE TABLE brin_test_multi_2 (a UUID) WITH (fillfactor=10);
INSERT INTO brin_test_multi_2
-SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT
md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c +
25 * random();
+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT
fipshash((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER
BY c + 25 * random();
CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2 USING brin (a
uuid_minmax_multi_ops) WITH (pages_per_range=5);
SET enable_seqscan=off;
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <
'33e75ff0-9dd6-01bb-e69f-351039152189';
count
-------
- 195
+ 156
(1 row)
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >
'33e75ff0-9dd6-01bb-e69f-351039152189';
count
-------
- 792
+ 844
(1 row)
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <=
'f457c545-a9de-d88f-18ec-ee47145a72c0';
@@ -764,19 +764,19 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <=
'f457c545-a9de-d88f-18ec-ee471
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >=
'c51ce410-c124-a10e-0db5-e4b97fc2af39';
count
-------
- 272
+ 221
(1 row)
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a =
'cfcd2084-95d5-65ef-66e7-dff9f98764da';
count
-------
- 12
+ 0
(1 row)
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a =
'aab32389-22bc-c25a-6f60-6eb525ffdc56';
count
-------
- 13
+ 0
(1 row)
-- now do the same, but insert the rows with the indexes already created
@@ -784,17 +784,17 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a =
'aab32389-22bc-c25a-6f60-6eb525
-- approach of adding rows into existing ranges
TRUNCATE brin_test_multi_2;
INSERT INTO brin_test_multi_2
-SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT
md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c +
25 * random();
+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT
fipshash((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER
BY c + 25 * random();
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <
'33e75ff0-9dd6-01bb-e69f-351039152189';
count
-------
- 195
+ 156
(1 row)
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >
'33e75ff0-9dd6-01bb-e69f-351039152189';
count
-------
- 792
+ 844
(1 row)
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <=
'f457c545-a9de-d88f-18ec-ee47145a72c0';
@@ -806,19 +806,19 @@ SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <=
'f457c545-a9de-d88f-18ec-ee471
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >=
'c51ce410-c124-a10e-0db5-e4b97fc2af39';
count
-------
- 272
+ 221
(1 row)
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a =
'cfcd2084-95d5-65ef-66e7-dff9f98764da';
count
-------
- 12
+ 0
(1 row)
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a =
'aab32389-22bc-c25a-6f60-6eb525ffdc56';
count
-------
- 13
+ 0
(1 row)
DROP TABLE brin_test_multi_2;
diff --git a/src/test/regress/sql/brin_multi.sql
b/src/test/regress/sql/brin_multi.sql
index d50dbdee68..5bca4fd350 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -545,7 +545,7 @@ CREATE INDEX brin_test_multi_1_idx_2 ON brin_test_multi_1
USING brin (b int8_min
-- do some inequality tests for varlena data types
CREATE TABLE brin_test_multi_2 (a UUID) WITH (fillfactor=10);
INSERT INTO brin_test_multi_2
-SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT
md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c +
25 * random();
+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT
fipshash((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER
BY c + 25 * random();
CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2 USING brin (a
uuid_minmax_multi_ops) WITH (pages_per_range=5);
@@ -570,7 +570,7 @@ CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2
USING brin (a uuid_minma
TRUNCATE brin_test_multi_2;
INSERT INTO brin_test_multi_2
-SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT
md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c +
25 * random();
+SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT
fipshash((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER
BY c + 25 * random();
SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <
'33e75ff0-9dd6-01bb-e69f-351039152189';
--
2.42.0
