Mercurial > gemma

changeset 4755:dfd990a4ac64

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Hide sys_admin accounts to waterway admins Since imports and import configurations are authorized based on the visibility of the user that created the import, that way waterway admins are no longer allowed to see imports and import configurations created by sys_admins.
author Tom Gottfried <tom@intevation.de>
date Fri, 18 Oct 2019 17:55:12 +0200
parents 64979fec89a7
children 415c1993c7fe
files schema/auth_tests.sql schema/gemma.sql schema/run_tests.sh schema/updates/1312/01.hide_sys_admins.sql
diffstat 4 files changed, 81 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- a/schema/auth_tests.sql	Fri Oct 18 17:32:02 2019 +0200
+++ b/schema/auth_tests.sql	Fri Oct 18 17:55:12 2019 +0200
@@ -4,7 +4,7 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018 by via donau
+-- Copyright (C) 2018, 2019 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
@@ -139,6 +139,31 @@
     'Waterway admin cannot delete templates for other country');
 
 -- import management
+SET SESSION AUTHORIZATION test_sys_admin1;
+SELECT lives_ok($$
+    WITH
+    job AS (
+        INSERT INTO import.imports (kind, username, data) VALUES (
+            'test', current_user, 'test') RETURNING id),
+    log AS (
+        INSERT INTO import.import_logs (import_id, msg)
+            SELECT id, 'test' FROM job)
+    INSERT INTO import.track_imports (import_id, relation, key)
+        SELECT id, 'waterway.bottlenecks', 2 FROM job
+    $$,
+    'Sys_admin can add import job and related data');
+
+SELECT lives_ok($$
+    WITH
+    config AS (
+        INSERT INTO import.import_configuration (kind, username) VALUES (
+            'test', current_user) RETURNING id)
+    INSERT INTO import.import_configuration_attributes
+        SELECT id, 'test key', 'test value' FROM config
+    $$,
+    'Sys_admin can add import config and related data');
+
+SET SESSION AUTHORIZATION test_admin_at;
 SELECT lives_ok($$
     WITH
     job AS (
@@ -167,6 +192,20 @@
     $$,
     'Waterway admin can edit import jobs from his country only');
 
+SELECT bag_hasnt($$
+    WITH job AS (
+        UPDATE import.imports SET state = 'accepted'
+            RETURNING id, username),
+    log AS (
+        INSERT INTO import.import_logs (import_id, msg)
+            SELECT id, 'test continued' FROM job)
+    SELECT username FROM job
+    $$,
+    $$
+    SELECT username FROM users.list_users WHERE rolname = 'sys_admin'
+    $$,
+    'Waterway admin cannot edit import jobs of sys_admins');
+
 SELECT lives_ok($$
     WITH
     config AS (
@@ -190,11 +229,29 @@
             SELECT id, 'test continued', 'test value' FROM config),
     attrib_upd AS (
         UPDATE import.import_configuration_attributes SET v = 'test v'
-            WHERE import_configuration_id = (SELECT id FROM config))
+            WHERE import_configuration_id IN (SELECT id FROM config))
     SELECT username FROM config
     $$,
     'Waterway admin can edit import config from his country only');
 
+SELECT bag_hasnt($$
+    WITH config AS (
+        UPDATE import.import_configuration SET kind = 'test'
+            RETURNING id, username),
+    attrib AS (
+        INSERT INTO import.import_configuration_attributes
+            SELECT id, 'test continued 1', 'test value' FROM config),
+    attrib_upd AS (
+        UPDATE import.import_configuration_attributes SET v = 'test v'
+            WHERE import_configuration_id IN (SELECT id FROM config))
+    SELECT username FROM config
+    $$,
+    $$
+    SELECT username FROM users.list_users
+        WHERE rolname = 'sys_admin' AND country = 'RO'
+    $$,
+    'Waterway admin cannot edit import config from sys_admin');
+
 SET SESSION AUTHORIZATION test_admin_ro;
 SELECT throws_ok($$
     INSERT INTO import.import_logs (import_id, msg)
--- a/schema/gemma.sql	Fri Oct 18 17:32:02 2019 +0200
+++ b/schema/gemma.sql	Fri Oct 18 17:55:12 2019 +0200
@@ -441,7 +441,7 @@
     CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
-    CREATE VIEW users.list_users WITH (security_barrier) AS
+    CREATE VIEW list_users WITH (security_barrier) AS
         SELECT
             r.rolname,
             p.username,
@@ -458,6 +458,7 @@
                 AND p.country = (
                     SELECT country FROM internal.user_profiles
                         WHERE username = current_user)
+                AND r.rolname <> 'sys_admin'
             OR pg_has_role('sys_admin', 'MEMBER')
 ;
 
--- a/schema/run_tests.sh	Fri Oct 18 17:32:02 2019 +0200
+++ b/schema/run_tests.sh	Fri Oct 18 17:55:12 2019 +0200
@@ -80,7 +80,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f "$BASEDIR"/tap_tests_data.sql \
-    -c "SELECT plan(77 + (
+    -c "SELECT plan(81 + (
             SELECT count(*)::int
                 FROM information_schema.tables
                 WHERE table_schema = 'waterway'))" \
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1312/01.hide_sys_admins.sql	Fri Oct 18 17:55:12 2019 +0200
@@ -0,0 +1,19 @@
+CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
+    SELECT
+        r.rolname,
+        p.username,
+        CAST('' AS varchar) AS pw,
+        p.country,
+        p.map_extent,
+        p.email_address
+    FROM internal.user_profiles p
+        JOIN pg_roles u ON p.username = u.rolname
+        JOIN pg_auth_members a ON u.oid = a.member
+        JOIN pg_roles r ON a.roleid = r.oid
+    WHERE p.username = current_user
+        OR pg_has_role('waterway_admin', 'MEMBER')
+            AND p.country = (
+                SELECT country FROM internal.user_profiles
+                    WHERE username = current_user)
+            AND r.rolname <> 'sys_admin'
+        OR pg_has_role('sys_admin', 'MEMBER')