Mercurial > gemma
annotate schema/updates/1105/01.improve_rls_performance.sql @ 4988:61eb65394a13
Only use JSON path when configure feature layers with Geoserver REST API.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 05 Mar 2020 11:59:11 +0100 |
parents | 64cd18281c76 |
children |
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)); |