Mercurial > gemma
annotate tap_tests.sql @ 104:bc1c7fa3a939
Add tests for authorisation of templates.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 15 Jun 2018 18:15:04 +0200 |
parents | 7f934f77831a |
children | ae5bb7a979ff |
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 |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
8 SELECT plan(7); -- 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 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 SET search_path TO public, wamos, wamos_waterway, wamos_fairway; |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 -- Add test data |
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 INSERT INTO countries VALUES ('AT'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 INSERT INTO user_profiles (username, country, email_adress) |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 VALUES |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 ('waterway_user', 'AT', 'xxx'), |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 ('waterway_admin', 'AT', 'yyy'), |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 ('sys_admin', 'AT', 'zzz'); |
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 INSERT INTO limiting_factors VALUES ('depth'), ('width'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 INSERT INTO gauges ( |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 location, function_code, objname, geom, zero_point, source_organization) |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 VALUES |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 (('AT', 'XXX', '00001', '00000', 1)::isrs, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 'xxx', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 'testgauge', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 ST_geomfromtext('POINT(0 0)', 4326), |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 0, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 'testorganization'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 INSERT INTO bottlenecks ( |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 revisiting_time, limiting, source_organization) |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 VALUES |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 ('testbottleneck1', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 ('AT', 'XXX', '00001', '00000', 1)::isrs, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 ('AT', 'XXX', '00001', '00000', 2)::isrs), |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326), |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 'AT', 'AT', 'AT', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 1, 'depth', 'testorganization'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 INSERT INTO bottlenecks ( |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 revisiting_time, limiting, source_organization, staging_done) |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 VALUES |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 ('testbottleneck2', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 ('AT', 'XXX', '00001', '00000', 1)::isrs, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 ('AT', 'XXX', '00001', '00000', 2)::isrs), |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326), |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 'AT', 'AT', 'AT', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 1, 'depth', 'testorganization', true); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
56 INSERT INTO templates (dummy_attrib) |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
57 VALUES ('usertemplate'), ('othertemplate'); |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
58 INSERT INTO user_templates |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
59 SELECT 'waterway_user', id FROM templates |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
60 WHERE dummy_attrib = 'usertemplate'; |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
61 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 -- Run tests as unprivileged user |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
64 -- |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
65 SET SESSION AUTHORIZATION waterway_user; |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 SELECT throws_ok('CREATE TABLE test()', 42501, NULL, |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 'No objects can be created'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
69 |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
70 SELECT isnt_empty('SELECT * FROM bottlenecks', |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
71 'Staged data should be visible'); |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 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
|
73 'Only staged data should be visible'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 SELECT set_eq('SELECT count(*) FROM wamos.user_profiles', ARRAY[1], |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 'User should only see his own profile'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
77 SELECT results_eq('SELECT username FROM user_profiles', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 'SELECT CAST(current_user AS varchar)', |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 'User should only see his own profile'); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 |
104
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
81 SELECT isnt_empty('SELECT * FROM templates', |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
82 'User should see templates associated to him'); |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
83 SELECT is_empty('SELECT * FROM templates t |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
84 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
|
85 WHERE username <> current_user', |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
86 'User should only see templates associated to him'); |
bc1c7fa3a939
Add tests for authorisation of templates.
Tom Gottfried <tom@intevation.de>
parents:
100
diff
changeset
|
87 |
100
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
88 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 -- finish tests |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
90 -- |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 SELECT * FROM finish(); |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
92 |
7f934f77831a
Add first tests of RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 ROLLBACK; |