Mercurial > gemma

comparison schema/auth.sql @ 1879:9a2fbeaabd52 dev-pdf-generation

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merging in from branch default
author Bernhard Reiter <bernhard@intevation.de>
date Tue, 15 Jan 2019 10:07:10 +0100
parents 40cbfd268aa9
children 661597546ed9
comparison
equal deleted inserted replaced
1878:f030182f82f1 1879:9a2fbeaabd52
75 DO LANGUAGE plpgsql 75 DO LANGUAGE plpgsql
76 $$ 76 $$
77 DECLARE the_table varchar; 77 DECLARE the_table varchar;
78 BEGIN 78 BEGIN
79 FOREACH the_table IN ARRAY ARRAY[ 79 FOREACH the_table IN ARRAY ARRAY[
80 -- 'gauge_measurements', XXX Removed since this table has currently no 80 'gauge_measurements',
81 -- staging
82 'sections_stretches', 81 'sections_stretches',
83 'waterway_profiles', 82 'waterway_profiles',
84 'fairway_dimensions', 83 'fairway_dimensions',
85 'bottlenecks', 84 'bottlenecks',
86 'sounding_results'] 85 'sounding_results']
141 STABLE; 140 STABLE;
142 141
143 -- Staging area 142 -- Staging area
144 -- TODO: add all relevant tables here 143 -- TODO: add all relevant tables here
145 144
145 CREATE POLICY same_country ON waterway.gauge_measurements
146 FOR ALL TO waterway_admin
147 USING ((fk_gauge_id).country_code = users.current_user_country());
148
146 CREATE POLICY responsibility_area ON waterway.bottlenecks 149 CREATE POLICY responsibility_area ON waterway.bottlenecks
147 FOR ALL TO waterway_admin 150 FOR ALL TO waterway_admin
148 USING (utm_covers(area)); 151 USING (utm_covers(area));
149 152
150 CREATE POLICY responsibility_area ON waterway.sounding_results 153 CREATE POLICY responsibility_area ON waterway.sounding_results
151 FOR ALL TO waterway_admin 154 FOR ALL TO waterway_admin
152 USING (utm_covers(area)); 155 USING (utm_covers(area));
153 156
157 -- Imports and import config
158
159 CREATE POLICY same_country ON waterway.imports
160 FOR ALL TO waterway_admin
161 USING (users.current_user_country() = (
162 SELECT country FROM users.list_users lu
163 WHERE lu.username = imports.username));
164 ALTER table waterway.imports ENABLE ROW LEVEL SECURITY;
165
166 -- The job running the import queue is running as sys_admin and login users
167 -- with that role should see all imports anyhow
168 CREATE POLICY read_all ON waterway.imports
169 FOR SELECT TO sys_admin
170 USING (true);
171 CREATE POLICY update_all ON waterway.imports
172 FOR UPDATE TO sys_admin
173 USING (true);
174
175 CREATE POLICY parent_allowed ON waterway.import_logs
176 FOR ALL TO waterway_admin
177 USING (import_id IN (SELECT id FROM waterway.imports));
178 ALTER table waterway.import_logs ENABLE ROW LEVEL SECURITY;
179
180 CREATE POLICY parent_allowed ON waterway.track_imports
181 FOR ALL TO waterway_admin
182 USING (import_id IN (SELECT id FROM waterway.imports));
183 ALTER table waterway.track_imports ENABLE ROW LEVEL SECURITY;
184
154 CREATE POLICY import_configuration_policy ON waterway.import_configuration 185 CREATE POLICY import_configuration_policy ON waterway.import_configuration
155 FOR ALL TO waterway_admin 186 FOR ALL TO waterway_admin
156 USING ( 187 USING (
157 users.current_user_country() = ( 188 users.current_user_country() = (
158 SELECT country FROM users.list_users lu 189 SELECT country FROM users.list_users lu
159 WHERE lu.username = waterway.import_configuration.username)); 190 WHERE lu.username = waterway.import_configuration.username));
160 191
192 CREATE POLICY import_configuration_policy_sys_admin ON waterway.import_configuration
193 FOR ALL TO sys_admin
194 USING (true);
195
161 ALTER table waterway.import_configuration ENABLE ROW LEVEL SECURITY; 196 ALTER table waterway.import_configuration ENABLE ROW LEVEL SECURITY;
162 197
163 COMMIT; 198 COMMIT;