Mercurial > gemma

annotate schema/auth_tests.sql @ 196:b67208d82543

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Make test output more comprehensive Running all tests in one transaction ensures the final output tells about any failing test, not just in the last transaction (i.e. test script). The price is that no traces of the tests are left in the database because we have to rollback in order to have no left-over test roles in the cluster.
author Tom Gottfried <tom@intevation.de>
date Fri, 20 Jul 2018 18:31:45 +0200
parents 5dc8e734487a
children 88d21c29cf04
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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 --
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 SET SESSION AUTHORIZATION waterway_user;
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 --
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
34 SET SESSION AUTHORIZATION waterway_admin;
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
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
38 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
39 revisiting_time, limiting, source_organization)
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
40 VALUES (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
41 $1,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
42 ('AT', 'XXX', '00001', '00000', 1)::isrs,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
43 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
44 ('AT', 'XXX', '00001', '00000', 2)::isrs),
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
45 $2, 'AT', 'AT', 'AT',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
46 1, 'depth', 'testorganization'
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
47 );
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
48 SELECT lives_ok('EXECUTE bn_insert(
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
49 ''test1'',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
50 ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
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
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
53 SELECT throws_ok('EXECUTE bn_insert(
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
54 ''test2'',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
55 ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
56 4326))',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
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
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
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
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
69 VALUES (''waterway_user'', ''New AT'')',
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');