Mercurial > gemma
changeset 1873:9f8f7d3fd655
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');