gemma: schema/manage_users.sql annotate

annotate 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

rev	line source
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	1 --
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	2 -- Functions encapsulating user management functionality and
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	3 -- exposing it to appropriately privileged users
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	4 --
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	5
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	6 CREATE OR REPLACE FUNCTION users.check_password(
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	7 pw varchar
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	8 )
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	9 RETURNS varchar
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	10 AS $$
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	11 DECLARE
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	12 min_len CONSTANT int = 8;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	13 BEGIN
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	14 IF char_length(pw) < min_len
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	15 OR pw NOT SIMILAR TO '%[^[:alnum:]]%'
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	16 OR pw NOT SIMILAR TO '%[[:digit:]]%'
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	17 THEN
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	18 RAISE invalid_password USING MESSAGE = 'Invalid password';
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	19 ELSE
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	20 RETURN pw;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	21 END IF;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	22 END;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	23 $$
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	24 LANGUAGE plpgsql;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	25
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	26
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	27 -- Security-definer function to get current users country, which allows to
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	28 -- restrict the view on user_profiles by country without infinite recursion
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	29 CREATE FUNCTION current_user_country()
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	30 RETURNS users.user_profiles.country%TYPE
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	31 AS $$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	32 SELECT country FROM users.user_profiles WHERE username = session_user
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	33 $$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	34 LANGUAGE SQL
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	35 SECURITY DEFINER
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	36 STABLE PARALLEL SAFE;
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	37
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	38
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	39 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	40 WITH cur AS (
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	41 SELECT rolname
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	42 FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	43 WHERE member = (
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	44 SELECT oid FROM pg_roles WHERE rolname = current_user))
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	45 SELECT r.rolname, p.*
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	46 FROM cur, users.user_profiles p
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	47 JOIN pg_roles u ON p.username = u.rolname
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	48 JOIN pg_auth_members a ON u.oid = a.member
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	49 JOIN pg_roles r ON a.roleid = r.oid
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	50 WHERE p.username = current_user
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	51 OR cur.rolname = 'waterway_admin'
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	52 AND p.country = current_user_country()
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	53 OR cur.rolname = 'sys_admin';
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	54
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	55
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	56 CREATE OR REPLACE FUNCTION sys_admin.create_user(
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	57 userrole varchar,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	58 username users.user_profiles.username%TYPE,
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	59 pw varchar,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	60 country users.user_profiles.country%TYPE,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	61 map_extent users.user_profiles.map_extent%TYPE,
233 531d1f8a2b4b Fix spelling Tom Gottfried <tom@intevation.de> parents: 232 diff changeset	62 email_address users.user_profiles.email_address%TYPE
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	63 )
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	64 RETURNS void
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	65 AS $$
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	66 BEGIN
212 229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	67 IF map_extent IS NULL
229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	68 THEN
229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	69 map_extent = ST_Extent(area) FROM users.responsibility_areas ra
229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	70 WHERE ra.country = create_user.country;
229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	71 END IF;
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	72 INSERT INTO users.user_profiles VALUES (
233 531d1f8a2b4b Fix spelling Tom Gottfried <tom@intevation.de> parents: 232 diff changeset	73 username, country, map_extent, email_address);
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	74 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	75 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	76 username,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	77 userrole,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	78 users.check_password(pw));
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	79 END;
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	80 $$
207 88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 195 diff changeset	81 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 195 diff changeset	82 SECURITY DEFINER;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	83
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	84
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	85 CREATE OR REPLACE FUNCTION sys_admin.update_user(
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	86 username users.user_profiles.username%TYPE,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	87 new_userrole varchar,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	88 new_username users.user_profiles.username%TYPE,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	89 new_pw varchar,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	90 new_country users.user_profiles.country%TYPE,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	91 new_map_extent users.user_profiles.map_extent%TYPE,
233 531d1f8a2b4b Fix spelling Tom Gottfried <tom@intevation.de> parents: 232 diff changeset	92 new_email_address users.user_profiles.email_address%TYPE
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	93 )
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	94 RETURNS void
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	95 AS $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	96 DECLARE
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	97 cur_username name;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	98 cur_userrole name;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	99 BEGIN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	100 cur_username = username;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	101
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	102 UPDATE users.user_profiles p
233 531d1f8a2b4b Fix spelling Tom Gottfried <tom@intevation.de> parents: 232 diff changeset	103 SET (username, country, map_extent, email_address)
531d1f8a2b4b Fix spelling Tom Gottfried <tom@intevation.de> parents: 232 diff changeset	104 = (new_username, new_country, new_map_extent, new_email_address)
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	105 WHERE p.username = cur_username;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	106
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	107 IF new_username <> cur_username
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	108 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	109 EXECUTE format(
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	110 'ALTER ROLE %I RENAME TO %I', username, new_username);
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	111 cur_username = new_username;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	112 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	113
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	114 cur_userrole = rolname FROM pg_roles r
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	115 JOIN pg_auth_members a ON r.oid = a.roleid
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	116 WHERE member = (
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	117 SELECT oid FROM pg_roles WHERE rolname = cur_username);
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	118 IF new_userrole <> cur_userrole
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	119 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	120 EXECUTE format(
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	121 'REVOKE %I FROM %I', cur_userrole, cur_username);
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	122 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	123 -- GRANT new_userrole unconditionally to ensure it's an error to upgrade
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	124 -- a non-existent cur_username (GRANTing a role twice is not an error)
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	125 EXECUTE format(
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	126 'GRANT %I TO %I', new_userrole, cur_username);
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	127
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	128 IF new_pw IS NOT NULL AND new_pw <> ''
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	129 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	130 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	131 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	132 cur_username,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	133 users.check_password(new_pw));
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	134 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	135 END;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	136 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	137 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	138 SECURITY DEFINER;
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	139
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	140
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	141 CREATE OR REPLACE FUNCTION sys_admin.delete_user(
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	142 username users.user_profiles.username%TYPE
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	143 )
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	144 RETURNS void
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	145 AS $$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	146 DECLARE
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	147 bid int;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	148 BEGIN
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	149 -- Terminate the users backends started before the current transaction
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	150 FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = username LOOP
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	151 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	152 END LOOP;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	153 -- Note that any backend that might be started during the transaction
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	154 -- in which this function is executed will not be terminated but lost
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	155 -- without any privileges after commiting this transaction
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	156
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	157 -- Delete user
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	158 EXECUTE format('DROP ROLE %I', username);
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	159 DELETE FROM users.user_profiles p WHERE p.username = delete_user.username;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	160 END;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	161 $$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	162 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	163 SECURITY DEFINER;

Mercurial > gemma

annotate schema/manage_users.sql @ 263:13ad969a9138