Mercurial > gemma
view schema/updates/1105/01.improve_rls_performance.sql @ 5131:52e3980e3462 queued-stage-done
review decisions controller: Lowered immediate feedback timeout to 5 secs.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 27 Mar 2020 13:09:02 +0100 |
parents | 64cd18281c76 |
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));