Mercurial > gemma

annotate schema/auth_tests.sql @ 1697:f1c3fe8b79f5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Imports: Fixed some golint issues.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 07 Jan 2019 11:02:48 +0100
parents 1d1fc92fc3ea
children cedadd4e3db5
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
6
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
7 -- Copyright (C) 2018 by via donau
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Tom Gottfried <tom@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 579
diff changeset
13
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 --
183
f3a09fc9c1eb Prepare for having more than one database test script
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
15 -- 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
16 --
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 --
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 -- Run tests as unprivileged user
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
20 --
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
21 SET SESSION AUTHORIZATION test_user_at;
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 SELECT throws_ok('CREATE TABLE test()', 42501, NULL,
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 'No objects can be created');
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
26 SELECT isnt_empty('SELECT * FROM waterway.bottlenecks',
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
27 'Staged data should be visible');
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
28 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
29 'Only staged data should be visible');
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
31 SELECT isnt_empty('SELECT * FROM users.templates',
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
32 '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
33 SELECT is_empty('SELECT * FROM users.templates
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
34 JOIN users.user_templates USING (template_name)
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
35 WHERE username <> current_user',
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
36 'User should only see templates associated to him');
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
37
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 --
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
39 -- Run tests as waterway administrator
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
40 --
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
41 SET SESSION AUTHORIZATION test_admin_at;
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
42
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
43 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
44 INSERT INTO waterway.bottlenecks (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
45 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
46 revisiting_time, limiting, source_organization)
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
47 VALUES (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
48 $1,
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
49 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
50 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
51 ('AT', 'XXX', '00001', '00000', 2)::isrs),
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
52 $2, 'AT', 'AT', 'AT',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
53 1, 'depth', 'testorganization'
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
54 );
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
55 SELECT lives_ok('EXECUTE bn_insert(
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
56 ''test1'',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
57 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
58 4326))',
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
59 'Waterway admin can insert data within his region');
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
60 SELECT throws_ok('EXECUTE bn_insert(
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
61 ''test2'',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
62 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
63 4326))',
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 181
diff changeset
64 42501, NULL,
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
65 'Waterway admin cannot insert data outside his region');
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
66
177
4e2451d561b1 Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents: 175
diff changeset
67 -- template management
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
68 SELECT isnt_empty('SELECT * FROM users.templates
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
69 JOIN users.user_templates USING (template_name)
112
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
70 WHERE username <> current_user',
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
71 'Waterway admin should see templates of other users');
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
72
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
73 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
74 VALUES (''New AT'', ''\x'');
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
75 INSERT INTO users.user_templates
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
76 VALUES (''test_user_at'', ''New AT'')',
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
77 '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
78
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
79 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
80 VALUES (''waterway_user2'', ''AT'')',
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
81 42501, NULL,
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
82 'Waterway admin cannot add template for other country');
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
83
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
84 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
85 WHERE template_name = ''AT'' RETURNING *',
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
86 'Waterway admin can alter templates for own country');
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
87
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
88 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
89 WHERE template_name = ''RO'' RETURNING *',
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
90 'Waterway admin cannot alter templates for other country');
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
91
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
92 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
93 RETURNING *',
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
94 'Waterway admin can delete templates for own country');
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
95
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 183
diff changeset
96 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
97 RETURNING *',
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 178
diff changeset
98 'Waterway admin cannot delete templates for other country');