Mercurial > gemma

annotate schema/tap_tests.sql @ 143:abfac07bd82a vue-gettext

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
closing branch vue-gettext
author Thomas Junk <thomas.junk@intevation.de>
date Mon, 02 Jul 2018 09:37:53 +0200
parents d349db18bece
children 3f7053e53fa6
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 --
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- pgTAP test script
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 --
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 BEGIN;
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 CREATE EXTENSION pgtap;
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7
112
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
8 SELECT plan(10); -- Give number of tests that have to be run
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9
115
d349db18bece s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 113
diff changeset
10 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents: 106
diff changeset
12 \i tap_tests_data.sql
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
13
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 --
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 -- Run tests as unprivileged user
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
16 --
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 SET SESSION AUTHORIZATION waterway_user;
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 SELECT throws_ok('CREATE TABLE test()', 42501, NULL,
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 'No objects can be created');
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
22 SELECT isnt_empty('SELECT * FROM bottlenecks',
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
23 'Staged data should be visible');
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 SELECT is_empty('SELECT * FROM bottlenecks WHERE NOT staging_done',
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 'Only staged data should be visible');
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26
115
d349db18bece s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 113
diff changeset
27 SELECT set_eq('SELECT count(*) FROM gemma.user_profiles', ARRAY[1],
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 'User should only see his own profile');
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 SELECT results_eq('SELECT username FROM user_profiles',
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 'SELECT CAST(current_user AS varchar)',
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 'User should only see his own profile');
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32
104
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
33 SELECT isnt_empty('SELECT * FROM templates',
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
34 'User should see templates associated to him');
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
35 SELECT is_empty('SELECT * FROM templates t
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
36 JOIN user_templates ut ON t.id = template_id
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
37 WHERE username <> current_user',
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
38 'User should only see templates associated to him');
bc1c7fa3a939 Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents: 100
diff changeset
39
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 --
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
41 -- Run tests as waterway administrator
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 SET SESSION AUTHORIZATION waterway_admin;
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
44
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
45 PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
46 INSERT INTO bottlenecks (
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
47 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
48 revisiting_time, limiting, source_organization)
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
49 VALUES
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
50 ($1,
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
51 ('AT', 'XXX', '00001', '00000', 1)::isrs,
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
52 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
53 ('AT', 'XXX', '00001', '00000', 2)::isrs),
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
54 $2, 'AT', 'AT', 'AT',
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
55 1, 'depth', 'testorganization');
110
c86b20edb398 Comment on understand of docs.
Tom Gottfried <tom@intevation.de>
parents: 108
diff changeset
56 -- XXX: In contrast to what table 240 in the PostgreSQL docs says
c86b20edb398 Comment on understand of docs.
Tom Gottfried <tom@intevation.de>
parents: 108
diff changeset
57 -- (https://www.postgresql.org/docs/10/static/sql-createpolicy.html),
c86b20edb398 Comment on understand of docs.
Tom Gottfried <tom@intevation.de>
parents: 108
diff changeset
58 -- the following tests seem to show that the USING expression is applied
c86b20edb398 Comment on understand of docs.
Tom Gottfried <tom@intevation.de>
parents: 108
diff changeset
59 -- also to an INSERT:
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
60 SELECT lives_ok('EXECUTE bn_insert(''test1'',
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
61 ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'',
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
62 4326))',
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
63 'Waterway admin can insert data within his region');
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
64 SELECT throws_ok('EXECUTE bn_insert(''test2'',
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
65 ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'',
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
66 4326))', 42501, NULL,
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
67 'Waterway admin cannot insert data outside his region');
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
68
112
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
69 -- XXX: Why does this fail? POLICY manage_templates should allow to see
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
70 -- the template of waterway_user.
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
71 SELECT isnt_empty('SELECT * FROM templates t
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
72 JOIN user_templates ut ON t.id = template_id
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
73 WHERE username <> current_user',
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
74 'Waterway admin should see templates of users in country');
894f633e2d3f WIP: Add failing test.
Tom Gottfried <tom@intevation.de>
parents: 110
diff changeset
75
106
ae5bb7a979ff Test RLS policy for responsibility area.
Tom Gottfried <tom@intevation.de>
parents: 104
diff changeset
76 --
100
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 -- finish tests
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 --
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 SELECT * FROM finish();
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80
7f934f77831a Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 ROLLBACK;