Thread: Allow tests to pass in OpenSSL FIPS mode
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.
The main issue is liberal use of the md5() function in tests to generate
random strings. For example, this is a common pattern:
SELECT x, md5(x::text) FROM generate_series(-10,10) x;
This can be replaced by
SELECT x, encode(sha256(x::text::bytea), 'hex')
FROM generate_series(-10,10) x;
In most cases, this could be further simplified by not using text but
bytea for the column types, thus skipping the encode step.
Some tests are carefully calibrated to achieve a certain column size or
something like that. These will need to be checked in more detail.
Another set of issues is in the SSL tests, where apparently some
certificates are generated with obsolete hash methods, probably SHA1
(and possibly MD5 again). Some of this can be addressed by just
regenerating everything with a newer OpenSSL installation, in some other
cases it appears to need additional command-line options or a local
configuration file change. This needs more research. I think we should
augment the setup used to generate these test files in a way that they
don't depend on the local configuration of whoever runs it.
Of course, there are some some tests where we do want to test MD5
functionality, such as in the authentication tests or in the tests of
the md5() function itself. I think we can conditionalize these somehow.
That looks like a smaller issue compared to the issues above.
Attachment
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. > Of course, there are some some tests where we do want to test MD5 > functionality, such as in the authentication tests or in the tests of > the md5() function itself. I think we can conditionalize these somehow. Let's make a small start on this. The attached patch moves the tests of the md5() function to a separate test file. That would ultimately make it easier to maintain a variant expected file for FIPS mode where that function will fail (similar to how we have done it for the pgcrypto tests).
Attachment
On Tue, Oct 11, 2022 at 01:51:50PM +0200, Peter Eisentraut wrote: > Let's make a small start on this. The attached patch moves the tests of the > md5() function to a separate test file. That would ultimately make it > easier to maintain a variant expected file for FIPS mode where that function > will fail (similar to how we have done it for the pgcrypto tests). Makes sense to me. This slice looks fine. I think that the other md5() computations done in the main regression test suite could just be switched to use one of the sha*() functions as they just want to put their hands on text values. It looks like a few of them have some expections with the output size and generate_series(), though, but this could be tweaked by making the series shorter, for example. -- Michael
Attachment
On 12.10.22 03:18, Michael Paquier wrote: > On Tue, Oct 11, 2022 at 01:51:50PM +0200, Peter Eisentraut wrote: >> Let's make a small start on this. The attached patch moves the tests of the >> md5() function to a separate test file. That would ultimately make it >> easier to maintain a variant expected file for FIPS mode where that function >> will fail (similar to how we have done it for the pgcrypto tests). > > Makes sense to me. This slice looks fine. Committed. > I think that the other md5() computations done in the main regression > test suite could just be switched to use one of the sha*() functions > as they just want to put their hands on text values. It looks like a > few of them have some expections with the output size and > generate_series(), though, but this could be tweaked by making the > series shorter, for example. Right, that's the rest of my original patch. I'll come back with an updated version of that.
On 2022-Oct-13, Peter Eisentraut wrote: > Right, that's the rest of my original patch. I'll come back with an updated > version of that. However, there are some changes in brin_multi.out that are quite surprising and suggest that we might have bugs in brin: +WARNING: unexpected number of results 31 for (macaddr8col,>,macaddr8,b1:d1:0e:7b:af:a4:42:12,33) +WARNING: unexpected number of results 17 for (macaddr8col,>=,macaddr8,d9:35:91:bd:f7:86:0e:1e,15) +WARNING: unexpected number of results 11 for (macaddr8col,<=,macaddr8,23:e8:46:63:86:07:ad:cb,13) +WARNING: unexpected number of results 4 for (macaddr8col,<,macaddr8,13:16:8e:6a:2e:6c:84:b4,6) -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "La victoria es para quien se atreve a estar solo"
On 13.10.22 12:26, Peter Eisentraut wrote: >> I think that the other md5() computations done in the main regression >> test suite could just be switched to use one of the sha*() functions >> as they just want to put their hands on text values. It looks like a >> few of them have some expections with the output size and >> generate_series(), though, but this could be tweaked by making the >> series shorter, for example. > > Right, that's the rest of my original patch. I'll come back with an > updated version of that. Here is the next step. To contain the scope, I focused on just "make check" for now. This patch removes all incidental calls to md5(), replacing them with sha256(), so that they'd pass with or without FIPS mode. (Two tests would need alternative expected files: md5 and password. I have not included those here.) Some tests inspect the actual md5 result strings or build statistics based on them. I have tried to carefully preserve the meaning of the original tests, to the extent that they could be inferred, in some cases adjusting example values by matching the md5 outputs to the equivalent sha256 outputs. Some cases are tricky or mysterious or both and could use another look.
Attachment
On Wed, Dec 07, 2022 at 03:14:09PM +0100, Peter Eisentraut wrote: > Here is the next step. To contain the scope, I focused on just "make check" > for now. This patch removes all incidental calls to md5(), replacing them > with sha256(), so that they'd pass with or without FIPS mode. (Two tests > would need alternative expected files: md5 and password. I have not > included those here.) Yeah, fine by me to do that step-by-step. > Some tests inspect the actual md5 result strings or build statistics based > on them. I have tried to carefully preserve the meaning of the original > tests, to the extent that they could be inferred, in some cases adjusting > example values by matching the md5 outputs to the equivalent sha256 outputs. > Some cases are tricky or mysterious or both and could use another look. incremental_sort mostly relies on the plan generated, so the change should be rather straight-forward I guess, though there may be a side effect depending on costing. Hmm, it does not look like stats_ext would be an issue as it checks the stats correlation of the attributes for mcv_lists_arrays. largeobject_1.out has been forgotten in the set requiring a refresh. -- Michael
Attachment
On 09.12.22 05:16, Michael Paquier wrote: >> Some tests inspect the actual md5 result strings or build statistics based >> on them. I have tried to carefully preserve the meaning of the original >> tests, to the extent that they could be inferred, in some cases adjusting >> example values by matching the md5 outputs to the equivalent sha256 outputs. >> Some cases are tricky or mysterious or both and could use another look. > incremental_sort mostly relies on the plan generated, so the change > should be rather straight-forward I guess, though there may be a side > effect depending on costing. Hmm, it does not look like stats_ext > would be an issue as it checks the stats correlation of the attributes > for mcv_lists_arrays. > > largeobject_1.out has been forgotten in the set requiring a refresh. Here is a refreshed patch with the missing file added.
Attachment
On Thu, Oct 13, 2022 at 01:16:18PM +0200, Alvaro Herrera wrote: > However, there are some changes in brin_multi.out that are quite > surprising and suggest that we might have bugs in brin: > > +WARNING: unexpected number of results 31 for (macaddr8col,>,macaddr8,b1:d1:0e:7b:af:a4:42:12,33) > +WARNING: unexpected number of results 17 for (macaddr8col,>=,macaddr8,d9:35:91:bd:f7:86:0e:1e,15) > +WARNING: unexpected number of results 11 for (macaddr8col,<=,macaddr8,23:e8:46:63:86:07:ad:cb,13) > +WARNING: unexpected number of results 4 for (macaddr8col,<,macaddr8,13:16:8e:6a:2e:6c:84:b4,6) This refers to brin_minmax_multi_distance_macaddr8(), no? This is amazing. I have a hard time imagining how FIPS would interact with what we do in mac8.c to explain that, so it may be something entirely different. Is that reproducible? -- Michael
Attachment
On 27.02.23 08:16, Michael Paquier wrote: > On Thu, Oct 13, 2022 at 01:16:18PM +0200, Alvaro Herrera wrote: >> However, there are some changes in brin_multi.out that are quite >> surprising and suggest that we might have bugs in brin: >> >> +WARNING: unexpected number of results 31 for (macaddr8col,>,macaddr8,b1:d1:0e:7b:af:a4:42:12,33) >> +WARNING: unexpected number of results 17 for (macaddr8col,>=,macaddr8,d9:35:91:bd:f7:86:0e:1e,15) >> +WARNING: unexpected number of results 11 for (macaddr8col,<=,macaddr8,23:e8:46:63:86:07:ad:cb,13) >> +WARNING: unexpected number of results 4 for (macaddr8col,<,macaddr8,13:16:8e:6a:2e:6c:84:b4,6) > > This refers to brin_minmax_multi_distance_macaddr8(), no? This is > amazing. I have a hard time imagining how FIPS would interact with > what we do in mac8.c to explain that, so it may be something entirely > different. Is that reproducible? This is no longer present in the v2 patch.
On Mon, Feb 27, 2023 at 08:23:34AM +0100, Peter Eisentraut wrote: > On 27.02.23 08:16, Michael Paquier wrote: >> This refers to brin_minmax_multi_distance_macaddr8(), no? This is >> amazing. I have a hard time imagining how FIPS would interact with >> what we do in mac8.c to explain that, so it may be something entirely >> different. Is that reproducible? > > This is no longer present in the v2 patch. Sure, but why was it happening in the first place? The proposed patch set only reworks some regression tests. So It seems to me that this is a sign that we may have issues in some code area that got stressed in some new way, no? -- Michael
Attachment
On 28.02.23 06:01, Michael Paquier wrote: > On Mon, Feb 27, 2023 at 08:23:34AM +0100, Peter Eisentraut wrote: >> On 27.02.23 08:16, Michael Paquier wrote: >>> This refers to brin_minmax_multi_distance_macaddr8(), no? This is >>> amazing. I have a hard time imagining how FIPS would interact with >>> what we do in mac8.c to explain that, so it may be something entirely >>> different. Is that reproducible? >> >> This is no longer present in the v2 patch. > > Sure, but why was it happening in the first place? Because the earlier patch only changed the test input values (which were generated on the fly using md5()), but did not adjust the expected test results in all the places.
Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> [ v2-0001-Remove-incidental-md5-function-uses-from-main-reg.patch ]
I've gone through this and have a modest suggestion: let's invent some
wrapper functions around encode(sha256()) to reduce the cosmetic diffs
and consequent need for closer study of patch changes. In the attached
I called them "notmd5()", but I'm surely not wedded to that name.
This also accounts for some relatively recent additions to stats_ext.sql
that introduced yet more uses of md5(). This passes for me on a
FIPS-enabled Fedora system, with the exception of md5.sql and
password.sql. I agree that the right thing for md5.sql is just to add
a variant expected-file. password.sql could perhaps use some refactoring
so that we don't have two large expected-files to manage.
The only other place that perhaps needs discussion is rowsecurity.sql,
which has some surprisingly large changes: not only do the random
strings change, but there are rowcount differences in some results.
I believe this is because there are RLS policy checks and view conditions
that actually examine the contents of the "md5" strings, eg
CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
My recommendation is to just accept those changes as OK and move on.
I doubt that anybody checked the existing results line-by-line either.
So, once we've done something about md5.sql and password.sql, I think
this is committable.
regards, tom lane
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 0ff54a18de..1c46da1e0b 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -2303,14 +2303,14 @@ insert into src
create type textandtext as (c1 text, c2 text);
create temp table dest (f1 textandtext[]);
insert into dest select array[row(f1,f1)::textandtext] from src;
-select length(md5((f1[1]).c2)) from dest;
+select length(notmd5((f1[1]).c2)) from dest;
length
--------
32
(1 row)
delete from src;
-select length(md5((f1[1]).c2)) from dest;
+select length(notmd5((f1[1]).c2)) from dest;
length
--------
32
@@ -2318,7 +2318,7 @@ select length(md5((f1[1]).c2)) from dest;
truncate table src;
drop table src;
-select length(md5((f1[1]).c2)) from dest;
+select length(notmd5((f1[1]).c2)) from dest;
length
--------
32
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index 73fa38396e..b3aac15ecc 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -530,7 +530,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE b = 1;
CREATE TABLE brintest_3 (a text, b text, c text, d text);
-- long random strings (~2000 chars each, so ~6kB for min/max on two
-- columns) to trigger toasting
-WITH rand_value AS (SELECT string_agg(md5(i::text),'') AS val FROM generate_series(1,60) s(i))
+WITH rand_value AS (SELECT string_agg(notmd5(i::text),'') AS val FROM generate_series(1,60) s(i))
INSERT INTO brintest_3
SELECT val, val, val, val FROM rand_value;
CREATE INDEX brin_test_toast_idx ON brintest_3 USING brin (b, c);
@@ -545,7 +545,7 @@ VACUUM brintest_3;
-- retry insert with a different random-looking (but deterministic) value
-- the value is different, and so should replace either min or max in the
-- brin summary
-WITH rand_value AS (SELECT string_agg(md5((-i)::text),'') AS val FROM generate_series(1,60) s(i))
+WITH rand_value AS (SELECT string_agg(notmd5((-i)::text),'') AS val FROM generate_series(1,60) s(i))
INSERT INTO brintest_3
SELECT val, val, val, val FROM rand_value;
-- now try some queries, accessing the brin index
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index f3309f433f..28d136f59c 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -29,7 +29,7 @@ INSERT INTO brintest_multi SELECT
(four + 1.0)/(hundred+1),
odd::float8 / (tenthous + 1),
format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
- substr(md5(unique1::text), 1, 16)::macaddr8,
+ substr(notmd5(unique1::text), 1, 16)::macaddr8,
inet '10.2.3.4/24' + tenthous,
cidr '10.2.3/24' + tenthous,
date '1995-08-15' + tenthous,
@@ -179,7 +179,7 @@ INSERT INTO brinopers_multi VALUES
('macaddr8col', 'macaddr8',
'{>, >=, =, <=, <}',
'{b1:d1:0e:7b:af:a4:42:12, d9:35:91:bd:f7:86:0e:1e, 72:8f:20:6c:2a:01:bf:57, 23:e8:46:63:86:07:ad:cb,
13:16:8e:6a:2e:6c:84:b4}',
- '{33, 15, 1, 13, 6}'),
+ '{31, 17, 1, 11, 4}'),
('inetcol', 'inet',
'{=, <, <=, >, >=}',
'{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
@@ -327,7 +327,7 @@ INSERT INTO brintest_multi SELECT
(four + 1.0)/(hundred+1),
odd::float8 / (tenthous + 1),
format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
- substr(md5(unique1::text), 1, 16)::macaddr8,
+ substr(notmd5(unique1::text), 1, 16)::macaddr8,
inet '10.2.3.4' + tenthous,
cidr '10.2.3/24' + tenthous,
date '1995-08-15' + tenthous,
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index e06ac93a36..0e814e3192 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -102,7 +102,7 @@ SELECT pg_column_compression(f1) FROM cmmove2;
-- test externally stored compressed data
CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS
-'select array_agg(md5(g::text))::text from generate_series(1, 256) g';
+'select array_agg(notmd5(g::text))::text from generate_series(1, 256) g';
CREATE TABLE cmdata2 (f1 text COMPRESSION pglz);
INSERT INTO cmdata2 SELECT large_val() || repeat('a', 4000);
SELECT pg_column_compression(f1) FROM cmdata2;
@@ -123,13 +123,13 @@ SELECT SUBSTR(f1, 200, 5) FROM cmdata1;
substr
--------
01234
- 8f14e
+ 79026
(2 rows)
SELECT SUBSTR(f1, 200, 5) FROM cmdata2;
substr
--------
- 8f14e
+ 79026
(1 row)
DROP TABLE cmdata2;
@@ -317,7 +317,7 @@ SELECT pg_column_compression(f1) FROM cmdata;
DROP TABLE cmdata2;
CREATE TABLE cmdata2 (f1 TEXT COMPRESSION pglz, f2 TEXT COMPRESSION lz4);
CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2));
-INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM
+INSERT INTO cmdata2 VALUES((SELECT array_agg(notmd5(g::TEXT))::TEXT FROM
generate_series(1, 50) g), VERSION());
-- check data is ok
SELECT length(f1) FROM cmdata;
diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out
index c0a47646eb..5fde7bf94f 100644
--- a/src/test/regress/expected/compression_1.out
+++ b/src/test/regress/expected/compression_1.out
@@ -102,7 +102,7 @@ SELECT pg_column_compression(f1) FROM cmmove2;
-- test externally stored compressed data
CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS
-'select array_agg(md5(g::text))::text from generate_series(1, 256) g';
+'select array_agg(notmd5(g::text))::text from generate_series(1, 256) g';
CREATE TABLE cmdata2 (f1 text COMPRESSION pglz);
INSERT INTO cmdata2 SELECT large_val() || repeat('a', 4000);
SELECT pg_column_compression(f1) FROM cmdata2;
@@ -126,7 +126,7 @@ LINE 1: SELECT SUBSTR(f1, 200, 5) FROM cmdata1;
SELECT SUBSTR(f1, 200, 5) FROM cmdata2;
substr
--------
- 8f14e
+ 79026
(1 row)
DROP TABLE cmdata2;
@@ -312,10 +312,10 @@ ERROR: compression method lz4 not supported
DETAIL: This functionality requires the server to be built with lz4 support.
CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2));
ERROR: relation "cmdata2" does not exist
-INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM
+INSERT INTO cmdata2 VALUES((SELECT array_agg(notmd5(g::TEXT))::TEXT FROM
generate_series(1, 50) g), VERSION());
ERROR: relation "cmdata2" does not exist
-LINE 1: INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::...
+LINE 1: INSERT INTO cmdata2 VALUES((SELECT array_agg(notmd5(g::TEXT)...
^
-- check data is ok
SELECT length(f1) FROM cmdata;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index e2a0dc80b2..9f98f33b0d 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2560,7 +2560,7 @@ alter table permtest_child attach partition permtest_grandchild for values in ('
alter table permtest_parent attach partition permtest_child for values in (1);
create index on permtest_parent (left(c, 3));
insert into permtest_parent
- select 1, 'a', left(md5(i::text), 5) from generate_series(0, 100) i;
+ select 1, 'a', left(notmd5(i::text), 5) from generate_series(0, 100) i;
analyze permtest_parent;
create role regress_no_child_access;
revoke all on permtest_grandchild from regress_no_child_access;
diff --git a/src/test/regress/expected/largeobject.out b/src/test/regress/expected/largeobject.out
index 31fba2ff9d..af65664eb7 100644
--- a/src/test/regress/expected/largeobject.out
+++ b/src/test/regress/expected/largeobject.out
@@ -441,7 +441,7 @@ TRUNCATE lotest_stash_values;
\set newloid_1 :LASTOID
SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2
\gset
-SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+SELECT notmd5(lo_get(:newloid_1)) = notmd5(lo_get(:newloid_2));
?column?
----------
t
diff --git a/src/test/regress/expected/largeobject_1.out b/src/test/regress/expected/largeobject_1.out
index 7acd7f73e1..10f1782eeb 100644
--- a/src/test/regress/expected/largeobject_1.out
+++ b/src/test/regress/expected/largeobject_1.out
@@ -441,7 +441,7 @@ TRUNCATE lotest_stash_values;
\set newloid_1 :LASTOID
SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2
\gset
-SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+SELECT notmd5(lo_get(:newloid_1)) = notmd5(lo_get(:newloid_2));
?column?
----------
t
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 87b6e569a5..8dcf51ccd4 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -556,10 +556,10 @@ SET ROLE regress_user_mvtest;
-- duplicate all the aliases used in those queries
CREATE TABLE mvtest_foo_data AS SELECT i,
i+1 AS tid,
- md5(random()::text) AS mv,
- md5(random()::text) AS newdata,
- md5(random()::text) AS newdata2,
- md5(random()::text) AS diff
+ notmd5(random()::text) AS mv,
+ notmd5(random()::text) AS newdata,
+ notmd5(random()::text) AS newdata2,
+ notmd5(random()::text) AS diff
FROM generate_series(1, 10) i;
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 60cbdeec7a..42e4396269 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -164,7 +164,7 @@ DROP TABLE flt;
CREATE TABLE strtest (n name, t text);
CREATE INDEX strtest_n_idx ON strtest (n);
CREATE INDEX strtest_t_idx ON strtest (t);
-INSERT INTO strtest VALUES('one','one'),('two','two'),('three',repeat(md5('three'),100));
+INSERT INTO strtest VALUES('one','one'),('two','two'),('three',repeat(notmd5('three'),100));
-- duplicate rows so we get some cache hits
INSERT INTO strtest SELECT * FROM strtest;
ANALYZE strtest;
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index cdc519256a..8be2fa9b40 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -3404,22 +3404,22 @@ select * from ret_query1();
create type record_type as (x text, y int, z boolean);
create or replace function ret_query2(lim int) returns setof record_type as $$
begin
- return query select md5(s.x::text), s.x, s.x > 0
+ return query select notmd5(s.x::text), s.x, s.x > 0
from generate_series(-8, lim) s (x) where s.x % 2 = 0;
end;
$$ language plpgsql;
select * from ret_query2(8);
x | y | z
----------------------------------+----+---
- a8d2ec85eaf98407310b72eb73dda247 | -8 | f
- 596a3d04481816330f07e4f97510c28f | -6 | f
- 0267aaf632e87a63288a08331f22c7c3 | -4 | f
- 5d7b9adcbe1c629ec722529dd12e5129 | -2 | f
- cfcd208495d565ef66e7dff9f98764da | 0 | f
- c81e728d9d4c2f636f067f89cc14862c | 2 | t
- a87ff679a2f3e71d9181a67b7542122c | 4 | t
- 1679091c5a880faf6fb5e6087eb1b2dc | 6 | t
- c9f0f895fb98ab9159f51fd0297e236d | 8 | t
+ e91592205d3881e3ea35d66973bb4898 | -8 | f
+ 03b26944890929ff751653acb2f2af79 | -6 | f
+ e5e0093f285a4fb94c3fcc2ad7fd04ed | -4 | f
+ cf3bae39dd692048a8bf961182e6a34d | -2 | f
+ 5feceb66ffc86f38d952786c6d696c79 | 0 | f
+ d4735e3a265e16eee03f59718b9b5d03 | 2 | t
+ 4b227777d4dd1fc61c6f884f48641d02 | 4 | t
+ e7f6c011776e8db7cd330b54174fd76f | 6 | t
+ 2c624232cdd221771294dfbb310aca00 | 8 | t
(9 rows)
-- test EXECUTE USING
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index a415ad168c..2a7160c1b9 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -1408,9 +1408,9 @@ ERROR: infinite recursion detected in policy for relation "rec1"
--
SET SESSION AUTHORIZATION regress_rls_alice;
CREATE TABLE s1 (a int, b text);
-INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
+INSERT INTO s1 (SELECT x, public.notmd5(x::text) FROM generate_series(-10,10) x);
CREATE TABLE s2 (x int, y text);
-INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
+INSERT INTO s2 (SELECT x, public.notmd5(x::text) FROM generate_series(-6,6) x);
GRANT SELECT ON s1, s2 TO regress_rls_bob;
CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
@@ -1428,13 +1428,11 @@ DROP POLICY p3 on s1;
ALTER POLICY p2 ON s2 USING (x % 2 = 0);
SET SESSION AUTHORIZATION regress_rls_bob;
SELECT * FROM s1 WHERE f_leak(b); -- OK
-NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
-NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
- a | b
----+----------------------------------
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 4 | a87ff679a2f3e71d9181a67b7542122c
-(2 rows)
+NOTICE: f_leak => 03b26944890929ff751653acb2f2af79
+ a | b
+----+----------------------------------
+ -6 | 03b26944890929ff751653acb2f2af79
+(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
QUERY PLAN
@@ -1450,13 +1448,11 @@ SET SESSION AUTHORIZATION regress_rls_alice;
ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy
SET SESSION AUTHORIZATION regress_rls_bob;
SELECT * FROM s1 WHERE f_leak(b); -- OK
-NOTICE: f_leak => 0267aaf632e87a63288a08331f22c7c3
-NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
+NOTICE: f_leak => 03b26944890929ff751653acb2f2af79
a | b
----+----------------------------------
- -4 | 0267aaf632e87a63288a08331f22c7c3
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
-(2 rows)
+ -6 | 03b26944890929ff751653acb2f2af79
+(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
QUERY PLAN
@@ -1471,10 +1467,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
xx | x | y
----+----+----------------------------------
- -6 | -6 | 596a3d04481816330f07e4f97510c28f
- -4 | -4 | 0267aaf632e87a63288a08331f22c7c3
- 2 | 2 | c81e728d9d4c2f636f067f89cc14862c
-(3 rows)
+ -4 | -4 | e5e0093f285a4fb94c3fcc2ad7fd04ed
+(1 row)
EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
QUERY PLAN
@@ -1900,7 +1894,7 @@ NOTICE: f_leak => yyyyyy
--
SET SESSION AUTHORIZATION regress_rls_alice;
CREATE TABLE b1 (a int, b text);
-INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
+INSERT INTO b1 (SELECT x, public.notmd5(x::text) FROM generate_series(-10,10) x);
CREATE POLICY p1 ON b1 USING (a % 2 = 0);
ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
GRANT ALL ON b1 TO regress_rls_bob;
@@ -1918,18 +1912,18 @@ EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
(4 rows)
SELECT * FROM bv1 WHERE f_leak(b);
-NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
-NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
-NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
-NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
-NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
+NOTICE: f_leak => d4735e3a265e16eee03f59718b9b5d03
+NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
+NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
+NOTICE: f_leak => 2c624232cdd221771294dfbb310aca00
+NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
a | b
----+----------------------------------
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 10 | d3d9446802a44259755d38e6d163e820
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 10 | 4a44dc15364204a80fe80e9039455cc1
(5 rows)
INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO
@@ -1946,7 +1940,7 @@ EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
(3 rows)
UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
-NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
+NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
QUERY PLAN
-----------------------------------------------------------------------
@@ -1956,30 +1950,30 @@ EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
(3 rows)
DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
-NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
+NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
SET SESSION AUTHORIZATION regress_rls_alice;
SELECT * FROM b1;
a | b
-----+----------------------------------
- -10 | 1b0fd9efa5279c4203b7c70233f86dbf
- -9 | 252e691406782824eec43d7eadc3d256
- -8 | a8d2ec85eaf98407310b72eb73dda247
- -7 | 74687a12d3915d3c4d83f1af7b3683d5
- -6 | 596a3d04481816330f07e4f97510c28f
- -5 | 47c1b025fa18ea96c33fbb6718688c0f
- -4 | 0267aaf632e87a63288a08331f22c7c3
- -3 | b3149ecea4628efd23d2f86e5a723472
- -2 | 5d7b9adcbe1c629ec722529dd12e5129
- -1 | 6bb61e3b7bce0931da574d19d1d82c88
- 0 | cfcd208495d565ef66e7dff9f98764da
- 1 | c4ca4238a0b923820dcc509a6f75849b
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
- 5 | e4da3b7fbbce2345d7772b0674a318d5
- 7 | 8f14e45fceea167a5a36dedd4bea2543
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
- 10 | d3d9446802a44259755d38e6d163e820
+ -10 | c171d4ec282b23db89a99880cd624e9b
+ -9 | d5c534fde62beb89c745a59952c8efed
+ -8 | e91592205d3881e3ea35d66973bb4898
+ -7 | a770d3270c9dcdedf12ed9fd70444f7c
+ -6 | 03b26944890929ff751653acb2f2af79
+ -5 | 37aa1ccf80e481832b2db282d4d4f895
+ -4 | e5e0093f285a4fb94c3fcc2ad7fd04ed
+ -3 | 615bdd17c2556f82f384392ea8557f8c
+ -2 | cf3bae39dd692048a8bf961182e6a34d
+ -1 | 1bad6b8cf97131fceab8543e81f77571
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 1 | 6b86b273ff34fce19d6b804eff5a3f57
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 3 | 4e07408562bedb8b60ce05c1decfe3ad
+ 5 | ef2d127de37b942baad06145e54b0c61
+ 7 | 7902699be42c8a8e46fbbb4501726517
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 9 | 19581e27de7ced00ff1ce50b2047e7a5
+ 10 | 4a44dc15364204a80fe80e9039455cc1
12 | xxx
4 | yyy
(21 rows)
@@ -3038,41 +3032,41 @@ DROP VIEW rls_sbv;
-- Expression structure
--
SET SESSION AUTHORIZATION regress_rls_alice;
-INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
+INSERT INTO y2 (SELECT x, public.notmd5(x::text) FROM generate_series(0,20) x);
CREATE POLICY p2 ON y2 USING (a % 3 = 0);
CREATE POLICY p3 ON y2 USING (a % 4 = 0);
SET SESSION AUTHORIZATION regress_rls_bob;
SELECT * FROM y2 WHERE f_leak(b);
-NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
-NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
-NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
-NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
-NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
-NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
-NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
-NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
-NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
-NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
-NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
-NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
-NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
-NOTICE: f_leak => 98f13708210194c475687be6106a3b84
+NOTICE: f_leak => 5feceb66ffc86f38d952786c6d696c79
+NOTICE: f_leak => d4735e3a265e16eee03f59718b9b5d03
+NOTICE: f_leak => 4e07408562bedb8b60ce05c1decfe3ad
+NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
+NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
+NOTICE: f_leak => 2c624232cdd221771294dfbb310aca00
+NOTICE: f_leak => 19581e27de7ced00ff1ce50b2047e7a5
+NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
+NOTICE: f_leak => 6b51d431df5d7f141cbececcf79edf3d
+NOTICE: f_leak => 8527a891e224136950ff32ca212b45bc
+NOTICE: f_leak => e629fa6598d732768f7c726b4b621285
+NOTICE: f_leak => b17ef6d19c7a5b1ee83b907c595526dc
+NOTICE: f_leak => 4ec9599fc203d176a301536c2e091a19
+NOTICE: f_leak => f5ca38f748a1d6eaf726b8a42fb575c3
a | b
----+----------------------------------
- 0 | cfcd208495d565ef66e7dff9f98764da
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
- 10 | d3d9446802a44259755d38e6d163e820
- 12 | c20ad4d76fe97759aa27a0c99bff6710
- 14 | aab3238922bcc25a6f606eb525ffdc56
- 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
- 16 | c74d97b01eae257e44aa9d5bade97baf
- 18 | 6f4922f45568161a8cdf4ad2299f6d23
- 20 | 98f13708210194c475687be6106a3b84
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 3 | 4e07408562bedb8b60ce05c1decfe3ad
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 9 | 19581e27de7ced00ff1ce50b2047e7a5
+ 10 | 4a44dc15364204a80fe80e9039455cc1
+ 12 | 6b51d431df5d7f141cbececcf79edf3d
+ 14 | 8527a891e224136950ff32ca212b45bc
+ 15 | e629fa6598d732768f7c726b4b621285
+ 16 | b17ef6d19c7a5b1ee83b907c595526dc
+ 18 | 4ec9599fc203d176a301536c2e091a19
+ 20 | f5ca38f748a1d6eaf726b8a42fb575c3
(14 rows)
EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
@@ -3109,20 +3103,20 @@ NOTICE: f_leak => abc
NOTICE: f_leak => abc
a | b
----+----------------------------------
- 0 | cfcd208495d565ef66e7dff9f98764da
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
- 10 | d3d9446802a44259755d38e6d163e820
- 12 | c20ad4d76fe97759aa27a0c99bff6710
- 14 | aab3238922bcc25a6f606eb525ffdc56
- 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
- 16 | c74d97b01eae257e44aa9d5bade97baf
- 18 | 6f4922f45568161a8cdf4ad2299f6d23
- 20 | 98f13708210194c475687be6106a3b84
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 3 | 4e07408562bedb8b60ce05c1decfe3ad
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 9 | 19581e27de7ced00ff1ce50b2047e7a5
+ 10 | 4a44dc15364204a80fe80e9039455cc1
+ 12 | 6b51d431df5d7f141cbececcf79edf3d
+ 14 | 8527a891e224136950ff32ca212b45bc
+ 15 | e629fa6598d732768f7c726b4b621285
+ 16 | b17ef6d19c7a5b1ee83b907c595526dc
+ 18 | 4ec9599fc203d176a301536c2e091a19
+ 20 | f5ca38f748a1d6eaf726b8a42fb575c3
(14 rows)
EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc');
@@ -3156,20 +3150,20 @@ EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE
(7 rows)
SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
-NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
-NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
-NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
-NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
-NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
-NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
-NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
-NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
-NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
-NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
-NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
-NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
-NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
-NOTICE: f_leak => 98f13708210194c475687be6106a3b84
+NOTICE: f_leak => 5feceb66ffc86f38d952786c6d696c79
+NOTICE: f_leak => d4735e3a265e16eee03f59718b9b5d03
+NOTICE: f_leak => 4e07408562bedb8b60ce05c1decfe3ad
+NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
+NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
+NOTICE: f_leak => 2c624232cdd221771294dfbb310aca00
+NOTICE: f_leak => 19581e27de7ced00ff1ce50b2047e7a5
+NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
+NOTICE: f_leak => 6b51d431df5d7f141cbececcf79edf3d
+NOTICE: f_leak => 8527a891e224136950ff32ca212b45bc
+NOTICE: f_leak => e629fa6598d732768f7c726b4b621285
+NOTICE: f_leak => b17ef6d19c7a5b1ee83b907c595526dc
+NOTICE: f_leak => 4ec9599fc203d176a301536c2e091a19
+NOTICE: f_leak => f5ca38f748a1d6eaf726b8a42fb575c3
a | b | abc
---+---+-----
(0 rows)
@@ -3239,33 +3233,33 @@ CREATE TABLE t1 (a integer, b text);
CREATE POLICY p1 ON t1 USING (a % 2 = 0);
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
GRANT ALL ON t1 TO regress_rls_bob;
-INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
+INSERT INTO t1 (SELECT x, public.notmd5(x::text) FROM generate_series(0,20) x);
SET SESSION AUTHORIZATION regress_rls_bob;
WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
-NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
-NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
-NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
-NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
-NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
-NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
-NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
-NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
-NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
-NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
-NOTICE: f_leak => 98f13708210194c475687be6106a3b84
+NOTICE: f_leak => 5feceb66ffc86f38d952786c6d696c79
+NOTICE: f_leak => d4735e3a265e16eee03f59718b9b5d03
+NOTICE: f_leak => 4b227777d4dd1fc61c6f884f48641d02
+NOTICE: f_leak => e7f6c011776e8db7cd330b54174fd76f
+NOTICE: f_leak => 2c624232cdd221771294dfbb310aca00
+NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
+NOTICE: f_leak => 6b51d431df5d7f141cbececcf79edf3d
+NOTICE: f_leak => 8527a891e224136950ff32ca212b45bc
+NOTICE: f_leak => b17ef6d19c7a5b1ee83b907c595526dc
+NOTICE: f_leak => 4ec9599fc203d176a301536c2e091a19
+NOTICE: f_leak => f5ca38f748a1d6eaf726b8a42fb575c3
a | b
----+----------------------------------
- 0 | cfcd208495d565ef66e7dff9f98764da
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 10 | d3d9446802a44259755d38e6d163e820
- 12 | c20ad4d76fe97759aa27a0c99bff6710
- 14 | aab3238922bcc25a6f606eb525ffdc56
- 16 | c74d97b01eae257e44aa9d5bade97baf
- 18 | 6f4922f45568161a8cdf4ad2299f6d23
- 20 | 98f13708210194c475687be6106a3b84
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 10 | 4a44dc15364204a80fe80e9039455cc1
+ 12 | 6b51d431df5d7f141cbececcf79edf3d
+ 14 | 8527a891e224136950ff32ca212b45bc
+ 16 | b17ef6d19c7a5b1ee83b907c595526dc
+ 18 | 4ec9599fc203d176a301536c2e091a19
+ 20 | f5ca38f748a1d6eaf726b8a42fb575c3
(11 rows)
EXPLAIN (COSTS OFF)
@@ -3283,17 +3277,17 @@ ERROR: new row violates row-level security policy for table "t1"
WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
a | b
----+----------------------------------
- 0 | cfcd208495d565ef66e7dff9f98764da
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 10 | d3d9446802a44259755d38e6d163e820
- 12 | c20ad4d76fe97759aa27a0c99bff6710
- 14 | aab3238922bcc25a6f606eb525ffdc56
- 16 | c74d97b01eae257e44aa9d5bade97baf
- 18 | 6f4922f45568161a8cdf4ad2299f6d23
- 20 | 98f13708210194c475687be6106a3b84
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 10 | 4a44dc15364204a80fe80e9039455cc1
+ 12 | 6b51d431df5d7f141cbececcf79edf3d
+ 14 | 8527a891e224136950ff32ca212b45bc
+ 16 | b17ef6d19c7a5b1ee83b907c595526dc
+ 18 | 4ec9599fc203d176a301536c2e091a19
+ 20 | f5ca38f748a1d6eaf726b8a42fb575c3
(11 rows)
WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail
@@ -3346,17 +3340,17 @@ EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
SELECT * FROM t2;
a | b
----+----------------------------------
- 0 | cfcd208495d565ef66e7dff9f98764da
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 10 | d3d9446802a44259755d38e6d163e820
- 12 | c20ad4d76fe97759aa27a0c99bff6710
- 14 | aab3238922bcc25a6f606eb525ffdc56
- 16 | c74d97b01eae257e44aa9d5bade97baf
- 18 | 6f4922f45568161a8cdf4ad2299f6d23
- 20 | 98f13708210194c475687be6106a3b84
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 10 | 4a44dc15364204a80fe80e9039455cc1
+ 12 | 6b51d431df5d7f141cbececcf79edf3d
+ 14 | 8527a891e224136950ff32ca212b45bc
+ 16 | b17ef6d19c7a5b1ee83b907c595526dc
+ 18 | 4ec9599fc203d176a301536c2e091a19
+ 20 | f5ca38f748a1d6eaf726b8a42fb575c3
20 | Success
(12 rows)
@@ -3370,17 +3364,17 @@ CREATE TABLE t3 AS SELECT * FROM t1;
SELECT * FROM t3;
a | b
----+----------------------------------
- 0 | cfcd208495d565ef66e7dff9f98764da
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 10 | d3d9446802a44259755d38e6d163e820
- 12 | c20ad4d76fe97759aa27a0c99bff6710
- 14 | aab3238922bcc25a6f606eb525ffdc56
- 16 | c74d97b01eae257e44aa9d5bade97baf
- 18 | 6f4922f45568161a8cdf4ad2299f6d23
- 20 | 98f13708210194c475687be6106a3b84
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 10 | 4a44dc15364204a80fe80e9039455cc1
+ 12 | 6b51d431df5d7f141cbececcf79edf3d
+ 14 | 8527a891e224136950ff32ca212b45bc
+ 16 | b17ef6d19c7a5b1ee83b907c595526dc
+ 18 | 4ec9599fc203d176a301536c2e091a19
+ 20 | f5ca38f748a1d6eaf726b8a42fb575c3
20 | Success
(12 rows)
@@ -3388,17 +3382,17 @@ SELECT * INTO t4 FROM t1;
SELECT * FROM t4;
a | b
----+----------------------------------
- 0 | cfcd208495d565ef66e7dff9f98764da
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 10 | d3d9446802a44259755d38e6d163e820
- 12 | c20ad4d76fe97759aa27a0c99bff6710
- 14 | aab3238922bcc25a6f606eb525ffdc56
- 16 | c74d97b01eae257e44aa9d5bade97baf
- 18 | 6f4922f45568161a8cdf4ad2299f6d23
- 20 | 98f13708210194c475687be6106a3b84
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 10 | 4a44dc15364204a80fe80e9039455cc1
+ 12 | 6b51d431df5d7f141cbececcf79edf3d
+ 14 | 8527a891e224136950ff32ca212b45bc
+ 16 | b17ef6d19c7a5b1ee83b907c595526dc
+ 18 | 4ec9599fc203d176a301536c2e091a19
+ 20 | f5ca38f748a1d6eaf726b8a42fb575c3
20 | Success
(12 rows)
@@ -3471,27 +3465,27 @@ RESET SESSION AUTHORIZATION;
SELECT * FROM t1;
a | b
----+----------------------------------
- 1 | c4ca4238a0b923820dcc509a6f75849b
- 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
- 5 | e4da3b7fbbce2345d7772b0674a318d5
- 7 | 8f14e45fceea167a5a36dedd4bea2543
- 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
- 11 | 6512bd43d9caa6e02c990b0a82652dca
- 13 | c51ce410c124a10e0db5e4b97fc2af39
- 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
- 17 | 70efdf2ec9b086079795c442636b55fb
- 19 | 1f0e3dad99908345f7439f8ffabdffc4
- 0 | cfcd208495d565ef66e7dff9f98764da
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 10 | d3d9446802a44259755d38e6d163e820
- 12 | c20ad4d76fe97759aa27a0c99bff6710
- 14 | aab3238922bcc25a6f606eb525ffdc56
- 16 | c74d97b01eae257e44aa9d5bade97baf
- 18 | 6f4922f45568161a8cdf4ad2299f6d23
- 20 | 98f13708210194c475687be6106a3b84
+ 1 | 6b86b273ff34fce19d6b804eff5a3f57
+ 3 | 4e07408562bedb8b60ce05c1decfe3ad
+ 5 | ef2d127de37b942baad06145e54b0c61
+ 7 | 7902699be42c8a8e46fbbb4501726517
+ 9 | 19581e27de7ced00ff1ce50b2047e7a5
+ 11 | 4fc82b26aecb47d2868c4efbe3581732
+ 13 | 3fdba35f04dc8c462986c992bcf87554
+ 15 | e629fa6598d732768f7c726b4b621285
+ 17 | 4523540f1504cd17100c4835e85b7eef
+ 19 | 9400f1b21cb527d7fa3d3eabba93557a
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 10 | 4a44dc15364204a80fe80e9039455cc1
+ 12 | 6b51d431df5d7f141cbececcf79edf3d
+ 14 | 8527a891e224136950ff32ca212b45bc
+ 16 | b17ef6d19c7a5b1ee83b907c595526dc
+ 18 | 4ec9599fc203d176a301536c2e091a19
+ 20 | f5ca38f748a1d6eaf726b8a42fb575c3
20 | Success
(22 rows)
@@ -3506,27 +3500,27 @@ SET SESSION AUTHORIZATION regress_rls_alice;
SELECT * FROM t1;
a | b
----+----------------------------------
- 1 | c4ca4238a0b923820dcc509a6f75849b
- 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
- 5 | e4da3b7fbbce2345d7772b0674a318d5
- 7 | 8f14e45fceea167a5a36dedd4bea2543
- 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
- 11 | 6512bd43d9caa6e02c990b0a82652dca
- 13 | c51ce410c124a10e0db5e4b97fc2af39
- 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
- 17 | 70efdf2ec9b086079795c442636b55fb
- 19 | 1f0e3dad99908345f7439f8ffabdffc4
- 0 | cfcd208495d565ef66e7dff9f98764da
- 2 | c81e728d9d4c2f636f067f89cc14862c
- 4 | a87ff679a2f3e71d9181a67b7542122c
- 6 | 1679091c5a880faf6fb5e6087eb1b2dc
- 8 | c9f0f895fb98ab9159f51fd0297e236d
- 10 | d3d9446802a44259755d38e6d163e820
- 12 | c20ad4d76fe97759aa27a0c99bff6710
- 14 | aab3238922bcc25a6f606eb525ffdc56
- 16 | c74d97b01eae257e44aa9d5bade97baf
- 18 | 6f4922f45568161a8cdf4ad2299f6d23
- 20 | 98f13708210194c475687be6106a3b84
+ 1 | 6b86b273ff34fce19d6b804eff5a3f57
+ 3 | 4e07408562bedb8b60ce05c1decfe3ad
+ 5 | ef2d127de37b942baad06145e54b0c61
+ 7 | 7902699be42c8a8e46fbbb4501726517
+ 9 | 19581e27de7ced00ff1ce50b2047e7a5
+ 11 | 4fc82b26aecb47d2868c4efbe3581732
+ 13 | 3fdba35f04dc8c462986c992bcf87554
+ 15 | e629fa6598d732768f7c726b4b621285
+ 17 | 4523540f1504cd17100c4835e85b7eef
+ 19 | 9400f1b21cb527d7fa3d3eabba93557a
+ 0 | 5feceb66ffc86f38d952786c6d696c79
+ 2 | d4735e3a265e16eee03f59718b9b5d03
+ 4 | 4b227777d4dd1fc61c6f884f48641d02
+ 6 | e7f6c011776e8db7cd330b54174fd76f
+ 8 | 2c624232cdd221771294dfbb310aca00
+ 10 | 4a44dc15364204a80fe80e9039455cc1
+ 12 | 6b51d431df5d7f141cbececcf79edf3d
+ 14 | 8527a891e224136950ff32ca212b45bc
+ 16 | b17ef6d19c7a5b1ee83b907c595526dc
+ 18 | 4ec9599fc203d176a301536c2e091a19
+ 20 | f5ca38f748a1d6eaf726b8a42fb575c3
20 | Success
(22 rows)
@@ -3574,35 +3568,35 @@ CREATE TABLE copy_t (a integer, b text);
CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user;
-INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
+INSERT INTO copy_t (SELECT x, public.notmd5(x::text) FROM generate_series(0,10) x);
-- Check COPY TO as Superuser/owner.
RESET SESSION AUTHORIZATION;
SET row_security TO OFF;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
-0,cfcd208495d565ef66e7dff9f98764da
-1,c4ca4238a0b923820dcc509a6f75849b
-2,c81e728d9d4c2f636f067f89cc14862c
-3,eccbc87e4b5ce2fe28308fd9f2a7baf3
-4,a87ff679a2f3e71d9181a67b7542122c
-5,e4da3b7fbbce2345d7772b0674a318d5
-6,1679091c5a880faf6fb5e6087eb1b2dc
-7,8f14e45fceea167a5a36dedd4bea2543
-8,c9f0f895fb98ab9159f51fd0297e236d
-9,45c48cce2e2d7fbdea1afc51c7c6ad26
-10,d3d9446802a44259755d38e6d163e820
+0,5feceb66ffc86f38d952786c6d696c79
+1,6b86b273ff34fce19d6b804eff5a3f57
+2,d4735e3a265e16eee03f59718b9b5d03
+3,4e07408562bedb8b60ce05c1decfe3ad
+4,4b227777d4dd1fc61c6f884f48641d02
+5,ef2d127de37b942baad06145e54b0c61
+6,e7f6c011776e8db7cd330b54174fd76f
+7,7902699be42c8a8e46fbbb4501726517
+8,2c624232cdd221771294dfbb310aca00
+9,19581e27de7ced00ff1ce50b2047e7a5
+10,4a44dc15364204a80fe80e9039455cc1
SET row_security TO ON;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
-0,cfcd208495d565ef66e7dff9f98764da
-1,c4ca4238a0b923820dcc509a6f75849b
-2,c81e728d9d4c2f636f067f89cc14862c
-3,eccbc87e4b5ce2fe28308fd9f2a7baf3
-4,a87ff679a2f3e71d9181a67b7542122c
-5,e4da3b7fbbce2345d7772b0674a318d5
-6,1679091c5a880faf6fb5e6087eb1b2dc
-7,8f14e45fceea167a5a36dedd4bea2543
-8,c9f0f895fb98ab9159f51fd0297e236d
-9,45c48cce2e2d7fbdea1afc51c7c6ad26
-10,d3d9446802a44259755d38e6d163e820
+0,5feceb66ffc86f38d952786c6d696c79
+1,6b86b273ff34fce19d6b804eff5a3f57
+2,d4735e3a265e16eee03f59718b9b5d03
+3,4e07408562bedb8b60ce05c1decfe3ad
+4,4b227777d4dd1fc61c6f884f48641d02
+5,ef2d127de37b942baad06145e54b0c61
+6,e7f6c011776e8db7cd330b54174fd76f
+7,7902699be42c8a8e46fbbb4501726517
+8,2c624232cdd221771294dfbb310aca00
+9,19581e27de7ced00ff1ce50b2047e7a5
+10,4a44dc15364204a80fe80e9039455cc1
-- Check COPY TO as user with permissions.
SET SESSION AUTHORIZATION regress_rls_bob;
SET row_security TO OFF;
@@ -3610,40 +3604,40 @@ COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail
ERROR: query would be affected by row-level security policy for table "copy_t"
SET row_security TO ON;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
-0,cfcd208495d565ef66e7dff9f98764da
-2,c81e728d9d4c2f636f067f89cc14862c
-4,a87ff679a2f3e71d9181a67b7542122c
-6,1679091c5a880faf6fb5e6087eb1b2dc
-8,c9f0f895fb98ab9159f51fd0297e236d
-10,d3d9446802a44259755d38e6d163e820
+0,5feceb66ffc86f38d952786c6d696c79
+2,d4735e3a265e16eee03f59718b9b5d03
+4,4b227777d4dd1fc61c6f884f48641d02
+6,e7f6c011776e8db7cd330b54174fd76f
+8,2c624232cdd221771294dfbb310aca00
+10,4a44dc15364204a80fe80e9039455cc1
-- Check COPY TO as user with permissions and BYPASSRLS
SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
-0,cfcd208495d565ef66e7dff9f98764da
-1,c4ca4238a0b923820dcc509a6f75849b
-2,c81e728d9d4c2f636f067f89cc14862c
-3,eccbc87e4b5ce2fe28308fd9f2a7baf3
-4,a87ff679a2f3e71d9181a67b7542122c
-5,e4da3b7fbbce2345d7772b0674a318d5
-6,1679091c5a880faf6fb5e6087eb1b2dc
-7,8f14e45fceea167a5a36dedd4bea2543
-8,c9f0f895fb98ab9159f51fd0297e236d
-9,45c48cce2e2d7fbdea1afc51c7c6ad26
-10,d3d9446802a44259755d38e6d163e820
+0,5feceb66ffc86f38d952786c6d696c79
+1,6b86b273ff34fce19d6b804eff5a3f57
+2,d4735e3a265e16eee03f59718b9b5d03
+3,4e07408562bedb8b60ce05c1decfe3ad
+4,4b227777d4dd1fc61c6f884f48641d02
+5,ef2d127de37b942baad06145e54b0c61
+6,e7f6c011776e8db7cd330b54174fd76f
+7,7902699be42c8a8e46fbbb4501726517
+8,2c624232cdd221771294dfbb310aca00
+9,19581e27de7ced00ff1ce50b2047e7a5
+10,4a44dc15364204a80fe80e9039455cc1
SET row_security TO ON;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
-0,cfcd208495d565ef66e7dff9f98764da
-1,c4ca4238a0b923820dcc509a6f75849b
-2,c81e728d9d4c2f636f067f89cc14862c
-3,eccbc87e4b5ce2fe28308fd9f2a7baf3
-4,a87ff679a2f3e71d9181a67b7542122c
-5,e4da3b7fbbce2345d7772b0674a318d5
-6,1679091c5a880faf6fb5e6087eb1b2dc
-7,8f14e45fceea167a5a36dedd4bea2543
-8,c9f0f895fb98ab9159f51fd0297e236d
-9,45c48cce2e2d7fbdea1afc51c7c6ad26
-10,d3d9446802a44259755d38e6d163e820
+0,5feceb66ffc86f38d952786c6d696c79
+1,6b86b273ff34fce19d6b804eff5a3f57
+2,d4735e3a265e16eee03f59718b9b5d03
+3,4e07408562bedb8b60ce05c1decfe3ad
+4,4b227777d4dd1fc61c6f884f48641d02
+5,ef2d127de37b942baad06145e54b0c61
+6,e7f6c011776e8db7cd330b54174fd76f
+7,7902699be42c8a8e46fbbb4501726517
+8,2c624232cdd221771294dfbb310aca00
+9,19581e27de7ced00ff1ce50b2047e7a5
+10,4a44dc15364204a80fe80e9039455cc1
-- Check COPY TO as user without permissions. SET row_security TO OFF;
SET SESSION AUTHORIZATION regress_rls_carol;
SET row_security TO OFF;
@@ -3659,15 +3653,15 @@ CREATE TABLE copy_rel_to (a integer, b text);
CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0);
ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
GRANT ALL ON copy_rel_to TO regress_rls_bob, regress_rls_exempt_user;
-INSERT INTO copy_rel_to VALUES (1, md5('1'));
+INSERT INTO copy_rel_to VALUES (1, public.notmd5('1'));
-- Check COPY TO as Superuser/owner.
RESET SESSION AUTHORIZATION;
SET row_security TO OFF;
COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
-1,c4ca4238a0b923820dcc509a6f75849b
+1,6b86b273ff34fce19d6b804eff5a3f57
SET row_security TO ON;
COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
-1,c4ca4238a0b923820dcc509a6f75849b
+1,6b86b273ff34fce19d6b804eff5a3f57
-- Check COPY TO as user with permissions.
SET SESSION AUTHORIZATION regress_rls_bob;
SET row_security TO OFF;
@@ -3679,10 +3673,10 @@ COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
-1,c4ca4238a0b923820dcc509a6f75849b
+1,6b86b273ff34fce19d6b804eff5a3f57
SET row_security TO ON;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
-1,c4ca4238a0b923820dcc509a6f75849b
+1,6b86b273ff34fce19d6b804eff5a3f57
-- Check COPY TO as user without permissions. SET row_security TO OFF;
SET SESSION AUTHORIZATION regress_rls_carol;
SET row_security TO OFF;
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 03880874c1..8777c9fc2b 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -2615,18 +2615,18 @@ CREATE TABLE mcv_lists_uuid (
WITH (autovacuum_enabled = off);
INSERT INTO mcv_lists_uuid (a, b, c)
SELECT
- md5(mod(i,100)::text)::uuid,
- md5(mod(i,50)::text)::uuid,
- md5(mod(i,25)::text)::uuid
+ notmd5(mod(i,100)::text)::uuid,
+ notmd5(mod(i,50)::text)::uuid,
+ notmd5(mod(i,25)::text)::uuid
FROM generate_series(1,5000) s(i);
ANALYZE mcv_lists_uuid;
-SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''
ANDb = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''
ANDb = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''');
estimated | actual
-----------+--------
1 | 50
(1 row)
-SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''
ANDb = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND c = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''
ANDb = ''e7f6c011-776e-8db7-cd33-0b54174fd76f'' AND c = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''');
estimated | actual
-----------+--------
1 | 50
@@ -2635,13 +2635,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''167
CREATE STATISTICS mcv_lists_uuid_stats (mcv) ON a, b, c
FROM mcv_lists_uuid;
ANALYZE mcv_lists_uuid;
-SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''
ANDb = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''
ANDb = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''');
estimated | actual
-----------+--------
50 | 50
(1 row)
-SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''
ANDb = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND c = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''
ANDb = ''e7f6c011-776e-8db7-cd33-0b54174fd76f'' AND c = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''');
estimated | actual
-----------+--------
50 | 50
@@ -2657,7 +2657,7 @@ CREATE TABLE mcv_lists_arrays (
WITH (autovacuum_enabled = off);
INSERT INTO mcv_lists_arrays (a, b, c)
SELECT
- ARRAY[md5((i/100)::text), md5((i/100-1)::text), md5((i/100+1)::text)],
+ ARRAY[notmd5((i/100)::text), notmd5((i/100-1)::text), notmd5((i/100+1)::text)],
ARRAY[(i/100-1)::numeric/1000, (i/100)::numeric/1000, (i/100+1)::numeric/1000],
ARRAY[(i/100-1), i/100, (i/100+1)]
FROM generate_series(1,5000) s(i);
@@ -3038,7 +3038,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 0 AND b =
DROP TABLE expr_stats;
-- statistics on expressions with different data types
CREATE TABLE expr_stats (a int, b name, c text);
-INSERT INTO expr_stats SELECT mod(i,10), md5(mod(i,10)::text), md5(mod(i,10)::text) FROM generate_series(1,1000) s(i);
+INSERT INTO expr_stats SELECT mod(i,10), notmd5(mod(i,10)::text), notmd5(mod(i,10)::text) FROM generate_series(1,1000)
s(i);
ANALYZE expr_stats;
SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 0 AND (b || c) <= ''z'' AND (c || b) >=
''0''');
estimated | actual
diff --git a/src/test/regress/expected/test_setup.out b/src/test/regress/expected/test_setup.out
index 4f54fe20ec..258d4cd60f 100644
--- a/src/test/regress/expected/test_setup.out
+++ b/src/test/regress/expected/test_setup.out
@@ -231,3 +231,15 @@ create function part_hashtext_length(value text, seed int8)
create operator class part_test_text_ops for type text using hash as
operator 1 =,
function 2 part_hashtext_length(text, int8);
+--
+-- These functions are used in tests that used to use md5(), which we now
+-- mostly avoid so that the tests will pass in FIPS mode.
+--
+create function notmd5(bytea)
+ returns text
+ strict immutable parallel safe leakproof
+ begin atomic; select substr(encode(sha256($1), 'hex'), 1, 32); end;
+create function notmd5(text)
+ returns text
+ strict immutable parallel safe leakproof
+ begin atomic; select substr(encode(sha256($1::bytea), 'hex'), 1, 32); end;
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 6ea4dba9f1..39c44ca47a 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -677,12 +677,12 @@ insert into src
create type textandtext as (c1 text, c2 text);
create temp table dest (f1 textandtext[]);
insert into dest select array[row(f1,f1)::textandtext] from src;
-select length(md5((f1[1]).c2)) from dest;
+select length(notmd5((f1[1]).c2)) from dest;
delete from src;
-select length(md5((f1[1]).c2)) from dest;
+select length(notmd5((f1[1]).c2)) from dest;
truncate table src;
drop table src;
-select length(md5((f1[1]).c2)) from dest;
+select length(notmd5((f1[1]).c2)) from dest;
drop table dest;
drop type textandtext;
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index e68e9e18df..aea5ed778b 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -476,7 +476,7 @@ CREATE TABLE brintest_3 (a text, b text, c text, d text);
-- long random strings (~2000 chars each, so ~6kB for min/max on two
-- columns) to trigger toasting
-WITH rand_value AS (SELECT string_agg(md5(i::text),'') AS val FROM generate_series(1,60) s(i))
+WITH rand_value AS (SELECT string_agg(notmd5(i::text),'') AS val FROM generate_series(1,60) s(i))
INSERT INTO brintest_3
SELECT val, val, val, val FROM rand_value;
@@ -495,7 +495,7 @@ VACUUM brintest_3;
-- retry insert with a different random-looking (but deterministic) value
-- the value is different, and so should replace either min or max in the
-- brin summary
-WITH rand_value AS (SELECT string_agg(md5((-i)::text),'') AS val FROM generate_series(1,60) s(i))
+WITH rand_value AS (SELECT string_agg(notmd5((-i)::text),'') AS val FROM generate_series(1,60) s(i))
INSERT INTO brintest_3
SELECT val, val, val, val FROM rand_value;
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index 2189b6ccf4..ad67bd8828 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -30,7 +30,7 @@ INSERT INTO brintest_multi SELECT
(four + 1.0)/(hundred+1),
odd::float8 / (tenthous + 1),
format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
- substr(md5(unique1::text), 1, 16)::macaddr8,
+ substr(notmd5(unique1::text), 1, 16)::macaddr8,
inet '10.2.3.4/24' + tenthous,
cidr '10.2.3/24' + tenthous,
date '1995-08-15' + tenthous,
@@ -183,7 +183,7 @@ INSERT INTO brinopers_multi VALUES
('macaddr8col', 'macaddr8',
'{>, >=, =, <=, <}',
'{b1:d1:0e:7b:af:a4:42:12, d9:35:91:bd:f7:86:0e:1e, 72:8f:20:6c:2a:01:bf:57, 23:e8:46:63:86:07:ad:cb,
13:16:8e:6a:2e:6c:84:b4}',
- '{33, 15, 1, 13, 6}'),
+ '{31, 17, 1, 11, 4}'),
('inetcol', 'inet',
'{=, <, <=, >, >=}',
'{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
@@ -334,7 +334,7 @@ INSERT INTO brintest_multi SELECT
(four + 1.0)/(hundred+1),
odd::float8 / (tenthous + 1),
format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
- substr(md5(unique1::text), 1, 16)::macaddr8,
+ substr(notmd5(unique1::text), 1, 16)::macaddr8,
inet '10.2.3.4' + tenthous,
cidr '10.2.3/24' + tenthous,
date '1995-08-15' + tenthous,
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql
index 86332dcc51..d8fd3be855 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression.sql
@@ -48,7 +48,7 @@ SELECT pg_column_compression(f1) FROM cmmove2;
-- test externally stored compressed data
CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS
-'select array_agg(md5(g::text))::text from generate_series(1, 256) g';
+'select array_agg(notmd5(g::text))::text from generate_series(1, 256) g';
CREATE TABLE cmdata2 (f1 text COMPRESSION pglz);
INSERT INTO cmdata2 SELECT large_val() || repeat('a', 4000);
SELECT pg_column_compression(f1) FROM cmdata2;
@@ -135,7 +135,7 @@ SELECT pg_column_compression(f1) FROM cmdata;
DROP TABLE cmdata2;
CREATE TABLE cmdata2 (f1 TEXT COMPRESSION pglz, f2 TEXT COMPRESSION lz4);
CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2));
-INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM
+INSERT INTO cmdata2 VALUES((SELECT array_agg(notmd5(g::TEXT))::TEXT FROM
generate_series(1, 50) g), VERSION());
-- check data is ok
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 5db6dbc191..211b45b9a6 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -920,7 +920,7 @@ alter table permtest_child attach partition permtest_grandchild for values in ('
alter table permtest_parent attach partition permtest_child for values in (1);
create index on permtest_parent (left(c, 3));
insert into permtest_parent
- select 1, 'a', left(md5(i::text), 5) from generate_series(0, 100) i;
+ select 1, 'a', left(notmd5(i::text), 5) from generate_series(0, 100) i;
analyze permtest_parent;
create role regress_no_child_access;
revoke all on permtest_grandchild from regress_no_child_access;
diff --git a/src/test/regress/sql/largeobject.sql b/src/test/regress/sql/largeobject.sql
index 15e0dff7a3..ae45688ae6 100644
--- a/src/test/regress/sql/largeobject.sql
+++ b/src/test/regress/sql/largeobject.sql
@@ -244,7 +244,7 @@ TRUNCATE lotest_stash_values;
SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2
\gset
-SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+SELECT notmd5(lo_get(:newloid_1)) = notmd5(lo_get(:newloid_2));
SELECT lo_get(:newloid_1, 0, 20);
SELECT lo_get(:newloid_1, 10, 20);
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 68b9ccfd45..4c43899757 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -216,10 +216,10 @@ SET ROLE regress_user_mvtest;
-- duplicate all the aliases used in those queries
CREATE TABLE mvtest_foo_data AS SELECT i,
i+1 AS tid,
- md5(random()::text) AS mv,
- md5(random()::text) AS newdata,
- md5(random()::text) AS newdata2,
- md5(random()::text) AS diff
+ notmd5(random()::text) AS mv,
+ notmd5(random()::text) AS newdata,
+ notmd5(random()::text) AS newdata2,
+ notmd5(random()::text) AS diff
FROM generate_series(1, 10) i;
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index d66acaed85..4235c4223c 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -91,7 +91,7 @@ DROP TABLE flt;
CREATE TABLE strtest (n name, t text);
CREATE INDEX strtest_n_idx ON strtest (n);
CREATE INDEX strtest_t_idx ON strtest (t);
-INSERT INTO strtest VALUES('one','one'),('two','two'),('three',repeat(md5('three'),100));
+INSERT INTO strtest VALUES('one','one'),('two','two'),('three',repeat(notmd5('three'),100));
-- duplicate rows so we get some cache hits
INSERT INTO strtest SELECT * FROM strtest;
ANALYZE strtest;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 9a53b15081..58d73761ed 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2877,7 +2877,7 @@ create type record_type as (x text, y int, z boolean);
create or replace function ret_query2(lim int) returns setof record_type as $$
begin
- return query select md5(s.x::text), s.x, s.x > 0
+ return query select notmd5(s.x::text), s.x, s.x > 0
from generate_series(-8, lim) s (x) where s.x % 2 = 0;
end;
$$ language plpgsql;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index b38fa8ed8f..ce5cf40640 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -534,10 +534,10 @@ SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
--
SET SESSION AUTHORIZATION regress_rls_alice;
CREATE TABLE s1 (a int, b text);
-INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
+INSERT INTO s1 (SELECT x, public.notmd5(x::text) FROM generate_series(-10,10) x);
CREATE TABLE s2 (x int, y text);
-INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
+INSERT INTO s2 (SELECT x, public.notmd5(x::text) FROM generate_series(-6,6) x);
GRANT SELECT ON s1, s2 TO regress_rls_bob;
@@ -669,7 +669,7 @@ DELETE FROM t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
--
SET SESSION AUTHORIZATION regress_rls_alice;
CREATE TABLE b1 (a int, b text);
-INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
+INSERT INTO b1 (SELECT x, public.notmd5(x::text) FROM generate_series(-10,10) x);
CREATE POLICY p1 ON b1 USING (a % 2 = 0);
ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
@@ -1269,7 +1269,7 @@ DROP VIEW rls_sbv;
-- Expression structure
--
SET SESSION AUTHORIZATION regress_rls_alice;
-INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
+INSERT INTO y2 (SELECT x, public.notmd5(x::text) FROM generate_series(0,20) x);
CREATE POLICY p2 ON y2 USING (a % 3 = 0);
CREATE POLICY p3 ON y2 USING (a % 4 = 0);
@@ -1341,7 +1341,7 @@ ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
GRANT ALL ON t1 TO regress_rls_bob;
-INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
+INSERT INTO t1 (SELECT x, public.notmd5(x::text) FROM generate_series(0,20) x);
SET SESSION AUTHORIZATION regress_rls_bob;
@@ -1473,7 +1473,7 @@ ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user;
-INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
+INSERT INTO copy_t (SELECT x, public.notmd5(x::text) FROM generate_series(0,10) x);
-- Check COPY TO as Superuser/owner.
RESET SESSION AUTHORIZATION;
@@ -1513,7 +1513,7 @@ ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
GRANT ALL ON copy_rel_to TO regress_rls_bob, regress_rls_exempt_user;
-INSERT INTO copy_rel_to VALUES (1, md5('1'));
+INSERT INTO copy_rel_to VALUES (1, public.notmd5('1'));
-- Check COPY TO as Superuser/owner.
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index d0d42cd013..b27e9c683a 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -1283,25 +1283,25 @@ WITH (autovacuum_enabled = off);
INSERT INTO mcv_lists_uuid (a, b, c)
SELECT
- md5(mod(i,100)::text)::uuid,
- md5(mod(i,50)::text)::uuid,
- md5(mod(i,25)::text)::uuid
+ notmd5(mod(i,100)::text)::uuid,
+ notmd5(mod(i,50)::text)::uuid,
+ notmd5(mod(i,25)::text)::uuid
FROM generate_series(1,5000) s(i);
ANALYZE mcv_lists_uuid;
-SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''
ANDb = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''
ANDb = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''');
-SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''
ANDb = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND c = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''
ANDb = ''e7f6c011-776e-8db7-cd33-0b54174fd76f'' AND c = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''');
CREATE STATISTICS mcv_lists_uuid_stats (mcv) ON a, b, c
FROM mcv_lists_uuid;
ANALYZE mcv_lists_uuid;
-SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''
ANDb = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''
ANDb = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''');
-SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''
ANDb = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND c = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''
ANDb = ''e7f6c011-776e-8db7-cd33-0b54174fd76f'' AND c = ''e7f6c011-776e-8db7-cd33-0b54174fd76f''');
DROP TABLE mcv_lists_uuid;
@@ -1315,7 +1315,7 @@ WITH (autovacuum_enabled = off);
INSERT INTO mcv_lists_arrays (a, b, c)
SELECT
- ARRAY[md5((i/100)::text), md5((i/100-1)::text), md5((i/100+1)::text)],
+ ARRAY[notmd5((i/100)::text), notmd5((i/100-1)::text), notmd5((i/100+1)::text)],
ARRAY[(i/100-1)::numeric/1000, (i/100)::numeric/1000, (i/100+1)::numeric/1000],
ARRAY[(i/100-1), i/100, (i/100+1)]
FROM generate_series(1,5000) s(i);
@@ -1515,7 +1515,7 @@ DROP TABLE expr_stats;
-- statistics on expressions with different data types
CREATE TABLE expr_stats (a int, b name, c text);
-INSERT INTO expr_stats SELECT mod(i,10), md5(mod(i,10)::text), md5(mod(i,10)::text) FROM generate_series(1,1000) s(i);
+INSERT INTO expr_stats SELECT mod(i,10), notmd5(mod(i,10)::text), notmd5(mod(i,10)::text) FROM generate_series(1,1000)
s(i);
ANALYZE expr_stats;
SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 0 AND (b || c) <= ''z'' AND (c || b) >=
''0''');
diff --git a/src/test/regress/sql/test_setup.sql b/src/test/regress/sql/test_setup.sql
index 8439b38d21..dfb06d4b2e 100644
--- a/src/test/regress/sql/test_setup.sql
+++ b/src/test/regress/sql/test_setup.sql
@@ -284,3 +284,18 @@ create function part_hashtext_length(value text, seed int8)
create operator class part_test_text_ops for type text using hash as
operator 1 =,
function 2 part_hashtext_length(text, int8);
+
+--
+-- These functions are used in tests that used to use md5(), which we now
+-- mostly avoid so that the tests will pass in FIPS mode.
+--
+
+create function notmd5(bytea)
+ returns text
+ strict immutable parallel safe leakproof
+ begin atomic; select substr(encode(sha256($1), 'hex'), 1, 32); end;
+
+create function notmd5(text)
+ returns text
+ strict immutable parallel safe leakproof
+ begin atomic; select substr(encode(sha256($1::bytea), 'hex'), 1, 32); end;
> On 5 Mar 2023, at 00:04, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes: >> [ v2-0001-Remove-incidental-md5-function-uses-from-main-reg.patch ] > > I've gone through this and have a modest suggestion: let's invent some > wrapper functions around encode(sha256()) to reduce the cosmetic diffs > and consequent need for closer study of patch changes. In the attached > I called them "notmd5()", but I'm surely not wedded to that name. For readers without all context, wouldn't it be better to encode in the function name why we're not just calling a hash like md5? Something like fips_allowed_hash() or similar? -- Daniel Gustafsson
Daniel Gustafsson <daniel@yesql.se> writes:
>> On 5 Mar 2023, at 00:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I've gone through this and have a modest suggestion: let's invent some
>> wrapper functions around encode(sha256()) to reduce the cosmetic diffs
>> and consequent need for closer study of patch changes. In the attached
>> I called them "notmd5()", but I'm surely not wedded to that name.
> For readers without all context, wouldn't it be better to encode in the
> function name why we're not just calling a hash like md5? Something like
> fips_allowed_hash() or similar?
I'd prefer shorter than that --- all these queries are laid out on the
expectation of a very short function name. Maybe "fipshash()"?
We could make the comment introducing the function declarations more
elaborate, too.
regards, tom lane
> On 6 Mar 2023, at 15:55, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Daniel Gustafsson <daniel@yesql.se> writes: >> For readers without all context, wouldn't it be better to encode in the >> function name why we're not just calling a hash like md5? Something like >> fips_allowed_hash() or similar? > > I'd prefer shorter than that --- all these queries are laid out on the > expectation of a very short function name. Maybe "fipshash()"? > > We could make the comment introducing the function declarations more > elaborate, too. fipshash() with an explanatory comments sounds like a good idea. -- Daniel Gustafsson
On 05.03.23 00:04, Tom Lane wrote: > I've gone through this and have a modest suggestion: let's invent some > wrapper functions around encode(sha256()) to reduce the cosmetic diffs > and consequent need for closer study of patch changes. In the attached > I called them "notmd5()", but I'm surely not wedded to that name. Do you mean create this on the fly in the test suite, or make it a new built-in function?
Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> On 05.03.23 00:04, Tom Lane wrote:
>> I've gone through this and have a modest suggestion: let's invent some
>> wrapper functions around encode(sha256()) to reduce the cosmetic diffs
>> and consequent need for closer study of patch changes. In the attached
>> I called them "notmd5()", but I'm surely not wedded to that name.
> Do you mean create this on the fly in the test suite, or make it a new
> built-in function?
The former --- please read my version of the patch.
regards, tom lane
On 09.12.22 05:16, Michael Paquier wrote: > On Wed, Dec 07, 2022 at 03:14:09PM +0100, Peter Eisentraut wrote: >> Here is the next step. To contain the scope, I focused on just "make check" >> for now. This patch removes all incidental calls to md5(), replacing them >> with sha256(), so that they'd pass with or without FIPS mode. (Two tests >> would need alternative expected files: md5 and password. I have not >> included those here.) > > Yeah, fine by me to do that step-by-step. It occurred to me that it would be easier to maintain this in the long run if we could enable a "fake FIPS" mode that would have the same effect but didn't require fiddling with the OpenSSL configuration or installation. The attached patch shows how this could work. Thoughts?
Attachment
> On 8 Mar 2023, at 09:49, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote: > It occurred to me that it would be easier to maintain this in the long run if we could enable a "fake FIPS" mode that wouldhave the same effect but didn't require fiddling with the OpenSSL configuration or installation. > > The attached patch shows how this could work. Thoughts? - * Initialize a hash context. Note that this implementation is designed - * to never fail, so this always returns 0. + * Initialize a hash context. Regardless of which, we wan't this hunk since the code clearly can return -1. +#ifdef FAKE_FIPS_MODE I'm not enthusiastic about this. If we use this rather than OpenSSL with FIPS enabled we might end up missing bugs or weird behavior due to changes in OpenSSL that we didn't test. -- Daniel Gustafsson
On 08.03.23 08:40, Tom Lane wrote: > Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes: >> On 05.03.23 00:04, Tom Lane wrote: >>> I've gone through this and have a modest suggestion: let's invent some >>> wrapper functions around encode(sha256()) to reduce the cosmetic diffs >>> and consequent need for closer study of patch changes. In the attached >>> I called them "notmd5()", but I'm surely not wedded to that name. > >> Do you mean create this on the fly in the test suite, or make it a new >> built-in function? > > The former --- please read my version of the patch. Ok, that makes sense. We have some other uses of this pattern in other test suites that my initial patch didn't cover yet, for example in src/test/subscripton, but we don't have expected files there, so the argument of reducing the diffs doesn't apply.
On 06.03.23 17:06, Daniel Gustafsson wrote: > fipshash() with an explanatory comments sounds like a good idea. I think that name would be quite false advertising.
On 08.03.23 10:21, Daniel Gustafsson wrote: >> On 8 Mar 2023, at 09:49, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote: > >> It occurred to me that it would be easier to maintain this in the long run if we could enable a "fake FIPS" mode thatwould have the same effect but didn't require fiddling with the OpenSSL configuration or installation. >> >> The attached patch shows how this could work. Thoughts? > > - * Initialize a hash context. Note that this implementation is designed > - * to never fail, so this always returns 0. > + * Initialize a hash context. > Regardless of which, we wan't this hunk since the code clearly can return -1. I was a bit puzzled by these comments in that file. While the existing implementations (mostly) never fail, they are clearly not *designed* to never fail, since the parallel OpenSSL implementations can fail (which is the point of this thread). So I would remove these comments altogether, really. > +#ifdef FAKE_FIPS_MODE > I'm not enthusiastic about this. If we use this rather than OpenSSL with FIPS > enabled we might end up missing bugs or weird behavior due to changes in > OpenSSL that we didn't test. Valid point. In any case, the patch is available for ad hoc testing.
> On 8 Mar 2023, at 10:30, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote: > > On 08.03.23 10:21, Daniel Gustafsson wrote: >>> On 8 Mar 2023, at 09:49, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote: >>> It occurred to me that it would be easier to maintain this in the long run if we could enable a "fake FIPS" mode thatwould have the same effect but didn't require fiddling with the OpenSSL configuration or installation. >>> >>> The attached patch shows how this could work. Thoughts? >> - * Initialize a hash context. Note that this implementation is designed >> - * to never fail, so this always returns 0. >> + * Initialize a hash context. >> Regardless of which, we wan't this hunk since the code clearly can return -1. > > I was a bit puzzled by these comments in that file. While the existing implementations (mostly) never fail, they are clearlynot *designed* to never fail, since the parallel OpenSSL implementations can fail (which is the point of this thread). So I would remove these comments altogether, really. The comment in question was missed in 55fe26a4b58, but I agree that it's a false claim given the OpenSSL implementation so removing or at least mimicking the comments in cryptohash_openssl.c would be better. -- Daniel Gustafsson
On 08.03.23 10:37, Daniel Gustafsson wrote: > The comment in question was missed in 55fe26a4b58, but I agree that it's a > false claim given the OpenSSL implementation so removing or at least mimicking > the comments in cryptohash_openssl.c would be better. I have fixed these comments to match cryptohash_openssl.c.
On Thu, Mar 09, 2023 at 10:01:14AM +0100, Peter Eisentraut wrote: > I have fixed these comments to match cryptohash_openssl.c. Missed that, thanks for the fix. -- Michael
Attachment
On 06.03.23 17:06, Daniel Gustafsson wrote: >> On 6 Mar 2023, at 15:55, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Daniel Gustafsson <daniel@yesql.se> writes: > >>> For readers without all context, wouldn't it be better to encode in the >>> function name why we're not just calling a hash like md5? Something like >>> fips_allowed_hash() or similar? >> >> I'd prefer shorter than that --- all these queries are laid out on the >> expectation of a very short function name. Maybe "fipshash()"? >> >> We could make the comment introducing the function declarations more >> elaborate, too. > > fipshash() with an explanatory comments sounds like a good idea. committed like that (I'm going to close the CF item and revisit the other test suites for the next release.)
> On 13 Mar 2023, at 11:06, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote: > On 06.03.23 17:06, Daniel Gustafsson wrote: >> fipshash() with an explanatory comments sounds like a good idea. > > committed like that +1. Looks like there is a just a slight diff in the compression.sql test suite. -- Daniel Gustafsson
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.
Attachment
> On 5 Oct 2023, at 15:44, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:
>
> 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
overthe 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
+ERROR: crypt(3) returned NULL
Not within scope here, but I wish we had a better error message here. That's for another patch though clearly.
> 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.
I don't have a FIPS enabled build handy to test in, but reading the patch I
don't see anything that sticks out apart from very minor comments:
+my $md5_works = ($node->psql('postgres', "select md5('')") == 0);
I think this warrants an explanatory comment for readers not familiar with
FIPS, without that it may seem quite an odd test.
+), 0, 'created user with scram password');
Tiny nitpick, I think we use SCRAM when writing it in text.
> 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.
Without inventing a new structure for alternative outputs I don't see how.
--
Daniel Gustafsson
Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> Continuing this, we have fixed many issues since. Here is a patch set
> to fix all remaining issues.
On the way to testing this, I discovered that we have a usability
regression with recent OpenSSL releases. The Fedora 35 installation
I used to use for testing FIPS-mode behavior would produce errors like
select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE";
- TRUE
-------
- t
-(1 row)
-
+ERROR: could not compute MD5 hash: disabled for FIPS
In the shiny new Fedora 38 installation I just set up for the
same purpose, I'm seeing
select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE";
- TRUE
-------
- t
-(1 row)
-
+ERROR: could not compute MD5 hash: unsupported
This is less user-friendly; moreover it indicates that we're
going to get different output depending on the vintage of
OpenSSL we're testing against, which is going to be a pain for
expected-file maintenance.
I think we need to make an effort to restore the old output
if possible, although I grant that this may be mostly a whim
of OpenSSL's that we can't do much about.
The F35 installation has openssl 1.1.1q, where F38 has
openssl 3.0.9.
regards, tom lane
I found another bit of fun we'll need to deal with: on my F38 platform, pgcrypto/3des fails as attached. Some googling finds this relevant info: https://github.com/pyca/cryptography/issues/6875 That is, FIPS deprecation of 3DES is happening even as we speak. So apparently we'll have little choice but to deal with two different behaviors for that. As before, I'm not too pleased with the user-friendliness of the error: +ERROR: encrypt error: Cipher cannot be initialized That's even less useful to a user than "unsupported". FWIW, everything else seems to pass with this patchset. I ran check-world as well as the various "must run manually" test suites. regards, tom lane diff -U3 /home/tgl/pgsql/contrib/pgcrypto/expected/3des.out /home/tgl/pgsql/contrib/pgcrypto/results/3des.out --- /home/tgl/pgsql/contrib/pgcrypto/expected/3des.out 2023-10-05 15:25:46.922080156 -0400 +++ /home/tgl/pgsql/contrib/pgcrypto/results/3des.out 2023-10-05 16:29:32.416972002 -0400 @@ -5,61 +5,25 @@ SELECT encrypt('\x8000000000000000', '\x010101010101010101010101010101010101010101010101', '3des-ecb/pad:none'); - encrypt --------------------- - \x95f8a5e5dd31d900 -(1 row) - +ERROR: encrypt error: Cipher cannot be initialized select encrypt('', 'foo', '3des'); - encrypt --------------------- - \x752111e37a2d7ac3 -(1 row) - +ERROR: encrypt error: Cipher cannot be initialized -- 10 bytes key select encrypt('foo', '0123456789', '3des'); - encrypt --------------------- - \xd2fb8baa1717cb02 -(1 row) - +ERROR: encrypt error: Cipher cannot be initialized -- 22 bytes key select encrypt('foo', '0123456789012345678901', '3des'); - encrypt --------------------- - \xa44360e699269817 -(1 row) - +ERROR: encrypt error: Cipher cannot be initialized -- decrypt select encode(decrypt(encrypt('foo', '0123456', '3des'), '0123456', '3des'), 'escape'); - encode --------- - foo -(1 row) - +ERROR: encrypt error: Cipher cannot be initialized -- iv select encrypt_iv('foo', '0123456', 'abcd', '3des'); - encrypt_iv --------------------- - \x50735067b073bb93 -(1 row) - +ERROR: encrypt_iv error: Cipher cannot be initialized select encode(decrypt_iv('\x50735067b073bb93', '0123456', 'abcd', '3des'), 'escape'); - encode --------- - foo -(1 row) - +ERROR: decrypt_iv error: Cipher cannot be initialized -- long message select encrypt('Lets try a longer message.', '0123456789012345678901', '3des'); - encrypt --------------------------------------------------------------------- - \xb71e3422269d0ded19468f33d65cd663c28e0871984792a7b3ba0ddcecec8d2c -(1 row) - +ERROR: encrypt error: Cipher cannot be initialized select encode(decrypt(encrypt('Lets try a longer message.', '0123456789012345678901', '3des'), '0123456789012345678901','3des'), 'escape'); - encode ----------------------------- - Lets try a longer message. -(1 row) - +ERROR: encrypt error: Cipher cannot be initialized
On 05.10.23 22:04, Tom Lane wrote:
> On the way to testing this, I discovered that we have a usability
> regression with recent OpenSSL releases. The Fedora 35 installation
> I used to use for testing FIPS-mode behavior would produce errors like
> +ERROR: could not compute MD5 hash: disabled for FIPS
> In the shiny new Fedora 38 installation I just set up for the
> same purpose, I'm seeing
> +ERROR: could not compute MD5 hash: unsupported
This makes sense, because the older OpenSSL works basically like
if (FIPS_mode()) {
specific_error();
}
while the new one has all crypto methods in modules, and if you load the
fips module, then some crypto methods just don't exist.
On 05.10.23 22:55, Tom Lane wrote: > I found another bit of fun we'll need to deal with: on my F38 > platform, pgcrypto/3des fails as attached. Some googling finds > this relevant info: > > https://github.com/pyca/cryptography/issues/6875 > > That is, FIPS deprecation of 3DES is happening even as we speak. > So apparently we'll have little choice but to deal with two > different behaviors for that. Hmm, interesting, so maybe there should be a new openssl 3.x release at the end of the year that addresses this?
On 05.10.23 22:55, Tom Lane wrote: > I found another bit of fun we'll need to deal with: on my F38 > platform, pgcrypto/3des fails as attached. Some googling finds > this relevant info: > > https://github.com/pyca/cryptography/issues/6875 > > That is, FIPS deprecation of 3DES is happening even as we speak. > So apparently we'll have little choice but to deal with two > different behaviors for that. > > As before, I'm not too pleased with the user-friendliness > of the error: > > +ERROR: encrypt error: Cipher cannot be initialized > > That's even less useful to a user than "unsupported". > > FWIW, everything else seems to pass with this patchset. > I ran check-world as well as the various "must run manually" > test suites. I've been trying to get some VM set up with the right Red Hat environment to be able to reproduce the issues you reported. But somehow switching the OS into FIPS mode messes up the boot environment of the VM or something. So I haven't been able to make progress on this. I suggest that if there are no other concerns, we proceed with the patch set as is for now. The 3DES deprecation can be addressed by adding another expected file, which can easily be supplied by someone having this environment running. The error message difference in the older OpenSSL version would probably need a small bit of coding. But we can leave that as a separate add-on project.
Peter Eisentraut <peter@eisentraut.org> writes: > On 05.10.23 22:55, Tom Lane wrote: >> I found another bit of fun we'll need to deal with: on my F38 >> platform, pgcrypto/3des fails as attached. Some googling finds >> this relevant info: >> https://github.com/pyca/cryptography/issues/6875 >> That is, FIPS deprecation of 3DES is happening even as we speak. >> So apparently we'll have little choice but to deal with two >> different behaviors for that. > I've been trying to get some VM set up with the right Red Hat > environment to be able to reproduce the issues you reported. But > somehow switching the OS into FIPS mode messes up the boot environment > of the VM or something. So I haven't been able to make progress on this. Hm. I was just using a native install on a microSD card for my raspberry pi ... > I suggest that if there are no other concerns, we proceed with the patch > set as is for now. After thinking about it for awhile, I guess I'm okay with only bothering to provide expected-files for FIPS failures under OpenSSL 3.x (which is how your patch is set up, I believe). While there are certainly still LTS platforms with 1.x, we don't have to consider FIPS mode on them to be a supported case. I'm more concerned about the 3DES situation. Fedora might be a bit ahead of the curve here, but according to the link above, everybody is supposed to be in compliance by the end of 2023. So I'd be inclined to guess that the 3DES-is-rejected case is going to be mainstream before v17 ships. > The error message difference in the older OpenSSL version would probably > need a small bit of coding. But we can leave that as a separate add-on > project. It's the *newer* version's message that I'm unhappy about ;-). But I agree that that's not a reason to hold up applying what's here. (In reality, people running FIPS mode are probably pretty accustomed to seeing this error, so maybe it's not worth the trouble to improve it.) regards, tom lane
> On 15 Nov 2023, at 00:07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > (In reality, people running FIPS mode are probably pretty > accustomed to seeing this error, so maybe it's not worth the > trouble to improve it.) In my experience this holds a lot of truth, this is a common error pattern and while all improvements to error messages are good, it's not a reason to hold off this patch. -- Daniel Gustafsson
On 15.11.23 00:07, Tom Lane wrote: > I'm more concerned about the 3DES situation. Fedora might be a bit > ahead of the curve here, but according to the link above, everybody is > supposed to be in compliance by the end of 2023. So I'd be inclined > to guess that the 3DES-is-rejected case is going to be mainstream > before v17 ships. Right. It is curious that I have not found any activity in the OpenSSL issue trackers about this. But if you send me your results file, then I can include it in the patch as an alternative expected.
> On 15 Nov 2023, at 12:44, Peter Eisentraut <peter@eisentraut.org> wrote: > > On 15.11.23 00:07, Tom Lane wrote: >> I'm more concerned about the 3DES situation. Fedora might be a bit >> ahead of the curve here, but according to the link above, everybody is >> supposed to be in compliance by the end of 2023. So I'd be inclined >> to guess that the 3DES-is-rejected case is going to be mainstream >> before v17 ships. > > Right. It is curious that I have not found any activity in the OpenSSL issue trackers about this. But if you send meyour results file, then I can include it in the patch as an alternative expected. As NIST SP800-131A allows decryption with 3DES and DES I dont think OpenSSL will do much other than move it to the legacy module where it can be used opt-in like DES. SKIPJACK is already disallowed since before but is still tested with decryption during FIPS validation. Using an alternative resultsfile to handle platforms which explicitly removes disallowed ciphers seem like the right choice. Since the 3DES/DES deprecations aren't limited to FIPS, do we want to do anything for pgcrypto where we have DES/3DES encryption? Maybe a doc patch which mentions the deprecation with a link to the SP could be in order? -- Daniel Gustafsson
Daniel Gustafsson <daniel@yesql.se> writes:
> Since the 3DES/DES deprecations aren't limited to FIPS, do we want to do
> anything for pgcrypto where we have DES/3DES encryption? Maybe a doc patch
> which mentions the deprecation with a link to the SP could be in order?
A docs patch that marks both MD5 and 3DES as deprecated is probably
appropriate, but it seems like a matter for a separate thread and patch.
In the meantime, I've done a pass of review of Peter's v4 patches.
v4-0001 is already committed, so that's not considered here.
v4-0002: I think it is worth splitting up contrib/pgcrypto's
pgp-encrypt test, which has only one test case whose output changes,
and a bunch of others that don't. v5-0002, attached, does it
like that. It's otherwise the same as v4.
(It might be worth doing something similar for uuid_ossp's test,
but I have not bothered here. That test script is stable enough
that I'm not too worried about future maintenance.)
The attached 0003, 0004, 0005 patches are identical to Peter's.
I think that it is possibly worth modifying the password test so that
we don't fail to create the roles, so as to reduce the delta between
password.out and password_1.out (and thereby ease future maintenance
of those files). However you might disagree, so I split my proposal
out as a separate patch v5-0007-password-test-delta.patch; you can
drop that from the set if you don't like it.
v5-0006-allow-for-disabled-3DES.patch adds the necessary expected
file to make that pass on my Fedora 38 system.
With or without 0007, as you choose, I think it's committable.
regards, tom lane
diff --git a/contrib/pgcrypto/Makefile b/contrib/pgcrypto/Makefile
index 7fb59f51b7..5efa10c334 100644
--- a/contrib/pgcrypto/Makefile
+++ b/contrib/pgcrypto/Makefile
@@ -42,7 +42,7 @@ PGFILEDESC = "pgcrypto - cryptographic functions"
REGRESS = init md5 sha1 hmac-md5 hmac-sha1 blowfish rijndael \
sha2 des 3des cast5 \
crypt-des crypt-md5 crypt-blowfish crypt-xdes \
- pgp-armor pgp-decrypt pgp-encrypt $(CF_PGP_TESTS) \
+ pgp-armor pgp-decrypt pgp-encrypt pgp-encrypt-md5 $(CF_PGP_TESTS) \
pgp-pubkey-decrypt pgp-pubkey-encrypt pgp-info
EXTRA_CLEAN = gen-rtab
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-md5.out b/contrib/pgcrypto/expected/pgp-encrypt-md5.out
new file mode 100644
index 0000000000..339e12a434
--- /dev/null
+++ b/contrib/pgcrypto/expected/pgp-encrypt-md5.out
@@ -0,0 +1,11 @@
+--
+-- PGP encrypt using MD5
+--
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=md5'),
+ 'key', 'expect-s2k-digest-algo=md5');
+ pgp_sym_decrypt
+-----------------
+ Secret.
+(1 row)
+
diff --git a/contrib/pgcrypto/expected/pgp-encrypt-md5_1.out b/contrib/pgcrypto/expected/pgp-encrypt-md5_1.out
new file mode 100644
index 0000000000..612ca1d19c
--- /dev/null
+++ b/contrib/pgcrypto/expected/pgp-encrypt-md5_1.out
@@ -0,0 +1,7 @@
+--
+-- PGP encrypt using MD5
+--
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=md5'),
+ 'key', 'expect-s2k-digest-algo=md5');
+ERROR: Unsupported digest algorithm
diff --git a/contrib/pgcrypto/expected/pgp-encrypt.out b/contrib/pgcrypto/expected/pgp-encrypt.out
index 77e45abe53..50cd3f6daa 100644
--- a/contrib/pgcrypto/expected/pgp-encrypt.out
+++ b/contrib/pgcrypto/expected/pgp-encrypt.out
@@ -121,14 +121,6 @@ NOTICE: pgp_decrypt: unexpected s2k_count: expected 65000000 got 65011712
(1 row)
-- s2k digest change
-select pgp_sym_decrypt(
- pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=md5'),
- 'key', 'expect-s2k-digest-algo=md5');
- pgp_sym_decrypt
------------------
- Secret.
-(1 row)
-
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=sha1'),
'key', 'expect-s2k-digest-algo=sha1');
diff --git a/contrib/pgcrypto/meson.build b/contrib/pgcrypto/meson.build
index df7dd50dbc..4f62ea0af0 100644
--- a/contrib/pgcrypto/meson.build
+++ b/contrib/pgcrypto/meson.build
@@ -48,6 +48,7 @@ pgcrypto_regress = [
'pgp-armor',
'pgp-decrypt',
'pgp-encrypt',
+ 'pgp-encrypt-md5',
'pgp-pubkey-decrypt',
'pgp-pubkey-encrypt',
'pgp-info',
diff --git a/contrib/pgcrypto/sql/pgp-encrypt-md5.sql b/contrib/pgcrypto/sql/pgp-encrypt-md5.sql
new file mode 100644
index 0000000000..201636c820
--- /dev/null
+++ b/contrib/pgcrypto/sql/pgp-encrypt-md5.sql
@@ -0,0 +1,7 @@
+--
+-- PGP encrypt using MD5
+--
+
+select pgp_sym_decrypt(
+ pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=md5'),
+ 'key', 'expect-s2k-digest-algo=md5');
diff --git a/contrib/pgcrypto/sql/pgp-encrypt.sql b/contrib/pgcrypto/sql/pgp-encrypt.sql
index ed8b17776b..f67329c2c3 100644
--- a/contrib/pgcrypto/sql/pgp-encrypt.sql
+++ b/contrib/pgcrypto/sql/pgp-encrypt.sql
@@ -63,9 +63,6 @@ select pgp_sym_decrypt(
'key', 'expect-s2k-count=65000000');
-- s2k digest change
-select pgp_sym_decrypt(
- pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=md5'),
- 'key', 'expect-s2k-digest-algo=md5');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=sha1'),
'key', 'expect-s2k-digest-algo=sha1');
From 8feace1abca7aad6b9a9a58f464d571649e2d1e2 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@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 <peter@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>')
asrolpassword_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>')
asrolpassword_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 <peter@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
diff --git a/contrib/pgcrypto/expected/3des_1.out b/contrib/pgcrypto/expected/3des_1.out
new file mode 100644
index 0000000000..fb1d1f6f0c
--- /dev/null
+++ b/contrib/pgcrypto/expected/3des_1.out
@@ -0,0 +1,29 @@
+--
+-- 3DES cipher
+--
+-- test vector from somewhere
+SELECT encrypt('\x8000000000000000',
+ '\x010101010101010101010101010101010101010101010101',
+ '3des-ecb/pad:none');
+ERROR: encrypt error: Cipher cannot be initialized
+select encrypt('', 'foo', '3des');
+ERROR: encrypt error: Cipher cannot be initialized
+-- 10 bytes key
+select encrypt('foo', '0123456789', '3des');
+ERROR: encrypt error: Cipher cannot be initialized
+-- 22 bytes key
+select encrypt('foo', '0123456789012345678901', '3des');
+ERROR: encrypt error: Cipher cannot be initialized
+-- decrypt
+select encode(decrypt(encrypt('foo', '0123456', '3des'), '0123456', '3des'), 'escape');
+ERROR: encrypt error: Cipher cannot be initialized
+-- iv
+select encrypt_iv('foo', '0123456', 'abcd', '3des');
+ERROR: encrypt_iv error: Cipher cannot be initialized
+select encode(decrypt_iv('\x50735067b073bb93', '0123456', 'abcd', '3des'), 'escape');
+ERROR: decrypt_iv error: Cipher cannot be initialized
+-- long message
+select encrypt('Lets try a longer message.', '0123456789012345678901', '3des');
+ERROR: encrypt error: Cipher cannot be initialized
+select encode(decrypt(encrypt('Lets try a longer message.', '0123456789012345678901', '3des'),
'0123456789012345678901','3des'), 'escape');
+ERROR: encrypt error: Cipher cannot be initialized
diff --git a/src/test/regress/expected/password.out b/src/test/regress/expected/password.out
index 8475231735..924d6e001d 100644
--- a/src/test/regress/expected/password.out
+++ b/src/test/regress/expected/password.out
@@ -12,8 +12,10 @@ 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';
-CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2';
+CREATE ROLE regress_passwd1;
+ALTER ROLE regress_passwd1 PASSWORD 'role_pwd1';
+CREATE ROLE regress_passwd2;
+ALTER ROLE regress_passwd2 PASSWORD 'role_pwd2';
SET password_encryption = 'scram-sha-256';
CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3';
CREATE ROLE regress_passwd4 PASSWORD NULL;
diff --git a/src/test/regress/expected/password_1.out b/src/test/regress/expected/password_1.out
index 3bb411949e..9d2cc94f37 100644
--- a/src/test/regress/expected/password_1.out
+++ b/src/test/regress/expected/password_1.out
@@ -12,9 +12,11 @@ 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';
+CREATE ROLE regress_passwd1;
+ALTER ROLE regress_passwd1 PASSWORD 'role_pwd1';
ERROR: password encryption failed: unsupported
-CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2';
+CREATE ROLE regress_passwd2;
+ALTER ROLE regress_passwd2 PASSWORD 'role_pwd2';
ERROR: password encryption failed: unsupported
SET password_encryption = 'scram-sha-256';
CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3';
@@ -32,33 +34,33 @@ SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+
ORDER BY rolname, rolpassword;
rolname | rolpassword_masked
-----------------+---------------------------------------------------
+ regress_passwd1 |
+ regress_passwd2 |
regress_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
regress_passwd4 |
-(2 rows)
+(4 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)
+ rolname | rolpassword
+---------------------+-------------
+ regress_passwd2_new |
+(1 row)
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
+ERROR: password encryption failed: unsupported
-- 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
@@ -83,6 +85,8 @@ SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+
ORDER BY rolname, rolpassword;
rolname | rolpassword_masked
-----------------+---------------------------------------------------
+ regress_passwd1 | md5cd3578025fe2c3d7ed1b9a9b26238b70
+ regress_passwd2 |
regress_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
regress_passwd4 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
regress_passwd5 | md5e73a4b11df52a6068f8b39f90be36023
@@ -90,7 +94,7 @@ SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+
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)
+(9 rows)
-- An empty password is not allowed, in any form
CREATE ROLE regress_passwd_empty PASSWORD '';
@@ -125,9 +129,7 @@ SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassw
(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;
diff --git a/src/test/regress/sql/password.sql b/src/test/regress/sql/password.sql
index 53e86b0b6c..bb82aa4aa2 100644
--- a/src/test/regress/sql/password.sql
+++ b/src/test/regress/sql/password.sql
@@ -10,8 +10,10 @@ SET password_encryption = 'scram-sha-256'; -- ok
-- consistency of password entries
SET password_encryption = 'md5';
-CREATE ROLE regress_passwd1 PASSWORD 'role_pwd1';
-CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2';
+CREATE ROLE regress_passwd1;
+ALTER ROLE regress_passwd1 PASSWORD 'role_pwd1';
+CREATE ROLE regress_passwd2;
+ALTER ROLE regress_passwd2 PASSWORD 'role_pwd2';
SET password_encryption = 'scram-sha-256';
CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3';
CREATE ROLE regress_passwd4 PASSWORD NULL;
On 15.11.23 21:29, Tom Lane wrote: > Daniel Gustafsson <daniel@yesql.se> writes: >> Since the 3DES/DES deprecations aren't limited to FIPS, do we want to do >> anything for pgcrypto where we have DES/3DES encryption? Maybe a doc patch >> which mentions the deprecation with a link to the SP could be in order? > > A docs patch that marks both MD5 and 3DES as deprecated is probably > appropriate, but it seems like a matter for a separate thread and patch. > > In the meantime, I've done a pass of review of Peter's v4 patches. > v4-0001 is already committed, so that's not considered here. > > v4-0002: I think it is worth splitting up contrib/pgcrypto's > pgp-encrypt test, which has only one test case whose output changes, > and a bunch of others that don't. v5-0002, attached, does it > like that. It's otherwise the same as v4. > > (It might be worth doing something similar for uuid_ossp's test, > but I have not bothered here. That test script is stable enough > that I'm not too worried about future maintenance.) > > The attached 0003, 0004, 0005 patches are identical to Peter's. > I think that it is possibly worth modifying the password test so that > we don't fail to create the roles, so as to reduce the delta between > password.out and password_1.out (and thereby ease future maintenance > of those files). However you might disagree, so I split my proposal > out as a separate patch v5-0007-password-test-delta.patch; you can > drop that from the set if you don't like it. > > v5-0006-allow-for-disabled-3DES.patch adds the necessary expected > file to make that pass on my Fedora 38 system. > > With or without 0007, as you choose, I think it's committable. All done, thanks.
On Sat, Nov 18, 2023 at 7:46 AM Peter Eisentraut <peter@eisentraut.org> wrote: > All done, thanks. Probably not this thread's fault, but following the breadcrumbs to the last thread to touch the relevant test lines in authentication/001_password, is it expected that we have these warnings? psql:<stdin>:1: WARNING: roles created by regression test cases should have names starting with "regress_"
Thomas Munro <thomas.munro@gmail.com> writes:
> Probably not this thread's fault, but following the breadcrumbs to the
> last thread to touch the relevant test lines in
> authentication/001_password, is it expected that we have these
> warnings?
> psql:<stdin>:1: WARNING: roles created by regression test cases
> should have names starting with "regress_"
I think the policy is that we enforce that for cases reachable
via "make installcheck" (to avoid possibly clobbering global
objects in a live installation), but not for cases only reachable
via "make check", such as TAP tests. So I'm not that concerned
about this, although if someone is feeling anal enough to rename
the test role I won't stand in the way.
regards, tom lane
On Fri, Apr 19, 2024 at 4:00 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Thomas Munro <thomas.munro@gmail.com> writes: > > Probably not this thread's fault, but following the breadcrumbs to the > > last thread to touch the relevant test lines in > > authentication/001_password, is it expected that we have these > > warnings? > > > psql:<stdin>:1: WARNING: roles created by regression test cases > > should have names starting with "regress_" > > I think the policy is that we enforce that for cases reachable > via "make installcheck" (to avoid possibly clobbering global > objects in a live installation), but not for cases only reachable > via "make check", such as TAP tests. So I'm not that concerned > about this, although if someone is feeling anal enough to rename > the test role I won't stand in the way. Got it, thanks. Not me, just asking.