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
|
-- predictability
SET synchronous_commit = on;
-- setup
CREATE ROLE regress_lr_normal;
CREATE ROLE regress_lr_superuser SUPERUSER;
CREATE ROLE regress_lr_replication REPLICATION;
CREATE TABLE lr_test(data text);
-- superuser can control replication
SET ROLE regress_lr_superuser;
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
?column?
----------
init
(1 row)
INSERT INTO lr_test VALUES('lr_superuser_init');
SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
data
--------------------------------------------------------------
BEGIN
table public.lr_test: INSERT: data[text]:'lr_superuser_init'
COMMIT
(3 rows)
SELECT pg_drop_replication_slot('regression_slot');
pg_drop_replication_slot
--------------------------
(1 row)
RESET ROLE;
-- replication user can control replication
SET ROLE regress_lr_replication;
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
?column?
----------
init
(1 row)
INSERT INTO lr_test VALUES('lr_superuser_init');
ERROR: permission denied for table lr_test
SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
data
------
(0 rows)
SELECT pg_drop_replication_slot('regression_slot');
pg_drop_replication_slot
--------------------------
(1 row)
RESET ROLE;
-- plain user *can't* can control replication
SET ROLE regress_lr_normal;
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
ERROR: must be superuser or replication role to use replication slots
INSERT INTO lr_test VALUES('lr_superuser_init');
ERROR: permission denied for table lr_test
SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
ERROR: must be superuser or replication role to use replication slots
SELECT pg_drop_replication_slot('regression_slot');
ERROR: must be superuser or replication role to use replication slots
RESET ROLE;
-- replication users can drop superuser created slots
SET ROLE regress_lr_superuser;
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
?column?
----------
init
(1 row)
RESET ROLE;
SET ROLE regress_lr_replication;
SELECT pg_drop_replication_slot('regression_slot');
pg_drop_replication_slot
--------------------------
(1 row)
RESET ROLE;
-- normal users can't drop existing slots
SET ROLE regress_lr_superuser;
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
?column?
----------
init
(1 row)
RESET ROLE;
SET ROLE regress_lr_normal;
SELECT pg_drop_replication_slot('regression_slot');
ERROR: must be superuser or replication role to use replication slots
RESET ROLE;
-- all users can see existing slots
SET ROLE regress_lr_superuser;
SELECT slot_name, plugin FROM pg_replication_slots;
slot_name | plugin
-----------------+---------------
regression_slot | test_decoding
(1 row)
RESET ROLE;
SET ROLE regress_lr_replication;
SELECT slot_name, plugin FROM pg_replication_slots;
slot_name | plugin
-----------------+---------------
regression_slot | test_decoding
(1 row)
RESET ROLE;
SET ROLE regress_lr_normal;
SELECT slot_name, plugin FROM pg_replication_slots;
slot_name | plugin
-----------------+---------------
regression_slot | test_decoding
(1 row)
RESET ROLE;
-- cleanup
SELECT pg_drop_replication_slot('regression_slot');
pg_drop_replication_slot
--------------------------
(1 row)
DROP ROLE regress_lr_normal;
DROP ROLE regress_lr_superuser;
DROP ROLE regress_lr_replication;
DROP TABLE lr_test;
|
