Mercurial > gemma
view schema/manage_users.sql @ 4454:69166db6ba8a
console.log removed
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Mon, 23 Sep 2019 09:19:03 +0200 |
parents | 5e38667f740c |
children | baabc2b2f094 |
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_area_utm() RETURNS geometry AS $$ DECLARE utm_area geometry; BEGIN SELECT ST_Union(ST_Transform(area::geometry, z)) INTO STRICT utm_area FROM (SELECT area, best_utm(ST_Collect(area::geometry) OVER ()) AS z FROM users.stretches st JOIN users.stretch_countries stc ON stc.stretch_id = st.id WHERE country = (SELECT country FROM users.list_users WHERE username = current_user)) AS st; 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.stretches st JOIN users.stretch_countries stc ON stc.stretch_id = st.id WHERE stc.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;