Mercurial > gemma

changeset 1873:9f8f7d3fd655

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix policies interfering badly with integrity checks The 'parent_allowed' policies did not allow concurrent inserts into parent and child table in one statement (like in the tests introduced here) nor would they have allowed deferring foreign keys in transactions.
author Tom Gottfried <tom@intevation.de>
date Thu, 17 Jan 2019 19:22:49 +0100
parents f63712670c25
children 45197e7a7ad3
files schema/auth.sql schema/auth_tests.sql schema/manage_users_tests.sql schema/run_tests.sh schema/tap_tests_data.sql
diffstat 5 files changed, 73 insertions(+), 5 deletions(-) [+]
line wrap: on
line diff
--- a/schema/auth.sql	Thu Jan 17 18:15:15 2019 +0100
+++ b/schema/auth.sql	Thu Jan 17 19:22:49 2019 +0100
@@ -143,14 +143,43 @@
     FOR ALL TO sys_admin
     USING (true);
 
+-- For the given table, check whether the given value is used as primary key,
+-- bypassing row level security.
+CREATE OR REPLACE FUNCTION waterway.is_new_key(
+        tablename varchar,
+        kv anyelement)
+    RETURNS boolean
+AS $$
+DECLARE columnname varchar;
+DECLARE ret boolean;
+BEGIN
+    columnname = (SELECT column_name
+        FROM information_schema.key_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 $1 = ANY(SELECT %I FROM waterway.%I)',
+        columnname, tablename)
+        INTO ret
+        USING kv;
+    RETURN ret;
+END;
+$$
+    LANGUAGE plpgsql
+    SECURITY DEFINER
+    STABLE PARALLEL SAFE;
+
 CREATE POLICY parent_allowed ON waterway.import_logs
     FOR ALL TO waterway_admin
-    USING (import_id IN (SELECT id FROM waterway.imports));
+    USING (import_id IN (SELECT id FROM waterway.imports))
+    WITH CHECK (waterway.is_new_key('imports', import_id)
+        OR import_id IN (SELECT id FROM waterway.imports));
 ALTER table waterway.import_logs ENABLE ROW LEVEL SECURITY;
 
 CREATE POLICY parent_allowed ON waterway.track_imports
     FOR ALL TO waterway_admin
-    USING (import_id IN (SELECT id FROM waterway.imports));
+    USING (import_id IN (SELECT id FROM waterway.imports))
+    WITH CHECK (waterway.is_new_key('imports', import_id)
+        OR import_id IN (SELECT id FROM waterway.imports));
 ALTER table waterway.track_imports ENABLE ROW LEVEL SECURITY;
 
 CREATE POLICY import_configuration_policy ON waterway.import_configuration
@@ -169,7 +198,11 @@
 CREATE POLICY parent_allowed ON waterway.import_configuration_attributes
     FOR ALL TO waterway_admin
     USING (import_configuration_id IN (
-        SELECT id FROM waterway.import_configuration));
+        SELECT id FROM waterway.import_configuration))
+    WITH CHECK (
+        waterway.is_new_key('import_configuration', import_configuration_id)
+        OR import_configuration_id IN (
+            SELECT id FROM waterway.import_configuration));
 ALTER table waterway.import_configuration_attributes ENABLE ROW LEVEL SECURITY;
 
 COMMIT;
--- a/schema/auth_tests.sql	Thu Jan 17 18:15:15 2019 +0100
+++ b/schema/auth_tests.sql	Thu Jan 17 19:22:49 2019 +0100
@@ -121,3 +121,36 @@
     DELETE FROM users.templates WHERE template_name = 'RO' RETURNING *
     $$,
     'Waterway admin cannot delete templates for other country');
+
+-- import management
+SELECT lives_ok($$
+    WITH
+    job AS (
+        INSERT INTO waterway.imports (kind, username, data) VALUES (
+            'test', current_user, 'test') RETURNING id),
+    log AS (
+        INSERT INTO waterway.import_logs (import_id, msg)
+            SELECT id, 'test' FROM job)
+    INSERT INTO waterway.track_imports
+        SELECT id, 'waterway.bottlenecks', 0 FROM job
+    $$,
+    'Waterway admin can add import job and related data');
+
+SELECT lives_ok($$
+    WITH
+    config AS (
+        INSERT INTO waterway.import_configuration (kind, username) VALUES (
+            'test', current_user) RETURNING id)
+    INSERT INTO waterway.import_configuration_attributes
+        SELECT id, 'test key', 'test value' FROM config
+    $$,
+    'Waterway admin can add import config and related data');
+
+SET SESSION AUTHORIZATION test_admin_ro;
+
+SELECT throws_ok($$
+    INSERT INTO waterway.import_logs (import_id, msg)
+        VALUES (1, 'test')
+    $$,
+    42501, NULL,
+    'Waterway admin cannot add log messages to other countries imports');
--- a/schema/manage_users_tests.sql	Thu Jan 17 18:15:15 2019 +0100
+++ b/schema/manage_users_tests.sql	Thu Jan 17 19:22:49 2019 +0100
@@ -40,7 +40,7 @@
 SELECT set_eq($$
     SELECT count(*) FROM users.list_users
     $$,
-    ARRAY[4],
+    ARRAY[5],
     'System admin can see all users');
 
 --
--- a/schema/run_tests.sh	Thu Jan 17 18:15:15 2019 +0100
+++ b/schema/run_tests.sh	Thu Jan 17 19:22:49 2019 +0100
@@ -28,7 +28,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f tap_tests_data.sql \
-    -c 'SELECT plan(47)' \
+    -c 'SELECT plan(50)' \
     -f isrs_tests.sql \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
--- a/schema/tap_tests_data.sql	Thu Jan 17 18:15:15 2019 +0100
+++ b/schema/tap_tests_data.sql	Thu Jan 17 19:22:49 2019 +0100
@@ -29,6 +29,8 @@
 INSERT INTO users.list_users VALUES (
     'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy');
 INSERT INTO users.list_users VALUES (
+    'waterway_admin', 'test_admin_ro', 'admin_ro1$', 'RO', NULL, 'yyx');
+INSERT INTO users.list_users VALUES (
     'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz');
 
 INSERT INTO limiting_factors VALUES ('depth'), ('width');