Mercurial > gemma
annotate schema/auth_tests.sql @ 207:88d21c29cf04
Care for the fact that role attributes are not inherited
Tests are now run with login roles instead of abstract base roles.
create_user has become a SECURITY DEFINER function, thus circumventing
RLS policies and that a sys_admin cannot CREATE ROLEs by himself. A test
has been added to showcase the intentional error in case the name of
an abstract base role is used as a new username.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 23 Jul 2018 11:29:41 +0200 |
parents | b67208d82543 |
children | 57dfab80973c |
rev | line source |
---|---|
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 -- |
183
f3a09fc9c1eb
Prepare for having more than one database test script
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
2 -- pgTAP test script for privileges and RLS policies |
f3a09fc9c1eb
Prepare for having more than one database test script
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
3 -- |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 -- Run tests as unprivileged user |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
7 -- |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
8 SET SESSION AUTHORIZATION user_at; |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 SELECT throws_ok('CREATE TABLE test()', 42501, NULL, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 'No objects can be created'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
13 SELECT isnt_empty('SELECT * FROM waterway.bottlenecks', |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
14 'Staged data should be visible'); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
15 SELECT is_empty('SELECT * FROM waterway.bottlenecks WHERE NOT staging_done', |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 'Only staged data should be visible'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
18 SELECT set_eq('SELECT count(*) FROM users.user_profiles', ARRAY[1], |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 'User should only see his own profile'); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
20 SELECT results_eq('SELECT username FROM users.user_profiles', |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 'SELECT CAST(current_user AS varchar)', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 'User should only see his own profile'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
24 SELECT isnt_empty('SELECT * FROM users.templates', |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
25 'User should see templates associated to him'); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
26 SELECT is_empty('SELECT * FROM users.templates |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
27 JOIN users.user_templates USING (template_name) |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
28 WHERE username <> current_user', |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
29 'User should only see templates associated to him'); |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
30 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 -- |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
32 -- Run tests as waterway administrator |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
33 -- |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
34 SET SESSION AUTHORIZATION admin_at; |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
35 |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
36 PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
37 INSERT INTO waterway.bottlenecks ( |
182 | 38 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, |
39 revisiting_time, limiting, source_organization) | |
40 VALUES ( | |
41 $1, | |
42 ('AT', 'XXX', '00001', '00000', 1)::isrs, | |
43 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, | |
44 ('AT', 'XXX', '00001', '00000', 2)::isrs), | |
45 $2, 'AT', 'AT', 'AT', | |
46 1, 'depth', 'testorganization' | |
47 ); | |
48 SELECT lives_ok('EXECUTE bn_insert( | |
49 ''test1'', | |
50 ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'', | |
51 4326))', | |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
52 'Waterway admin can insert data within his region'); |
182 | 53 SELECT throws_ok('EXECUTE bn_insert( |
54 ''test2'', | |
55 ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'', | |
56 4326))', | |
57 42501, NULL, | |
106
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
58 'Waterway admin cannot insert data outside his region'); |
ae5bb7a979ff
Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents:
104
diff
changeset
|
59 |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
175
diff
changeset
|
60 -- template management |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
61 SELECT isnt_empty('SELECT * FROM users.templates |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
62 JOIN users.user_templates USING (template_name) |
112 | 63 WHERE username <> current_user', |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
64 'Waterway admin should see templates of other users'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
65 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
66 SELECT lives_ok('INSERT INTO users.templates (template_name, template_data) |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
67 VALUES (''New AT'', ''\x''); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
68 INSERT INTO users.user_templates |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
69 VALUES (''user_at'', ''New AT'')', |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
70 'Waterway admin can add templates for users in his country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
71 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
72 SELECT throws_ok('INSERT INTO users.user_templates |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
73 VALUES (''waterway_user2'', ''AT'')', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
74 42501, NULL, |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
75 'Waterway admin cannot add template for other country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
76 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
77 SELECT isnt_empty('UPDATE users.templates SET template_data = ''\xDABE'' |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
78 WHERE template_name = ''AT'' RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
79 'Waterway admin can alter templates for own country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
80 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
81 SELECT is_empty('UPDATE users.templates SET template_data = ''\xDABE'' |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
82 WHERE template_name = ''RO'' RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
83 'Waterway admin cannot alter templates for other country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
84 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
85 SELECT isnt_empty('DELETE FROM users.templates WHERE template_name = ''AT'' |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
86 RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
87 'Waterway admin can delete templates for own country'); |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
88 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
183
diff
changeset
|
89 SELECT is_empty('DELETE FROM users.templates WHERE template_name = ''RO'' |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
90 RETURNING *', |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
178
diff
changeset
|
91 'Waterway admin cannot delete templates for other country'); |