gemma: schema/manage_users.sql annotate

annotate schema/manage_users.sql @ 343:5b03f420957d

Use INSTEAD OF trigger for user creation Now make the whole thing look like a real table. There is no more function in schema sys_admin, thus remove respective privilege test.

author	Tom Gottfried <tom@intevation.de>
date	Mon, 06 Aug 2018 13:25:18 +0200
parents	c6bd6ed18942
children	fe87457a05d7

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
312 0745b4d336c4 Place functions in more matching schemas Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	6 CREATE OR REPLACE FUNCTION internal.check_password(
262 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
312 0745b4d336c4 Place functions in more matching schemas Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	29 CREATE FUNCTION users.current_user_country()
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	30 RETURNS internal.user_profiles.country%TYPE
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	31 AS $$
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	32 SELECT country FROM internal.user_profiles
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	33 WHERE username = session_user
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	34 $$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	35 LANGUAGE SQL
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	36 SECURITY DEFINER
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	37 STABLE PARALLEL SAFE;
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
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	40 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	41 SELECT
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	42 r.rolname,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	43 p.username,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	44 CAST('' AS varchar) AS pw,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	45 p.country,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	46 p.map_extent,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	47 p.email_address
269 0b2d9f96ddb8 Replace CTE with access privilege inquiry function call. Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	48 FROM internal.user_profiles p
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	49 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	50 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	51 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	52 WHERE p.username = current_user
269 0b2d9f96ddb8 Replace CTE with access privilege inquiry function call. Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	53 OR pg_has_role('waterway_admin', 'MEMBER')
312 0745b4d336c4 Place functions in more matching schemas Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	54 AND p.country = users.current_user_country()
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	55 OR pg_has_role('pw_reset', 'MEMBER')
269 0b2d9f96ddb8 Replace CTE with access privilege inquiry function call. Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	56 OR pg_has_role('sys_admin', 'MEMBER');
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	57
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	58
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	59 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	60 AS $$
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	61 BEGIN
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	62 IF NEW.map_extent IS NULL
212 229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	63 THEN
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	64 NEW.map_extent = ST_Extent(area) FROM users.responsibility_areas ra
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	65 WHERE ra.country = NEW.country;
212 229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	66 END IF;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	67 INSERT INTO internal.user_profiles (
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	68 username, country, map_extent, email_address)
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	69 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	70 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	71 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	72 NEW.username,
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	73 NEW.rolname,
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	74 internal.check_password(NEW.pw));
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	75
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	76 -- Do not leak new password
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	77 NEW.pw = '';
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	78 RETURN NEW;
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
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	84 CREATE TRIGGER create_user INSTEAD OF INSERT ON users.list_users FOR EACH ROW
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	85 EXECUTE PROCEDURE internal.create_user();
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	86
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	87
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	88 CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	89 AS $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	90 DECLARE
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	91 cur_username varchar;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	92 BEGIN
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	93 cur_username = OLD.username;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	94
334 df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	95 IF cur_username <> session_user
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	96 AND NOT (pg_has_role(session_user, 'sys_admin', 'MEMBER')
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	97 OR pg_has_role(session_user, 'pw_reset', 'MEMBER'))
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	98 THEN
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	99 -- Discard row. This is what WITH CHECK in an RLS policy would do.
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	100 RETURN NULL;
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	101 END IF;
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	102
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	103 UPDATE internal.user_profiles p
233 531d1f8a2b4b Fix spelling Tom Gottfried <tom@intevation.de> parents: 232 diff changeset	104 SET (username, country, map_extent, email_address)
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	105 = (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	106 WHERE p.username = cur_username;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	107
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	108 IF NEW.username <> cur_username
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	109 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	110 EXECUTE format(
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	111 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username);
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	112 cur_username = NEW.username;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	113 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	114
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	115 IF NEW.rolname <> OLD.rolname
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	116 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	117 EXECUTE format(
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	118 'REVOKE %I FROM %I', OLD.rolname, cur_username);
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	119 EXECUTE format(
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	120 'GRANT %I TO %I', NEW.rolname, cur_username);
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	121 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	122
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	123 IF NEW.pw IS NOT NULL AND NEW.pw <> ''
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	124 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	125 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	126 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	127 cur_username,
312 0745b4d336c4 Place functions in more matching schemas Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	128 internal.check_password(NEW.pw));
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	129 END IF;
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	130
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	131 -- Do not leak new password
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	132 NEW.pw = '';
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	133 RETURN NEW;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	134 END;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	135 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	136 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	137 SECURITY DEFINER;
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	138
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	139 CREATE TRIGGER update_user INSTEAD OF UPDATE ON users.list_users FOR EACH ROW
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	140 EXECUTE PROCEDURE internal.update_user();
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	141
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	142
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	143 CREATE OR REPLACE FUNCTION internal.delete_user() RETURNS trigger
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	144 AS $$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	145 DECLARE
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	146 bid int;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	147 BEGIN
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	148 -- Terminate the users backends started before the current transaction
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	149 FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = OLD.username
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	150 LOOP
232 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
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	158 EXECUTE format('DROP ROLE %I', OLD.username);
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	159 DELETE FROM internal.user_profiles p
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	160 WHERE p.username = OLD.username;
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	161
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	162 RETURN OLD;
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	163 END;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	164 $$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	165 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	166 SECURITY DEFINER;
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	167
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	168 CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	169 EXECUTE PROCEDURE internal.delete_user();
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	170
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	171
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	172 CREATE OR REPLACE VIEW pw_reset.list_users AS
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	173 SELECT username, pw, email_address FROM users.list_users;

Mercurial > gemma

annotate schema/manage_users.sql @ 343:5b03f420957d