Mercurial > gemma
view schema/manage_users.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
parents | 5e3e3d9e2c23 |
children |
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 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 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; IF NEW.username IS NOT NULL -- otherwise let the constraint on user_profiles speak THEN EXECUTE format( 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', NEW.username, NEW.rolname, internal.check_password(NEW.pw)); END IF; INSERT INTO internal.user_profiles ( username, country, map_extent, email_address, report_reciever, active) VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, NEW.report_reciever, NEW.active); IF NEW.active THEN EXECUTE format( 'ALTER ROLE %I LOGIN', NEW.username); ELSE EXECUTE format( 'ALTER ROLE %I NOLOGIN', NEW.username); END IF; -- 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; IF NEW.username <> cur_username THEN EXECUTE format( 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username); cur_username = NEW.username; END IF; UPDATE internal.user_profiles p SET (username, country, map_extent, email_address, report_reciever, active) = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, NEW.report_reciever, NEW.active) WHERE p.username = cur_username; 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; IF NEW.active <> OLD.active THEN IF NEW.active THEN EXECUTE format( 'ALTER ROLE %I LOGIN', cur_username); ELSE EXECUTE format( 'ALTER ROLE %I NOLOGIN', cur_username); END IF; 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;