Mercurial > gemma

annotate schema/updates/1105/01.improve_rls_performance.sql @ 5361:ce1fe22bda5a extented-report

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Backed out changeset f845c3b7b68e
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 22 Jun 2021 17:12:17 +0200
parents 64cd18281c76
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE OR REPLACE FUNCTION import.is_new_key(
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 tablename varchar,
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 kv anyelement)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 RETURNS boolean
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 AS $$
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 DECLARE columnname varchar;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 DECLARE ret boolean;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 BEGIN
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 columnname = (SELECT column_name
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 FROM information_schema.constraint_column_usage k
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 JOIN information_schema.table_constraints USING (constraint_name)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY');
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 EXECUTE format('SELECT NOT EXISTS(SELECT 1 FROM import.%I WHERE %I = $1)',
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 tablename, columnname)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 INTO ret
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 USING kv;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 RETURN ret;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 END;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 $$
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 LANGUAGE plpgsql
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 SECURITY DEFINER
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 STABLE PARALLEL SAFE;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 DROP POLICY parent_allowed ON import.import_logs;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 CREATE POLICY parent_allowed ON import.import_logs
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 FOR ALL TO waterway_admin
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 WITH CHECK (import.is_new_key('imports', import_id)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 DROP POLICY parent_allowed ON import.track_imports;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 CREATE POLICY parent_allowed ON import.track_imports
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 FOR ALL TO waterway_admin
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 WITH CHECK (import.is_new_key('imports', import_id)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 DROP POLICY import_configuration_policy ON import.import_configuration;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 CREATE POLICY import_configuration_policy ON import.import_configuration
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 FOR ALL TO waterway_admin
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 USING (EXISTS(SELECT 1 FROM users.list_users lu
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 WHERE lu.username = import_configuration.username));
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 DROP POLICY parent_allowed ON import.import_configuration_attributes;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 CREATE POLICY parent_allowed ON import.import_configuration_attributes
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 FOR ALL TO waterway_admin
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 USING (EXISTS(SELECT 1 FROM import.import_configuration
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 WHERE id = import_configuration_id))
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 WITH CHECK (
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 import.is_new_key('import_configuration', import_configuration_id)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 OR EXISTS(SELECT 1 FROM import.import_configuration
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 WHERE id = import_configuration_id));