Skip to content

Commit 657f5f2

Browse files
peterepetere
committed
Remove incidental md5() function uses from several tests
This removes md5() function calls from these test suites: - bloom - test_decoding - isolation - recovery - subscription This covers all remaining test suites where md5() calls were just used to generate some random data and can be replaced by appropriately adapted sha256() calls. This will eventually allow these tests to pass in OpenSSL FIPS mode (which does not allow MD5 use). See also 208bf36. Unlike for the main regression tests, I didn't write a fipshash() wrapper here, because that would have been too repetitive and wouldn't really save much here. In some cases it was easier to remove one layer of indirection by changing column types from text to bytea. Reviewed-by: Daniel Gustafsson <daniel@yesql.se> Discussion: https://www.postgresql.org/message-id/flat/f9b480b5-e473-d2d1-223a-4b9db30a229a@eisentraut.org
1 parent 625d5b3 commit 657f5f2

17 files changed

+111
-111
lines changed

‎contrib/bloom/expected/bloom.out

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@ CREATE TABLE tst (
33
i int4,
44
t text
55
);
6-
INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
6+
INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
77
CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (col1 = 3);
88
ALTER INDEX bloomidx SET (length=80);
99
SET enable_seqscan=on;
@@ -18,13 +18,13 @@ SELECT count(*) FROM tst WHERE i = 7;
1818
SELECT count(*) FROM tst WHERE t = '5';
1919
count
2020
-------
21-
112
21+
126
2222
(1 row)
2323

2424
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
2525
count
2626
-------
27-
13
27+
14
2828
(1 row)
2929

3030
SET enable_seqscan=off;
@@ -69,17 +69,17 @@ SELECT count(*) FROM tst WHERE i = 7;
6969
SELECT count(*) FROM tst WHERE t = '5';
7070
count
7171
-------
72-
112
72+
126
7373
(1 row)
7474

7575
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
7676
count
7777
-------
78-
13
78+
14
7979
(1 row)
8080

8181
DELETE FROM tst;
82-
INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
82+
INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
8383
VACUUM ANALYZE tst;
8484
SELECT count(*) FROM tst WHERE i = 7;
8585
count
@@ -90,18 +90,18 @@ SELECT count(*) FROM tst WHERE i = 7;
9090
SELECT count(*) FROM tst WHERE t = '5';
9191
count
9292
-------
93-
112
93+
126
9494
(1 row)
9595

9696
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
9797
count
9898
-------
99-
13
99+
14
100100
(1 row)
101101

102102
DELETE FROM tst WHERE i > 1 OR t = '5';
103103
VACUUM tst;
104-
INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
104+
INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
105105
SELECT count(*) FROM tst WHERE i = 7;
106106
count
107107
-------
@@ -111,13 +111,13 @@ SELECT count(*) FROM tst WHERE i = 7;
111111
SELECT count(*) FROM tst WHERE t = '5';
112112
count
113113
-------
114-
112
114+
126
115115
(1 row)
116116

117117
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
118118
count
119119
-------
120-
13
120+
14
121121
(1 row)
122122

123123
VACUUM FULL tst;
@@ -130,21 +130,21 @@ SELECT count(*) FROM tst WHERE i = 7;
130130
SELECT count(*) FROM tst WHERE t = '5';
131131
count
132132
-------
133-
112
133+
126
134134
(1 row)
135135

136136
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
137137
count
138138
-------
139-
13
139+
14
140140
(1 row)
141141

142142
-- Try an unlogged table too
143143
CREATE UNLOGGED TABLE tstu (
144144
i int4,
145145
t text
146146
);
147-
INSERT INTO tstu SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
147+
INSERT INTO tstu SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
148148
CREATE INDEX bloomidxu ON tstu USING bloom (i, t) WITH (col2 = 4);
149149
SET enable_seqscan=off;
150150
SET enable_bitmapscan=on;
@@ -188,13 +188,13 @@ SELECT count(*) FROM tstu WHERE i = 7;
188188
SELECT count(*) FROM tstu WHERE t = '5';
189189
count
190190
-------
191-
112
191+
126
192192
(1 row)
193193

194194
SELECT count(*) FROM tstu WHERE i = 7 AND t = '5';
195195
count
196196
-------
197-
13
197+
14
198198
(1 row)
199199

200200
RESET enable_seqscan;

‎contrib/bloom/sql/bloom.sql

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@ CREATE TABLE tst (
55
t text
66
);
77

8-
INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
8+
INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
99
CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (col1 = 3);
1010
ALTER INDEX bloomidx SET (length=80);
1111

@@ -30,7 +30,7 @@ SELECT count(*) FROM tst WHERE t = '5';
3030
SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
3131

3232
DELETE FROM tst;
33-
INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
33+
INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
3434
VACUUM ANALYZE tst;
3535

