Mercurial > gemma

comparison schema/auth.sql @ 2818:89f5af7e14ad

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Due to performance problems with users.current_user_country() inlined it as (SELECT country FROM users.list_users WHERE username = current_user) in RLS policies.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 27 Mar 2019 10:52:09 +0100
parents fc7d828695c9
children 69292eb68984
comparison
equal deleted inserted replaced
2817:ff58440ce809 2818:89f5af7e14ad
110 110
111 -- 111 --
112 -- RLS policies for templates 112 -- RLS policies for templates
113 -- 113 --
114 CREATE POLICY select_templates ON users.templates FOR SELECT TO waterway_user 114 CREATE POLICY select_templates ON users.templates FOR SELECT TO waterway_user
115 USING (country IS NULL OR country = users.current_user_country()); 115 USING (country IS NULL OR country = (SELECT country FROM users.list_users WHERE username = current_user));
116 116
117 CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_admin 117 CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_admin
118 USING (country = users.current_user_country()); 118 USING (country = (SELECT country FROM users.list_users WHERE username = current_user));
119 119
120 CREATE POLICY admin_templates ON users.templates FOR ALL TO sys_admin 120 CREATE POLICY admin_templates ON users.templates FOR ALL TO sys_admin
121 USING (true); 121 USING (true);
122 122
123 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY; 123 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
129 -- Staging area 129 -- Staging area
130 -- TODO: add all relevant tables here 130 -- TODO: add all relevant tables here
131 131
132 CREATE POLICY same_country ON waterway.gauge_measurements 132 CREATE POLICY same_country ON waterway.gauge_measurements
133 FOR ALL TO waterway_admin 133 FOR ALL TO waterway_admin
134 USING ((fk_gauge_id).country_code = users.current_user_country()); 134 USING ((fk_gauge_id).country_code = (SELECT country FROM users.list_users WHERE username = current_user));
135 135
136 CREATE POLICY same_country ON waterway.waterway_profiles 136 CREATE POLICY same_country ON waterway.waterway_profiles
137 FOR ALL TO waterway_admin 137 FOR ALL TO waterway_admin
138 USING ((location).country_code = users.current_user_country()); 138 USING ((location).country_code = (SELECT country FROM users.list_users WHERE username = current_user));
139 139
140 CREATE POLICY responsibility_area ON waterway.bottlenecks 140 CREATE POLICY responsibility_area ON waterway.bottlenecks
141 FOR ALL TO waterway_admin 141 FOR ALL TO waterway_admin
142 USING (utm_covers(area)); 142 USING (utm_covers(area));
143 143
210 ALTER table import.track_imports ENABLE ROW LEVEL SECURITY; 210 ALTER table import.track_imports ENABLE ROW LEVEL SECURITY;
211 211
212 CREATE POLICY import_configuration_policy ON import.import_configuration 212 CREATE POLICY import_configuration_policy ON import.import_configuration
213 FOR ALL TO waterway_admin 213 FOR ALL TO waterway_admin
214 USING ( 214 USING (
215 users.current_user_country() = ( 215 (SELECT country FROM users.list_users WHERE username = current_user) = (
216 SELECT country FROM users.list_users lu 216 SELECT country FROM users.list_users lu
217 WHERE lu.username = import.import_configuration.username)); 217 WHERE lu.username = import.import_configuration.username));
218 218
219 CREATE POLICY import_configuration_policy_sys_admin ON import.import_configuration 219 CREATE POLICY import_configuration_policy_sys_admin ON import.import_configuration
220 FOR ALL TO sys_admin 220 FOR ALL TO sys_admin