Mercurial > gemma

comparison auth.sql @ 103:b29538ac409d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix authorisation of templates for waterway_user. SELECT privilege for relation-table is needed to rely on it in RLS policy. Removed unnecessary JOINs that lead to infinite recursion.
author Tom Gottfried <tom@intevation.de>
date Fri, 15 Jun 2018 18:12:40 +0200
parents 81a2b26bf16b
children b5e403843639
comparison
equal deleted inserted replaced
102:3780a1afdc98 103:b29538ac409d
19 -- Privileges for waterway_user 19 -- Privileges for waterway_user
20 -- 20 --
21 GRANT USAGE ON SCHEMA wamos, wamos_waterway, wamos_fairway TO waterway_user; 21 GRANT USAGE ON SCHEMA wamos, wamos_waterway, wamos_fairway TO waterway_user;
22 GRANT SELECT ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway 22 GRANT SELECT ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway
23 TO waterway_user; 23 TO waterway_user;
24 GRANT SELECT ON templates, user_profiles TO waterway_user; 24 GRANT SELECT ON templates, user_templates, user_profiles TO waterway_user;
25 25
26 -- 26 --
27 -- Extended privileges for waterway_admin 27 -- Extended privileges for waterway_admin
28 -- 28 --
29 GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway 29 GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway
61 CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user 61 CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user
62 USING (username = current_user); 62 USING (username = current_user);
63 ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY; 63 ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
64 64
65 CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user 65 CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user
66 USING (id IN(SELECT t.id FROM templates t 66 USING (id IN(SELECT template_id FROM user_templates
67 JOIN user_templates ut ON t.id = ut.template_id 67 WHERE username = current_user));
68 JOIN user_profiles p ON ut.username = p.username
69 WHERE p.username = current_user));
70 ALTER TABLE templates ENABLE ROW LEVEL SECURITY; 68 ALTER TABLE templates ENABLE ROW LEVEL SECURITY;
71 69
72 -- 70 --
73 -- RLS policies for waterway_admin 71 -- RLS policies for waterway_admin
74 -- 72 --