Mercurial > gemma
view schema/manage_users.sql @ 244:4ebb791b8278
Added simple script to install db schema.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Thu, 26 Jul 2018 18:51:24 +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;