1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
|
# Copyright (c) 2021-2023, PostgreSQL Global Development Group
# Test that logical replication respects permissions
use strict;
use warnings;
use PostgreSQL::Test::Cluster;
use Test::More;
my ($node_publisher, $node_subscriber, $publisher_connstr, $result, $offset);
$offset = 0;
sub publish_insert
{
my ($tbl, $new_i) = @_;
$node_publisher->safe_psql(
'postgres', qq(
SET SESSION AUTHORIZATION regress_alice;
INSERT INTO $tbl (i) VALUES ($new_i);
));
}
sub publish_update
{
my ($tbl, $old_i, $new_i) = @_;
$node_publisher->safe_psql(
'postgres', qq(
SET SESSION AUTHORIZATION regress_alice;
UPDATE $tbl SET i = $new_i WHERE i = $old_i;
));
}
sub publish_delete
{
my ($tbl, $old_i) = @_;
$node_publisher->safe_psql(
'postgres', qq(
SET SESSION AUTHORIZATION regress_alice;
DELETE FROM $tbl WHERE i = $old_i;
));
}
sub expect_replication
{
my ($tbl, $cnt, $min, $max, $testname) = @_;
$node_publisher->wait_for_catchup('admin_sub');
$result = $node_subscriber->safe_psql(
'postgres', qq(
SELECT COUNT(i), MIN(i), MAX(i) FROM $tbl));
is($result, "$cnt|$min|$max", $testname);
}
sub expect_failure
{
my ($tbl, $cnt, $min, $max, $re, $testname) = @_;
$offset = $node_subscriber->wait_for_log($re, $offset);
$result = $node_subscriber->safe_psql(
'postgres', qq(
SELECT COUNT(i), MIN(i), MAX(i) FROM $tbl));
is($result, "$cnt|$min|$max", $testname);
}
sub revoke_superuser
{
my ($role) = @_;
$node_subscriber->safe_psql(
'postgres', qq(
ALTER ROLE $role NOSUPERUSER));
}
# Create publisher and subscriber nodes with schemas owned and published by
# "regress_alice" but subscribed and replicated by different role
# "regress_admin" and "regress_admin2". For partitioned tables, layout the
# partitions differently on the publisher than on the subscriber.
#
$node_publisher = PostgreSQL::Test::Cluster->new('publisher');
$node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
$node_publisher->init(allows_streaming => 'logical');
$node_subscriber->init;
$node_publisher->start;
$node_subscriber->start;
$publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
for my $node ($node_publisher, $node_subscriber)
{
$node->safe_psql(
'postgres', qq(
CREATE ROLE regress_admin SUPERUSER LOGIN;
CREATE ROLE regress_admin2 SUPERUSER LOGIN;
CREATE ROLE regress_alice NOSUPERUSER LOGIN;
GRANT CREATE ON DATABASE postgres TO regress_alice;
SET SESSION AUTHORIZATION regress_alice;
CREATE SCHEMA alice;
GRANT USAGE ON SCHEMA alice TO regress_admin;
CREATE TABLE alice.unpartitioned (i INTEGER);
ALTER TABLE alice.unpartitioned REPLICA IDENTITY FULL;
GRANT SELECT ON TABLE alice.unpartitioned TO regress_admin;
));
}
$node_publisher->safe_psql(
'postgres', qq(
SET SESSION AUTHORIZATION regress_alice;
CREATE PUBLICATION alice FOR TABLE alice.unpartitioned
WITH (publish_via_partition_root = true);
));
$node_subscriber->safe_psql(
'postgres', qq(
SET SESSION AUTHORIZATION regress_admin;
CREATE SUBSCRIPTION admin_sub CONNECTION '$publisher_connstr' PUBLICATION alice
WITH (run_as_owner = true, password_required = false);
));
# Wait for initial sync to finish
$node_subscriber->wait_for_subscription_sync($node_publisher, 'admin_sub');
# Verify that "regress_admin" can replicate into the tables
publish_insert("alice.unpartitioned", 1);
publish_insert("alice.unpartitioned", 3);
publish_insert("alice.unpartitioned", 5);
publish_update("alice.unpartitioned", 1 => 7);
publish_delete("alice.unpartitioned", 3);
expect_replication("alice.unpartitioned", 2, 5, 7, "superuser can replicate");
# Revoke superuser privilege for "regress_admin", and verify that we now
# fail to replicate an insert.
revoke_superuser("regress_admin");
publish_insert("alice.unpartitioned", 9);
expect_failure(
"alice.unpartitioned", 2, 5, 7,
qr/ERROR: ( [A-Z0-9]+:)? permission denied for table unpartitioned/msi,
"with no privileges cannot replicate");
# Now grant DML privileges and verify that we can replicate an INSERT.
$node_subscriber->safe_psql(
'postgres', qq(
ALTER ROLE regress_admin NOSUPERUSER;
SET SESSION AUTHORIZATION regress_alice;
GRANT INSERT,UPDATE,DELETE ON alice.unpartitioned TO regress_admin;
REVOKE SELECT ON alice.unpartitioned FROM regress_admin;
));
expect_replication("alice.unpartitioned", 3, 5, 9,
"with INSERT privilege can replicate INSERT");
# We can't yet replicate an UPDATE because we don't have SELECT.
publish_update("alice.unpartitioned", 5 => 11);
publish_delete("alice.unpartitioned", 9);
expect_failure(
"alice.unpartitioned",
3,
5,
9,
qr/ERROR: ( [A-Z0-9]+:)? permission denied for table unpartitioned/msi,
"without SELECT privilege cannot replicate UPDATE or DELETE");
# After granting SELECT, replication resumes.
$node_subscriber->safe_psql(
'postgres', qq(
SET SESSION AUTHORIZATION regress_alice;
GRANT SELECT ON alice.unpartitioned TO regress_admin;
));
expect_replication("alice.unpartitioned", 2, 7, 11,
"with all privileges can replicate");
# Remove all privileges again. Instead, give the ability to SET ROLE to
# regress_alice.
$node_subscriber->safe_psql(
'postgres', qq(
SET SESSION AUTHORIZATION regress_alice;
REVOKE ALL PRIVILEGES ON alice.unpartitioned FROM regress_admin;
RESET SESSION AUTHORIZATION;
GRANT regress_alice TO regress_admin WITH INHERIT FALSE, SET TRUE;
));
# Because replication is running as the subscription owner in this test,
# the above grant doesn't help: it gives the ability to SET ROLE, but not
# privileges on the table.
publish_insert("alice.unpartitioned", 13);
expect_failure(
"alice.unpartitioned",
2,
7,
11,
qr/ERROR: ( [A-Z0-9]+:)? permission denied for table unpartitioned/msi,
"with SET ROLE but not INHERIT cannot replicate");
# Now remove SET ROLE and add INHERIT and check that things start working.
$node_subscriber->safe_psql(
'postgres', qq(
GRANT regress_alice TO regress_admin WITH INHERIT TRUE, SET FALSE;
));
expect_replication("alice.unpartitioned", 3, 7, 13,
"with INHERIT but not SET ROLE can replicate");
# Remove the subscrition and truncate the table for the initial data sync
# tests.
$node_subscriber->safe_psql(
'postgres', qq(
DROP SUBSCRIPTION admin_sub;
TRUNCATE alice.unpartitioned;
));
# Create a new subscription "admin_sub" owned by regress_admin2. It's
# disabled so that we revoke superuser privilege after creation.
$node_subscriber->safe_psql(
'postgres', qq(
SET SESSION AUTHORIZATION regress_admin2;
CREATE SUBSCRIPTION admin_sub CONNECTION '$publisher_connstr' PUBLICATION alice
WITH (run_as_owner = false, password_required = false, copy_data = true, enabled = false);
));
# Revoke superuser privilege for "regress_admin2", and give it the
# ability to SET ROLE. Then enable the subscription "admin_sub".
revoke_superuser("regress_admin2");
$node_subscriber->safe_psql(
'postgres', qq(
GRANT regress_alice TO regress_admin2 WITH INHERIT FALSE, SET TRUE;
ALTER SUBSCRIPTION admin_sub ENABLE;
));
# Because the initial data sync is working as the table owner, all
# data should be copied.
$node_subscriber->wait_for_subscription_sync($node_publisher, 'admin_sub');
expect_replication("alice.unpartitioned", 3, 7, 13,
"table owner can do the initial data copy");
done_testing();
|
