Mercurial > gemma

diff schema/updates/1105/01.improve_rls_performance.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
children
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1105/01.improve_rls_performance.sql	Fri Aug 02 17:14:13 2019 +0200
@@ -0,0 +1,52 @@
+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));