Mercurial > gemma
view schema/manage_users.sql @ 296:b1116c4ce57f usermanagement
merge with default
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Tue, 31 Jul 2018 15:56:11 +0200 |
parents | 0b2d9f96ddb8 |
children | 750a9c9cd965 |
line wrap: on
line source
-- -- Functions encapsulating user management functionality and -- exposing it to appropriately privileged users -- CREATE OR REPLACE FUNCTION users.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; -- Security-definer function to get current users country, which allows to -- restrict the view on user_profiles by country without infinite recursion CREATE FUNCTION current_user_country() RETURNS internal.user_profiles.country%TYPE AS $$ SELECT country FROM internal.user_profiles WHERE username = session_user $$ LANGUAGE SQL SECURITY DEFINER STABLE PARALLEL SAFE; CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS SELECT r.rolname, p.* 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 = current_user_country() OR pg_has_role('sys_admin', 'MEMBER'); CREATE OR REPLACE FUNCTION sys_admin.create_user( userrole varchar, username internal.user_profiles.username%TYPE, pw varchar, country internal.user_profiles.country%TYPE, map_extent internal.user_profiles.map_extent%TYPE, email_address internal.user_profiles.email_address%TYPE ) RETURNS void AS $$ BEGIN IF map_extent IS NULL THEN map_extent = ST_Extent(area) FROM users.responsibility_areas ra WHERE ra.country = create_user.country; END IF; INSERT INTO internal.user_profiles ( username, country, map_extent, email_address) VALUES (username, country, map_extent, email_address); EXECUTE format( 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, users.check_password(pw)); END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE OR REPLACE FUNCTION sys_admin.update_user( username internal.user_profiles.username%TYPE, new_userrole varchar, new_username internal.user_profiles.username%TYPE, new_pw varchar, new_country internal.user_profiles.country%TYPE, new_map_extent internal.user_profiles.map_extent%TYPE, new_email_address internal.user_profiles.email_address%TYPE ) RETURNS void AS $$ DECLARE cur_username name; cur_userrole name; BEGIN cur_username = 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', username, new_username); cur_username = new_username; END IF; cur_userrole = rolname FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid WHERE member = ( SELECT oid FROM pg_roles WHERE rolname = cur_username); IF new_userrole <> cur_userrole THEN EXECUTE format( 'REVOKE %I FROM %I', cur_userrole, cur_username); END IF; -- GRANT new_userrole unconditionally to ensure it's an error to upgrade -- a non-existent cur_username (GRANTing a role twice is not an error) EXECUTE format( 'GRANT %I TO %I', new_userrole, cur_username); IF new_pw IS NOT NULL AND new_pw <> '' THEN EXECUTE format( 'ALTER ROLE %I PASSWORD %L', cur_username, users.check_password(new_pw)); END IF; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE OR REPLACE FUNCTION sys_admin.delete_user( username internal.user_profiles.username%TYPE ) RETURNS void 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 = 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', username); DELETE FROM internal.user_profiles p WHERE p.username = delete_user.username; END; $$ LANGUAGE plpgsql SECURITY DEFINER;