Mercurial > gemma
view schema/manage_users.sql @ 263:13ad969a9138
Enable listing of users for all roles with appropriate filters
Waterway users should see their own account data and their should be a
single interface for account data (i.e. users.list_users). Therefore, also
the RLS policy on user_profiles for waterway_admin is translated to the
view.
current_user_country() moved because it's needed earlier during database
setup, now.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 27 Jul 2018 19:03:56 +0200 |
parents | 92470caf81fd |
children | 72062ca52746 |
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 users.user_profiles.country%TYPE AS $$ SELECT country FROM users.user_profiles WHERE username = session_user $$ LANGUAGE SQL SECURITY DEFINER STABLE PARALLEL SAFE; CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS WITH cur AS ( SELECT rolname FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid WHERE member = ( SELECT oid FROM pg_roles WHERE rolname = current_user)) SELECT r.rolname, p.* FROM cur, 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 WHERE p.username = current_user OR cur.rolname = 'waterway_admin' AND p.country = current_user_country() OR cur.rolname = 'sys_admin'; 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, users.check_password(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, users.check_password(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;