Mercurial > gemma
changeset 4740:2440d2f86f4e
Authorize sections based on country of creator
Disregarding the area of resposibility will allow to create sections
outside of the country of the user in order to allow data analysis
and aggregation based on sections spanning over multiple countries.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 18 Oct 2019 12:04:04 +0200 |
| parents | 257dd6039a28 |
| children | 5164b4450c42 |
| files | schema/auth.sql schema/auth_tests.sql schema/gemma.sql schema/manage_users.sql schema/manage_users_tests.sql schema/updates/1308/01.add_section_country.sql schema/version.sql |
| diffstat | 7 files changed, 76 insertions(+), 37 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth.sql Fri Oct 18 12:01:21 2019 +0200 +++ b/schema/auth.sql Fri Oct 18 12:04:04 2019 +0200 @@ -149,9 +149,10 @@ FOR ALL TO waterway_admin USING (users.utm_covers(area)); -CREATE POLICY responsibility_area ON waterway.sections +CREATE POLICY same_country ON waterway.sections FOR ALL TO waterway_admin - USING (users.utm_covers(area)); + USING (country = ( + SELECT country FROM users.list_users WHERE username = current_user)); CREATE POLICY sys_admin ON users.stretches FOR ALL TO sys_admin
--- a/schema/auth_tests.sql Fri Oct 18 12:01:21 2019 +0200 +++ b/schema/auth_tests.sql Fri Oct 18 12:04:04 2019 +0200 @@ -15,17 +15,6 @@ -- pgTAP test script for privileges and RLS policies -- --- Helper function: -CREATE OR REPLACE FUNCTION users.current_user_country() - RETURNS internal.user_profiles.country%TYPE - AS $$ - SELECT country FROM users.list_users - WHERE username = current_user - $$ - LANGUAGE SQL - STABLE PARALLEL SAFE; - - CREATE FUNCTION test_privs() RETURNS SETOF TEXT AS $$ DECLARE the_schema CONSTANT varchar = 'waterway'; @@ -75,7 +64,7 @@ 'User should see templates associated to his country'); SELECT ok( - users.current_user_country() = ALL( + users.user_country() = ALL( SELECT country FROM users.templates), 'User should only see templates associated to his country');
--- a/schema/gemma.sql Fri Oct 18 12:01:21 2019 +0200 +++ b/schema/gemma.sql Fri Oct 18 12:04:04 2019 +0200 @@ -440,9 +440,43 @@ ) 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 + 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) + OR pg_has_role('sys_admin', 'MEMBER') ; +-- +-- Functions to be used in DEFAULT expresions +-- + +-- Return current_user's country code +CREATE FUNCTION users.user_country(user_name name DEFAULT current_user) + RETURNS internal.user_profiles.country%TYPE + AS $$ + SELECT country FROM users.list_users + WHERE username = user_name + $$ + LANGUAGE SQL + STABLE PARALLEL SAFE; + + -- Namespace for waterway data that can change in a running system CREATE SCHEMA waterway @@ -589,6 +623,8 @@ CHECK(ST_IsValid(CAST(area AS geometry))), objnam varchar NOT NULL, nobjnam varchar, + country char(2) NOT NULL REFERENCES countries + DEFAULT users.user_country(), date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false,
--- a/schema/manage_users.sql Fri Oct 18 12:01:21 2019 +0200 +++ b/schema/manage_users.sql Fri Oct 18 12:04:04 2019 +0200 @@ -38,26 +38,6 @@ LANGUAGE plpgsql; -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) - OR pg_has_role('sys_admin', 'MEMBER'); - - CREATE OR REPLACE FUNCTION users.current_user_area_utm() RETURNS geometry AS $$
--- a/schema/manage_users_tests.sql Fri Oct 18 12:01:21 2019 +0200 +++ b/schema/manage_users_tests.sql Fri Oct 18 12:04:04 2019 +0200 @@ -28,7 +28,7 @@ SELECT best_utm(ST_Collect(area::geometry)) FROM users.stretches st JOIN users.stretch_countries stc ON stc.stretch_id = st.id - WHERE country = users.current_user_country() + WHERE country = users.user_country() $$, 'Geometry has SRID corresponding to best_utm()'); @@ -169,7 +169,7 @@ UPDATE users.list_users SET (pw, map_extent, email_address) = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test') - WHERE country = users.current_user_country() + WHERE country = users.user_country() AND username <> current_user RETURNING * $$,
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1308/01.add_section_country.sql Fri Oct 18 12:04:04 2019 +0200 @@ -0,0 +1,33 @@ +CREATE FUNCTION users.user_country(user_name name DEFAULT current_user) + RETURNS internal.user_profiles.country%TYPE + AS $$ + SELECT country FROM users.list_users + WHERE username = user_name + $$ + LANGUAGE SQL + STABLE PARALLEL SAFE; + +ALTER TABLE waterway.sections ADD country char(2) REFERENCES countries + DEFAULT users.user_country(); + +-- Try to find out the country of existing sections: +UPDATE waterway.sections s SET country = ( + SELECT COALESCE( + users.user_country(( + SELECT i.username + FROM import.imports i + JOIN import.import_logs ON i.id = import_id + WHERE i.kind = 'sec' + AND (state = 'accepted' AND s.staging_done + OR state = 'pending' AND NOT s.staging_done) + AND msg = 'Storing section ''' || s.name || '''' + ORDER BY i.enqueued DESC FETCH FIRST ROW ONLY)), + '--')); + +ALTER TABLE waterway.sections ALTER country SET NOT NULL; + +DROP POLICY responsibility_area ON waterway.sections; +CREATE POLICY same_country ON waterway.sections + FOR ALL TO waterway_admin + USING (country = ( + SELECT country FROM users.list_users WHERE username = current_user));