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
|
LOAD 'test_rls_hooks';
CREATE TABLE rls_test_permissive (
username name,
supervisor name,
data integer
);
-- initial test data
INSERT INTO rls_test_permissive VALUES ('regress_r1','regress_s1',4);
INSERT INTO rls_test_permissive VALUES ('regress_r2','regress_s2',5);
INSERT INTO rls_test_permissive VALUES ('regress_r3','regress_s3',6);
CREATE TABLE rls_test_restrictive (
username name,
supervisor name,
data integer
);
-- At least one permissive policy must exist, otherwise
-- the default deny policy will be applied. For
-- testing the only-restrictive-policies from the hook,
-- create a simple 'allow all' policy.
CREATE POLICY p1 ON rls_test_restrictive USING (true);
-- initial test data
INSERT INTO rls_test_restrictive VALUES ('regress_r1','regress_s1',1);
INSERT INTO rls_test_restrictive VALUES ('regress_r2','regress_s2',2);
INSERT INTO rls_test_restrictive VALUES ('regress_r3','regress_s3',3);
CREATE TABLE rls_test_both (
username name,
supervisor name,
data integer
);
-- initial test data
INSERT INTO rls_test_both VALUES ('regress_r1','regress_s1',7);
INSERT INTO rls_test_both VALUES ('regress_r2','regress_s2',8);
INSERT INTO rls_test_both VALUES ('regress_r3','regress_s3',9);
ALTER TABLE rls_test_permissive ENABLE ROW LEVEL SECURITY;
ALTER TABLE rls_test_restrictive ENABLE ROW LEVEL SECURITY;
ALTER TABLE rls_test_both ENABLE ROW LEVEL SECURITY;
CREATE ROLE regress_r1;
CREATE ROLE regress_s1;
GRANT SELECT,INSERT ON rls_test_permissive TO regress_r1;
GRANT SELECT,INSERT ON rls_test_restrictive TO regress_r1;
GRANT SELECT,INSERT ON rls_test_both TO regress_r1;
GRANT SELECT,INSERT ON rls_test_permissive TO regress_s1;
GRANT SELECT,INSERT ON rls_test_restrictive TO regress_s1;
GRANT SELECT,INSERT ON rls_test_both TO regress_s1;
SET ROLE regress_r1;
-- With only the hook's policies, permissive
-- hook's policy is current_user = username
EXPLAIN (costs off) SELECT * FROM rls_test_permissive;
SELECT * FROM rls_test_permissive;
-- success
INSERT INTO rls_test_permissive VALUES ('regress_r1','regress_s1',10);
-- failure
INSERT INTO rls_test_permissive VALUES ('regress_r4','regress_s4',10);
SET ROLE regress_s1;
-- With only the hook's policies, restrictive
-- hook's policy is current_user = supervisor
EXPLAIN (costs off) SELECT * FROM rls_test_restrictive;
SELECT * FROM rls_test_restrictive;
-- success
INSERT INTO rls_test_restrictive VALUES ('regress_r1','regress_s1',10);
-- failure
INSERT INTO rls_test_restrictive VALUES ('regress_r4','regress_s4',10);
SET ROLE regress_s1;
-- With only the hook's policies, both
-- permissive hook's policy is current_user = username
-- restrictive hook's policy is current_user = superuser
-- combined with AND, results in nothing being allowed
EXPLAIN (costs off) SELECT * FROM rls_test_both;
SELECT * FROM rls_test_both;
-- failure
INSERT INTO rls_test_both VALUES ('regress_r1','regress_s1',10);
-- failure
INSERT INTO rls_test_both VALUES ('regress_r4','regress_s1',10);
-- failure
INSERT INTO rls_test_both VALUES ('regress_r4','regress_s4',10);
RESET ROLE;
-- Create "internal" policies, to check that the policies from
-- the hooks are combined correctly.
CREATE POLICY p1 ON rls_test_permissive USING (data % 2 = 0);
-- Remove the original allow-all policy
DROP POLICY p1 ON rls_test_restrictive;
CREATE POLICY p1 ON rls_test_restrictive USING (data % 2 = 0);
CREATE POLICY p1 ON rls_test_both USING (data % 2 = 0);
SET ROLE regress_r1;
-- With both internal and hook policies, permissive
EXPLAIN (costs off) SELECT * FROM rls_test_permissive;
SELECT * FROM rls_test_permissive;
-- success
INSERT INTO rls_test_permissive VALUES ('regress_r1','regress_s1',7);
-- success
INSERT INTO rls_test_permissive VALUES ('regress_r3','regress_s3',10);
-- failure
INSERT INTO rls_test_permissive VALUES ('regress_r4','regress_s4',7);
SET ROLE regress_s1;
-- With both internal and hook policies, restrictive
EXPLAIN (costs off) SELECT * FROM rls_test_restrictive;
SELECT * FROM rls_test_restrictive;
-- success
INSERT INTO rls_test_restrictive VALUES ('regress_r1','regress_s1',8);
-- failure
INSERT INTO rls_test_restrictive VALUES ('regress_r3','regress_s3',10);
-- failure
INSERT INTO rls_test_restrictive VALUES ('regress_r1','regress_s1',7);
-- failure
INSERT INTO rls_test_restrictive VALUES ('regress_r4','regress_s4',7);
-- With both internal and hook policies, both permissive
-- and restrictive hook policies
EXPLAIN (costs off) SELECT * FROM rls_test_both;
SELECT * FROM rls_test_both;
-- success
INSERT INTO rls_test_both VALUES ('regress_r1','regress_s1',8);
-- failure
INSERT INTO rls_test_both VALUES ('regress_r3','regress_s3',10);
-- failure
INSERT INTO rls_test_both VALUES ('regress_r1','regress_s1',7);
-- failure
INSERT INTO rls_test_both VALUES ('regress_r4','regress_s4',7);
RESET ROLE;
DROP TABLE rls_test_restrictive;
DROP TABLE rls_test_permissive;
DROP TABLE rls_test_both;
DROP ROLE regress_r1;
DROP ROLE regress_s1;
|
