Mercurial > gemma
view schema/manage_users.sql @ 4606:dfe9cde6a20c geoserver_sql_views
Reflect database model changes for SQL views in backend
In principle, we could use many datasources with different database
schemas, but this would imply changing GeoServer initialization,
service filtering, endpoints and eventually more. Since we do not need
it, just hard-code the schema name as a constant.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 05 Sep 2019 12:23:31 +0200 |
parents | 5466562cca60 |
children | 5e38667f740c |
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_Transform(area::geometry, best_utm(area)) INTO STRICT utm_area FROM users.responsibility_areas WHERE country = (SELECT country FROM users.list_users WHERE username = current_user); 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;