Mercurial > gemma
view schema/manage_users.sql @ 226:63dd5216eee4
Refactored gemma server to be more REST-like.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 26 Jul 2018 12:24:30 +0200 |
parents | 8b9cae6d3a21 |
children | 4859aa6c96be |
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_adress users.user_profiles.email_adress%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_adress); 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_adress users.user_profiles.email_adress%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_adress) = (new_username, new_country, new_map_extent, new_email_adress) 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;