3636
SELECT count(*) FROM tst WHERE i = 7;
@@ -39,7 +39,7 @@ SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
3939

4040
DELETE FROM tst WHERE i > 1 OR t = '5';
4141
VACUUM tst;
42-
INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
42+
INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
4343

4444
SELECT count(*) FROM tst WHERE i = 7;
4545
SELECT count(*) FROM tst WHERE t = '5';
@@ -58,7 +58,7 @@ CREATE UNLOGGED TABLE tstu (
5858
t text
5959
);
6060

61-
INSERT INTO tstu SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
61+
INSERT INTO tstu SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,2000) i;
6262
CREATE INDEX bloomidxu ON tstu USING bloom (i, t) WITH (col2 = 4);
6363

6464
SET enable_seqscan=off;

‎contrib/bloom/t/001_wal.pl

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ sub test_index_replay
5959
$node_primary->safe_psql("postgres", "CREATE EXTENSION bloom;");
6060
$node_primary->safe_psql("postgres", "CREATE TABLE tst (i int4, t text);");
6161
$node_primary->safe_psql("postgres",
62-
"INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,100000) i;"
62+
"INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series(1,10000) i;"
6363
);
6464
$node_primary->safe_psql("postgres",
6565
"CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (col1 = 3);");
@@ -76,7 +76,7 @@ sub test_index_replay
7676
test_index_replay("vacuum $i");
7777
my ($start, $end) = (100001 + ($i - 1) * 10000, 100000 + $i * 10000);
7878
$node_primary->safe_psql("postgres",
79-
"INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series($start,$end) i;"
79+
"INSERT INTO tst SELECT i%10, substr(encode(sha256(i::text::bytea), 'hex'), 1, 1) FROM generate_series($start,$end) i;"
8080
);
8181
test_index_replay("insert $i");
8282
}

‎contrib/test_decoding/expected/concurrent_stream.out

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2,11 +2,11 @@ Parsed test spec with 3 sessions
22

33
starting permutation: s0_begin s0_ddl s1_ddl s1_begin s1_toast_insert s2_ddl s1_commit s1_get_stream_changes
44
step s0_begin: BEGIN;
5-
step s0_ddl: CREATE TABLE stream_test1(data text);
6-
step s1_ddl: CREATE TABLE stream_test(data text);
5+
step s0_ddl: CREATE TABLE stream_test1(data bytea);
6+
step s1_ddl: CREATE TABLE stream_test(data bytea);
77
step s1_begin: BEGIN;
88
step s1_toast_insert: INSERT INTO stream_test SELECT large_val();
9-
step s2_ddl: CREATE TABLE stream_test2(data text);
9+
step s2_ddl: CREATE TABLE stream_test2(data bytea);
1010
step s1_commit: COMMIT;
1111
step s1_get_stream_changes: SELECT data FROM pg_logical_slot_get_changes('isolation_slot', NULL,NULL, 'include-xids', '0', 'skip-empty-xacts', '1', 'stream-changes', '1');
1212
data

‎contrib/test_decoding/specs/concurrent_stream.spec

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@ setup
88

99
-- consume DDL
1010
SELECT data FROM pg_logical_slot_get_changes('isolation_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
11-
CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS 'select array_agg(md5(g::text))::text from generate_series(1, 80000) g';
11+
CREATE OR REPLACE FUNCTION large_val() RETURNS bytea LANGUAGE SQL AS $$ select string_agg(sha256(g::text::bytea), '') from generate_series(1, 83000) g $$;
1212
}
1313

1414
teardown
@@ -21,11 +21,11 @@ teardown
2121
session "s0"
2222
setup { SET synchronous_commit=on; }
2323
step "s0_begin" { BEGIN; }
24-
step "s0_ddl" {CREATE TABLE stream_test1(data text);}
24+
step "s0_ddl" {CREATE TABLE stream_test1(data bytea);}
2525

2626
session "s2"
2727
setup { SET synchronous_commit=on; }
28-
step "s2_ddl" {CREATE TABLE stream_test2(data text);}
28+
step "s2_ddl" {CREATE TABLE stream_test2(data bytea);}
2929

3030
# The transaction commit for s1_ddl will add the INTERNAL_SNAPSHOT change to
3131
# the currently running s0_ddl and we want to test that s0_ddl should not get
@@ -34,7 +34,7 @@ step "s2_ddl" {CREATE TABLE stream_test2(data text);}
3434
# what gets streamed.
3535
session "s1"
3636
setup { SET synchronous_commit=on; }
37-
step "s1_ddl" { CREATE TABLE stream_test(data text); }
37+
step "s1_ddl" { CREATE TABLE stream_test(data bytea); }
3838
step "s1_begin" { BEGIN; }
3939
step "s1_toast_insert" {INSERT INTO stream_test SELECT large_val();}
4040
step "s1_commit" { COMMIT; }

