Mercurial > gemma
view schema/manage_users.sql @ 234:55dce2f649bc
Allow using the same email-address for multiple users
This can be useful at least in testing scenarios, where one
person wants to have multiple accounts with different roles
and there is no other reason to enforce uniqueness.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 26 Jul 2018 15:10:22 +0200 |
parents | 531d1f8a2b4b |
children | 946baea3d280 |
line wrap: on
line source
-- -- Functions encapsulating user management functionality and -- exposing it to privileged users -- CREATE OR REPLACE FUNCTION sys_admin.create_user( userrole varchar, username users.user_profiles.username%TYPE, pw varchar, country users.user_profiles.country%TYPE, map_extent users.user_profiles.map_extent%TYPE, email_address users.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 users.user_profiles VALUES ( username, country, map_extent, email_address); EXECUTE format( 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw); END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE OR REPLACE FUNCTION sys_admin.update_user( username users.user_profiles.username%TYPE, new_userrole varchar, new_username users.user_profiles.username%TYPE, new_pw varchar, new_country users.user_profiles.country%TYPE, new_map_extent users.user_profiles.map_extent%TYPE, new_email_address users.user_profiles.email_address%TYPE ) RETURNS void AS $$ DECLARE cur_username name; cur_userrole name; BEGIN cur_username = username; UPDATE users.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, new_pw); END IF; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE OR REPLACE FUNCTION sys_admin.delete_user( username users.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 users.user_profiles p WHERE p.username = delete_user.username; END; $$ LANGUAGE plpgsql SECURITY DEFINER;