Mercurial > gemma

comparison schema/auth.sql @ 4161:64cd18281c76

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve performance of row level security policies Using constraint_column_usage instead of key_column_usage makes the query twice as fast. I did not explore why. Let's just take it. Using 'EXISTS(... WHERE ... = value) is often more efficient than value IN(...) since it allows the inner query to be executed only up to the point where it turns out to return more than nothing with filtering directly in place.
author Tom Gottfried <tom@intevation.de>
date Fri, 02 Aug 2019 17:14:13 +0200
parents 5466562cca60
children 27ed6f709195
comparison
equal deleted inserted replaced
4160:7cccf7fef3e8 4161:64cd18281c76
74 -- 74 --
75 -- RLS policies for waterway_user 75 -- RLS policies for waterway_user
76 -- 76 --
77 -- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing 77 -- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing
78 -- data modifications generally. 78 -- data modifications generally.
79 -- Sometimes using 'username IN(SELECT username FROM users.list_users)' instead 79 -- Sometimes using 'EXISTS(SELECT 1 FROM list_users WHERE username = ...)'
80 -- of 'username = current_user', because waterway_admin is intentionally 80 -- instead of 'username = current_user', since waterway_admin is intentionally
81 -- allowed more with these policies (note that the subselect implies different 81 -- allowed more with these policies (note that the subselect implies different
82 -- filtering on list_users depending on current_user). 82 -- filtering on list_users depending on current_user).
83 -- 83 --
84 84
85 -- Staging area 85 -- Staging area
174 AS $$ 174 AS $$
175 DECLARE columnname varchar; 175 DECLARE columnname varchar;
176 DECLARE ret boolean; 176 DECLARE ret boolean;
177 BEGIN 177 BEGIN
178 columnname = (SELECT column_name 178 columnname = (SELECT column_name
179 FROM information_schema.key_column_usage k 179 FROM information_schema.constraint_column_usage k
180 JOIN information_schema.table_constraints USING (constraint_name) 180 JOIN information_schema.table_constraints USING (constraint_name)
181 WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY'); 181 WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY');
182 EXECUTE format('SELECT NOT $1 = ANY(SELECT %I FROM import.%I)', 182 EXECUTE format('SELECT NOT EXISTS(SELECT 1 FROM import.%I WHERE %I = $1)',
183 columnname, tablename) 183 tablename, columnname)
184 INTO ret 184 INTO ret
185 USING kv; 185 USING kv;
186 RETURN ret; 186 RETURN ret;
187 END; 187 END;
188 $$ 188 $$
190 SECURITY DEFINER 190 SECURITY DEFINER
191 STABLE PARALLEL SAFE; 191 STABLE PARALLEL SAFE;
192 192
193 CREATE POLICY parent_allowed ON import.import_logs 193 CREATE POLICY parent_allowed ON import.import_logs
194 FOR ALL TO waterway_admin 194 FOR ALL TO waterway_admin
195 USING (import_id IN (SELECT id FROM import.imports)) 195 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
196 WITH CHECK (import.is_new_key('imports', import_id) 196 WITH CHECK (import.is_new_key('imports', import_id)
197 OR import_id IN (SELECT id FROM import.imports)); 197 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
198 ALTER table import.import_logs ENABLE ROW LEVEL SECURITY; 198 ALTER table import.import_logs ENABLE ROW LEVEL SECURITY;
199 199
200 CREATE POLICY parent_allowed ON import.track_imports 200 CREATE POLICY parent_allowed ON import.track_imports
201 FOR ALL TO waterway_admin 201 FOR ALL TO waterway_admin
202 USING (import_id IN (SELECT id FROM import.imports)) 202 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
203 WITH CHECK (import.is_new_key('imports', import_id) 203 WITH CHECK (import.is_new_key('imports', import_id)
204 OR import_id IN (SELECT id FROM import.imports)); 204 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
205 ALTER table import.track_imports ENABLE ROW LEVEL SECURITY; 205 ALTER table import.track_imports ENABLE ROW LEVEL SECURITY;
206 206
207 CREATE POLICY import_configuration_policy ON import.import_configuration 207 CREATE POLICY import_configuration_policy ON import.import_configuration
208 FOR ALL TO waterway_admin 208 FOR ALL TO waterway_admin
209 USING ( 209 -- Relies on a user seeing only users from his own country:
210 (SELECT country FROM users.list_users WHERE username = current_user) = ( 210 USING (EXISTS(SELECT 1 FROM users.list_users lu
211 SELECT country FROM users.list_users lu 211 WHERE lu.username = import_configuration.username));
212 WHERE lu.username = import.import_configuration.username));
213 212
214 CREATE POLICY import_configuration_policy_sys_admin ON import.import_configuration 213 CREATE POLICY import_configuration_policy_sys_admin ON import.import_configuration
215 FOR ALL TO sys_admin 214 FOR ALL TO sys_admin
216 USING (true); 215 USING (true);
217 216
218 ALTER table import.import_configuration ENABLE ROW LEVEL SECURITY; 217 ALTER table import.import_configuration ENABLE ROW LEVEL SECURITY;
219 218
220 CREATE POLICY parent_allowed ON import.import_configuration_attributes 219 CREATE POLICY parent_allowed ON import.import_configuration_attributes
221 FOR ALL TO waterway_admin 220 FOR ALL TO waterway_admin
222 USING (import_configuration_id IN ( 221 USING (EXISTS(SELECT 1 FROM import.import_configuration
223 SELECT id FROM import.import_configuration)) 222 WHERE id = import_configuration_id))
224 WITH CHECK ( 223 WITH CHECK (
225 import.is_new_key('import_configuration', import_configuration_id) 224 import.is_new_key('import_configuration', import_configuration_id)
226 OR import_configuration_id IN ( 225 OR EXISTS(SELECT 1 FROM import.import_configuration
227 SELECT id FROM import.import_configuration)); 226 WHERE id = import_configuration_id));
228 ALTER table import.import_configuration_attributes ENABLE ROW LEVEL SECURITY; 227 ALTER table import.import_configuration_attributes ENABLE ROW LEVEL SECURITY;
229 228
230 COMMIT; 229 COMMIT;