Mercurial > gemma
view schema/updates/1105/01.improve_rls_performance.sql @ 4161:64cd18281c76
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 | |
children |
line wrap: on
line source
CREATE OR REPLACE FUNCTION import.is_new_key( tablename varchar, kv anyelement) RETURNS boolean AS $$ DECLARE columnname varchar; DECLARE ret boolean; BEGIN columnname = (SELECT column_name FROM information_schema.constraint_column_usage k JOIN information_schema.table_constraints USING (constraint_name) WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY'); EXECUTE format('SELECT NOT EXISTS(SELECT 1 FROM import.%I WHERE %I = $1)', tablename, columnname) INTO ret USING kv; RETURN ret; END; $$ LANGUAGE plpgsql SECURITY DEFINER STABLE PARALLEL SAFE; DROP POLICY parent_allowed ON import.import_logs; CREATE POLICY parent_allowed ON import.import_logs FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)) WITH CHECK (import.is_new_key('imports', import_id) OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)); DROP POLICY parent_allowed ON import.track_imports; CREATE POLICY parent_allowed ON import.track_imports FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)) WITH CHECK (import.is_new_key('imports', import_id) OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)); DROP POLICY import_configuration_policy ON import.import_configuration; CREATE POLICY import_configuration_policy ON import.import_configuration FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM users.list_users lu WHERE lu.username = import_configuration.username)); DROP POLICY parent_allowed ON import.import_configuration_attributes; CREATE POLICY parent_allowed ON import.import_configuration_attributes FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM import.import_configuration WHERE id = import_configuration_id)) WITH CHECK ( import.is_new_key('import_configuration', import_configuration_id) OR EXISTS(SELECT 1 FROM import.import_configuration WHERE id = import_configuration_id));