Mercurial > gemma
view schema/manage_users.sql @ 3301:6514b943654e
Re-enable checking of gauge availability
This partly reverts rev. 1cb6676d1510, which removed this code in
favour of handling database errors later. The thinko in this was
that possibly many NtS messages for the same gauge would lead to
many errors for a single reason and the same amount of unnecessary
database round-trips. Checking whether the current user is allowed
to import data for a gauge now also handles sys_admin correctly
and the import stops with status unchanged if no such gauge is
available.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 16 May 2019 17:22:33 +0200 |
parents | 69292eb68984 |
children | 5466562cca60 |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018, 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- * Sacha Teichmann <sascha.teichmann@intevation.de> -- -- Functions encapsulating user management functionality and -- exposing it to appropriately privileged users -- CREATE OR REPLACE FUNCTION internal.check_password( pw varchar ) RETURNS varchar AS $$ DECLARE min_len CONSTANT int = 8; BEGIN IF char_length(pw) < min_len OR pw NOT SIMILAR TO '%[^[:alnum:]]%' OR pw NOT SIMILAR TO '%[[:digit:]]%' THEN RAISE invalid_password USING MESSAGE = 'Invalid password'; ELSE RETURN pw; END IF; END; $$ 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_country() RETURNS internal.user_profiles.country%TYPE AS $$ SELECT country FROM users.list_users WHERE username = current_user $$ LANGUAGE SQL STABLE PARALLEL SAFE; CREATE OR REPLACE FUNCTION users.current_user_area_utm() RETURNS geometry AS $$ DECLARE utm_area geometry; BEGIN SELECT ST_Transform(area::geometry, best_utm(area)) INTO STRICT utm_area FROM users.responsibility_areas WHERE country = users.current_user_country(); RETURN utm_area; END; $$ LANGUAGE plpgsql STABLE PARALLEL SAFE; CREATE OR REPLACE FUNCTION users.utm_covers(g geography) RETURNS boolean AS $$ SELECT ST_Covers(a, ST_Transform(g::geometry, ST_SRID(a))) FROM users.current_user_area_utm() AS a (a) $$ LANGUAGE SQL STABLE PARALLEL SAFE; CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger AS $$ BEGIN IF NEW.map_extent IS NULL THEN NEW.map_extent = ST_Extent(CAST(area AS geometry)) FROM users.responsibility_areas ra WHERE ra.country = NEW.country; END IF; INSERT INTO internal.user_profiles ( username, country, map_extent, email_address) VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address); EXECUTE format( 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', NEW.username, NEW.rolname, internal.check_password(NEW.pw)); -- Do not leak new password NEW.pw = ''; RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER create_user INSTEAD OF INSERT ON users.list_users FOR EACH ROW EXECUTE PROCEDURE internal.create_user(); CREATE OR REPLACE FUNCTION internal.update_metamorph() RETURNS trigger AS $$ BEGIN EXECUTE format('GRANT %I TO metamorph', NEW.username); RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- Note that PostgreSQL fires triggers for the same event in alphabetical -- order! Make sure that the new role is created before this trigger is fired. CREATE TRIGGER update_metamorph INSTEAD OF INSERT ON users.list_users FOR EACH ROW EXECUTE PROCEDURE internal.update_metamorph(); -- Prevent roles other than sys_admin to update any user but -- themselves (affects waterway_admin) CREATE OR REPLACE FUNCTION internal.authorize_update_user() RETURNS trigger AS $$ BEGIN IF OLD.username <> current_user AND NOT pg_has_role('sys_admin', 'MEMBER') THEN RETURN NULL; ELSE RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; -- Note that PostgreSQL fires triggers for the same event in alphabetical -- order! Make sure that authorization takes place before any other trigger -- is fired that might execute otherwise unauthorized statements! CREATE TRIGGER authorize_update_user INSTEAD OF UPDATE ON users.list_users FOR EACH ROW EXECUTE PROCEDURE internal.authorize_update_user(); CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger AS $$ DECLARE cur_username varchar; BEGIN cur_username = OLD.username; UPDATE internal.user_profiles p SET (username, country, map_extent, email_address) = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address) WHERE p.username = cur_username; IF NEW.username <> cur_username THEN EXECUTE format( 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username); cur_username = NEW.username; END IF; IF NEW.rolname <> OLD.rolname THEN EXECUTE format( 'REVOKE %I FROM %I', OLD.rolname, cur_username); EXECUTE format( 'GRANT %I TO %I', NEW.rolname, cur_username); END IF; IF NEW.pw IS NOT NULL AND NEW.pw <> '' THEN EXECUTE format( 'ALTER ROLE %I PASSWORD %L', cur_username, internal.check_password(NEW.pw)); END IF; -- Do not leak new password NEW.pw = ''; RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER update_user INSTEAD OF UPDATE ON users.list_users FOR EACH ROW EXECUTE PROCEDURE internal.update_user(); CREATE OR REPLACE FUNCTION internal.delete_user() RETURNS trigger AS $$ DECLARE bid int; BEGIN -- Terminate the users backends started before the current transaction FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = OLD.username LOOP PERFORM pg_terminate_backend(bid); END LOOP; -- Note that any backend that might be started during the transaction -- in which this function is executed will not be terminated but lost -- without any privileges after commiting this transaction -- Delete user EXECUTE format('DROP ROLE %I', OLD.username); DELETE FROM internal.user_profiles p WHERE p.username = OLD.username; RETURN OLD; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW EXECUTE PROCEDURE internal.delete_user(); -- To set a role from a hex-encoded user name (which is save from SQL injections). CREATE OR REPLACE FUNCTION public.setrole(role text) RETURNS void AS $$ BEGIN IF role IS NOT NULL AND role <> '' THEN EXECUTE format('SET ROLE %I', convert_from(decode(role, 'hex'), 'UTF-8')); END IF; END; $$ LANGUAGE plpgsql; -- To set a role in form of a plannable statement (which is save from SQL injections). CREATE OR REPLACE FUNCTION public.setrole_plan(role text) RETURNS void AS $$ BEGIN IF role IS NOT NULL AND role <> '' THEN EXECUTE format('SET ROLE %I', role); END IF; END; $$ LANGUAGE plpgsql;