Mercurial > gemma
view schema/manage_users.sql @ 253:322c3d0e05ef
The column in sys_admin.list_users is called rolname not rolename.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 27 Jul 2018 12:12:48 +0200 |
parents | 946baea3d280 |
children | 92470caf81fd |
line wrap: on
line source
-- -- Functions encapsulating user management functionality and -- exposing it to privileged users -- CREATE OR REPLACE VIEW sys_admin.list_users AS SELECT r.rolname, p.* FROM users.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; 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;