Mercurial > gemma

comparison schema/auth.sql @ 4785:1fef9d8e7eb2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved performance for RLS on read access. Only do expensive regional test if data is in staging area (and on write operations).
author Sascha Wilde <wilde@intevation.de>
date Wed, 23 Oct 2019 15:23:52 +0200
parents 2440d2f86f4e
children 9e077ca97505
comparison
equal deleted inserted replaced
4784:ecbb12a6e8ee 4785:1fef9d8e7eb2
127 -- Staging area 127 -- Staging area
128 -- TODO: add all relevant tables here 128 -- TODO: add all relevant tables here
129 129
130 CREATE POLICY same_country ON waterway.gauge_measurements 130 CREATE POLICY same_country ON waterway.gauge_measurements
131 FOR ALL TO waterway_admin 131 FOR ALL TO waterway_admin
132 USING ((location).country_code 132 USING (staging_done
133 = (SELECT country FROM users.list_users WHERE username = current_user) 133 OR (location).country_code =
134 ); 134 (SELECT country FROM users.list_users
135 WHERE username = current_user))
136 WITH CHECK ((location).country_code =
137 (SELECT country FROM users.list_users
138 WHERE username = current_user));
135 139
136 CREATE POLICY same_country ON waterway.waterway_profiles 140 CREATE POLICY same_country ON waterway.waterway_profiles
137 FOR ALL TO waterway_admin 141 FOR ALL TO waterway_admin
138 USING ((location).country_code = (SELECT country FROM users.list_users WHERE username = current_user)); 142 USING (staging_done
143 OR (location).country_code =
144 (SELECT country FROM users.list_users
145 WHERE username = current_user))
146 WITH CHECK ((location).country_code =
147 (SELECT country FROM users.list_users
148 WHERE username = current_user));
139 149
140 CREATE POLICY responsibility_area ON waterway.bottlenecks 150 CREATE POLICY responsibility_area ON waterway.bottlenecks
141 FOR ALL TO waterway_admin 151 FOR ALL TO waterway_admin
142 USING (users.utm_covers(area)); 152 USING (staging_done OR users.utm_covers(area))
153 WITH CHECK (users.utm_covers(area));
143 154
144 CREATE POLICY responsibility_area ON waterway.sounding_results 155 CREATE POLICY responsibility_area ON waterway.sounding_results
145 FOR ALL TO waterway_admin 156 FOR ALL TO waterway_admin
146 USING (users.utm_covers(area)); 157 USING (staging_done OR users.utm_covers(area))
158 WITH CHECK (users.utm_covers(area));
147 159
148 CREATE POLICY responsibility_area ON waterway.fairway_dimensions 160 CREATE POLICY responsibility_area ON waterway.fairway_dimensions
149 FOR ALL TO waterway_admin 161 FOR ALL TO waterway_admin
150 USING (users.utm_covers(area)); 162 USING (staging_done OR users.utm_covers(area))
163 WITH CHECK (users.utm_covers(area));
151 164
152 CREATE POLICY same_country ON waterway.sections 165 CREATE POLICY same_country ON waterway.sections
153 FOR ALL TO waterway_admin 166 FOR ALL TO waterway_admin
154 USING (country = ( 167 USING (country = (
155 SELECT country FROM users.list_users WHERE username = current_user)); 168 SELECT country FROM users.list_users WHERE username = current_user));