‎src/test/isolation/specs/insert-conflict-specconflict.spec

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -31,7 +31,7 @@ setup
3131
RETURN $1;
3232
END;$$;
3333

34-
CREATE OR REPLACE FUNCTION ctoast_large_val() RETURNS TEXT LANGUAGE SQL AS 'select array_agg(md5(g::text))::text from generate_series(1, 256) g';
34+
CREATE OR REPLACE FUNCTION ctoast_large_val() RETURNS text LANGUAGE SQL AS $$ select string_agg(encode(sha256(g::text::bytea),'hex'), '')::text from generate_series(1, 133) g $$;
3535

3636
CREATE TABLE upserttest(key text, data text);
3737

‎src/test/recovery/t/015_promotion_pages.pl

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -54,9 +54,9 @@
5454

5555
# Now just use a dummy table and run some operations to move minRecoveryPoint
5656
# beyond the previous vacuum.
57-
$alpha->safe_psql('postgres', 'create table test2 (a int, b text)');
57+
$alpha->safe_psql('postgres', 'create table test2 (a int, b bytea)');
5858
$alpha->safe_psql('postgres',
59-
'insert into test2 select generate_series(1,10000), md5(random()::text)');
59+
q{insert into test2 select generate_series(1,10000), sha256(random()::text::bytea)});
6060
$alpha->safe_psql('postgres', 'truncate test2');
6161

6262
# Wait again for all records to be replayed.

‎src/test/recovery/t/026_overwrite_contrecord.pl

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -39,7 +39,7 @@
3939
BEGIN
4040
LOOP
4141
INSERT into filler
42-
select g, repeat(md5(g::text), (random() * 60 + 1)::int)
42+
select g, repeat(encode(sha256(g::text::bytea), 'hex'), (random() * 15 + 1)::int)
4343
from generate_series(1, 10) g;
4444
4545
remain := wal_segsize - (pg_current_wal_insert_lsn() - '0/0') % wal_segsize;

‎src/test/subscription/t/008_diff_schema.pl

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,7 @@
4848

4949
# Update the rows on the publisher and check the additional columns on
5050
# subscriber didn't change
51-
$node_publisher->safe_psql('postgres', "UPDATE test_tab SET b = md5(b)");
51+
$node_publisher->safe_psql('postgres', "UPDATE test_tab SET b = encode(sha256(b::bytea), 'hex')");
5252

5353
$node_publisher->wait_for_catchup('tap_sub');
5454

@@ -65,7 +65,7 @@
6565
"UPDATE test_tab SET c = 'epoch'::timestamptz + 987654321 * interval '1s'"
6666
);
6767
$node_publisher->safe_psql('postgres',
68-
"UPDATE test_tab SET b = md5(a::text)");
68+
"UPDATE test_tab SET b = encode(sha256(a::text::bytea), 'hex')");
6969

7070
$node_publisher->wait_for_catchup('tap_sub');
7171

‎src/test/subscription/t/015_stream.pl

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -38,15 +38,15 @@ sub test_streaming
3838
$h->query_safe(
3939
q{
4040
BEGIN;
41-
INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(3, 5000) s(i);
42-
UPDATE test_tab SET b = md5(b) WHERE mod(a,2) = 0;
41+
INSERT INTO test_tab SELECT i, sha256(i::text::bytea) FROM generate_series(3, 5000) s(i);
42+
UPDATE test_tab SET b = sha256(b) WHERE mod(a,2) = 0;
4343
DELETE FROM test_tab WHERE mod(a,3) = 0;
4444
});
4545

4646
$node_publisher->safe_psql(
4747
'postgres', q{
4848
BEGIN;
49-
INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(5001, 9999) s(i);
49+
INSERT INTO test_tab SELECT i, sha256(i::text::bytea) FROM generate_series(5001, 9999) s(i);
5050
DELETE FROM test_tab WHERE a > 5000;
5151
COMMIT;
5252
});
@@ -76,8 +76,8 @@ sub test_streaming
7676
$node_publisher->safe_psql(
7777
'postgres', q{
7878
BEGIN;
79-
INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(5001, 10000) s(i);
80-
UPDATE test_tab SET b = md5(b) WHERE mod(a,2) = 0;
79+
INSERT INTO test_tab SELECT i, sha256(i::text::bytea) FROM generate_series(5001, 10000) s(i);
80+
UPDATE test_tab SET b = sha256(b) WHERE mod(a,2) = 0;
8181
DELETE FROM test_tab WHERE mod(a,3) = 0;
8282
COMMIT;
8383
});
@@ -104,7 +104,7 @@ sub test_streaming
104104
$offset = -s $node_subscriber->logfile;
105105

