Mercurial > gemma

comparison schema/auth.sql @ 177:4e2451d561b1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Make schema for templates more realistic A print template should be stored with name and timestamp and the template itself will be any data to be interpreted as a template file by a templating engine. Delete relations in user_templates with their parents using CASCADE, because user_templates is only an m:n-table.
author Tom Gottfried <tom@intevation.de>
date Tue, 17 Jul 2018 16:50:16 +0200
parents 3f7053e53fa6
children 382f631d8dd8
comparison
equal deleted inserted replaced
176:0c0826063561 177:4e2451d561b1
55 CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user 55 CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user
56 USING (username = current_user); 56 USING (username = current_user);
57 ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY; 57 ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
58 58
59 CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user 59 CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user
60 USING (id IN(SELECT template_id FROM user_templates 60 USING (template_name IN(SELECT template_name FROM user_templates
61 WHERE username = current_user)); 61 WHERE username = current_user));
62 ALTER TABLE templates ENABLE ROW LEVEL SECURITY; 62 ALTER TABLE templates ENABLE ROW LEVEL SECURITY;
63 63
64 -- 64 --
65 -- RLS policies for waterway_admin 65 -- RLS policies for waterway_admin
85 85
86 CREATE POLICY country_profiles ON user_profiles FOR SELECT TO waterway_admin 86 CREATE POLICY country_profiles ON user_profiles FOR SELECT TO waterway_admin
87 USING (country = current_user_country()); 87 USING (country = current_user_country());
88 88
89 CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin 89 CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin
90 USING (id IN(SELECT template_id FROM user_templates ut 90 USING (template_name IN(SELECT template_name FROM user_templates ut
91 JOIN user_profiles p ON ut.username = p.username 91 JOIN user_profiles p ON ut.username = p.username
92 WHERE p.country = current_user_country())); 92 WHERE p.country = current_user_country()));
93 93
94 COMMIT; 94 COMMIT;