gemma: schema/manage_users.sql annotate

annotate schema/manage_users.sql @ 370:fe87457a05d7

Store spatial data as geography The main coordinate system should be lat/lon. Just using geometry with SRS 4326 will nonetheless let PostGIS consider coordinates as Cartesian, leading to nonesense results in some calculations. As there is no ST_Within for geography, replace with ST_CoveredBy, which should more correct anyhow, because we now accept also data on the boundary of the responsibility area.

author	Tom Gottfried <tom@intevation.de>
date	Thu, 09 Aug 2018 15:45:16 +0200
parents	5b03f420957d
children	3f803d64a6ee

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
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	64 NEW.map_extent = ST_Extent(CAST(area AS geometry))
fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	65 FROM users.responsibility_areas ra
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	66 WHERE ra.country = NEW.country;
212 229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	67 END IF;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	68 INSERT INTO internal.user_profiles (
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	69 username, country, map_extent, email_address)
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	70 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	71 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	72 '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	73 NEW.username,
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	74 NEW.rolname,
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	75 internal.check_password(NEW.pw));
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	76
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	77 -- Do not leak new password
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	78 NEW.pw = '';
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	79 RETURN NEW;
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	80 END;
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	81 $$
207 88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 195 diff changeset	82 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 195 diff changeset	83 SECURITY DEFINER;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	84
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	85 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	86 EXECUTE PROCEDURE internal.create_user();
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	87
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	88
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	89 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	90 AS $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	91 DECLARE
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	92 cur_username varchar;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	93 BEGIN
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	94 cur_username = OLD.username;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	95
334 df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	96 IF cur_username <> session_user
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	97 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	98 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	99 THEN
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	100 -- 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	101 RETURN NULL;
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	102 END IF;
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	103
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	104 UPDATE internal.user_profiles p
233 531d1f8a2b4b Fix spelling Tom Gottfried <tom@intevation.de> parents: 232 diff changeset	105 SET (username, country, map_extent, email_address)
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	106 = (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	107 WHERE p.username = cur_username;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	108
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	109 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	110 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	111 EXECUTE format(
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	112 '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	113 cur_username = NEW.username;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	114 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	115
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	116 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	117 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	118 EXECUTE format(
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	119 'REVOKE %I FROM %I', OLD.rolname, cur_username);
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	120 EXECUTE format(
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	121 '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	122 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	123
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	124 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	125 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	126 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	127 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	128 cur_username,
312 0745b4d336c4 Place functions in more matching schemas Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	129 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	130 END IF;
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	131
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	132 -- Do not leak new password
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	133 NEW.pw = '';
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	134 RETURN NEW;
225 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
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	140 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	141 EXECUTE PROCEDURE internal.update_user();
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	142
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	143
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	144 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	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
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	150 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	151 LOOP
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	152 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	153 END LOOP;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	154 -- 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	155 -- 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	156 -- 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	157
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	158 -- Delete user
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	159 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	160 DELETE FROM internal.user_profiles p
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	161 WHERE p.username = OLD.username;
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	162
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	163 RETURN OLD;
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	164 END;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	165 $$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	166 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	167 SECURITY DEFINER;
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	168
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	169 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	170 EXECUTE PROCEDURE internal.delete_user();
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	171
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	172
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	173 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	174 SELECT username, pw, email_address FROM users.list_users;

Mercurial > gemma

annotate schema/manage_users.sql @ 370:fe87457a05d7