106106
$node_publisher->safe_psql('postgres',
107-
"UPDATE test_tab SET b = md5(a::text)");
107+
"UPDATE test_tab SET b = sha256(a::text::bytea)");
108108

109109
$node_publisher->wait_for_catchup($appname);
110110

@@ -136,15 +136,15 @@ sub test_streaming
136136

137137
# Create some preexisting content on publisher
138138
$node_publisher->safe_psql('postgres',
139-
"CREATE TABLE test_tab (a int primary key, b varchar)");
139+
"CREATE TABLE test_tab (a int primary key, b bytea)");
140140
$node_publisher->safe_psql('postgres',
141141
"INSERT INTO test_tab VALUES (1, 'foo'), (2, 'bar')");
142142

143143
$node_publisher->safe_psql('postgres', "CREATE TABLE test_tab_2 (a int)");
144144

145145
# Setup structure on subscriber
146146
$node_subscriber->safe_psql('postgres',
147-
"CREATE TABLE test_tab (a int primary key, b text, c timestamptz DEFAULT now(), d bigint DEFAULT 999)"
147+
"CREATE TABLE test_tab (a int primary key, b bytea, c timestamptz DEFAULT now(), d bigint DEFAULT 999)"
148148
);
149149

150150
$node_subscriber->safe_psql('postgres', "CREATE TABLE test_tab_2 (a int)");

‎src/test/subscription/t/016_stream_subxact.pl

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -36,24 +36,24 @@ sub test_streaming
3636
$node_publisher->safe_psql(
3737
'postgres', q{
3838
BEGIN;
39-
INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(3, 5) s(i);
40-
UPDATE test_tab SET b = md5(b) WHERE mod(a,2) = 0;
39+
INSERT INTO test_tab SELECT i, sha256(i::text::bytea) FROM generate_series(3, 5) s(i);
40+
UPDATE test_tab SET b = sha256(b) WHERE mod(a,2) = 0;
4141
DELETE FROM test_tab WHERE mod(a,3) = 0;
4242
SAVEPOINT s1;
43-
INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(6, 8) s(i);
44-
UPDATE test_tab SET b = md5(b) WHERE mod(a,2) = 0;
43+
INSERT INTO test_tab SELECT i, sha256(i::text::bytea) FROM generate_series(6, 8) s(i);
44+
UPDATE test_tab SET b = sha256(b) WHERE mod(a,2) = 0;
4545
DELETE FROM test_tab WHERE mod(a,3) = 0;
4646
SAVEPOINT s2;
47-
INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(9, 11) s(i);
48-
UPDATE test_tab SET b = md5(b) WHERE mod(a,2) = 0;
47+
INSERT INTO test_tab SELECT i, sha256(i::text::bytea) FROM generate_series(9, 11) s(i);
48+
UPDATE test_tab SET b = sha256(b) WHERE mod(a,2) = 0;
4949
DELETE FROM test_tab WHERE mod(a,3) = 0;
5050
SAVEPOINT s3;
51-
INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(12, 14) s(i);
52-
UPDATE test_tab SET b = md5(b) WHERE mod(a,2) = 0;
51+
INSERT INTO test_tab SELECT i, sha256(i::text::bytea) FROM generate_series(12, 14) s(i);
52+
UPDATE test_tab SET b = sha256(b) WHERE mod(a,2) = 0;
5353
DELETE FROM test_tab WHERE mod(a,3) = 0;
5454
SAVEPOINT s4;
55-
INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(15, 17) s(i);
56-
UPDATE test_tab SET b = md5(b) WHERE mod(a,2) = 0;
55+
INSERT INTO test_tab SELECT i, sha256(i::text::bytea) FROM generate_series(15, 17) s(i);
56+
UPDATE test_tab SET b = sha256(b) WHERE mod(a,2) = 0;
5757
DELETE FROM test_tab WHERE mod(a,3) = 0;
5858
COMMIT;
5959
});
@@ -89,13 +89,13 @@ sub test_streaming
8989

9090
# Create some preexisting content on publisher
9191
$node_publisher->safe_psql('postgres',
92-
"CREATE TABLE test_tab (a int primary key, b varchar)");
92+
"CREATE TABLE test_tab (a int primary key, b bytea)");
9393
$node_publisher->safe_psql('postgres',
9494
"INSERT INTO test_tab VALUES (1, 'foo'), (2, 'bar')");
9595

9696
# Setup structure on subscriber
9797
$node_subscriber->safe_psql('postgres',
98-
"CREATE TABLE test_tab (a int primary key, b text, c timestamptz DEFAULT now(), d bigint DEFAULT 999)"
98+
"CREATE TABLE test_tab (a int primary key, b bytea, c timestamptz DEFAULT now(), d bigint DEFAULT 999)"
9999
);
100100

101101
# Setup logical replication

0 commit comments

Comments
 (0)