Mercurial > gemma

annotate schema/manage_users.sql @ 303:75e32633fb96

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Makefile: Force rebuild of dependencies when 3rdpartylibs.sh changes.
author Sascha Wilde <wilde@intevation.de>
date Wed, 01 Aug 2018 13:37:48 +0200
parents 0b2d9f96ddb8
children 750a9c9cd965
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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()
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
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
41 SELECT r.rolname, p.*
269
0b2d9f96ddb8 Replace CTE with access privilege inquiry function call.
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
42 FROM internal.user_profiles p
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
43 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
44 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
45 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
46 WHERE p.username = current_user
269
0b2d9f96ddb8 Replace CTE with access privilege inquiry function call.
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
47 OR pg_has_role('waterway_admin', 'MEMBER')
263
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
48 AND p.country = current_user_country()
269
0b2d9f96ddb8 Replace CTE with access privilege inquiry function call.
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
49 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
50
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
51
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
52 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
53 userrole varchar,
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
54 username internal.user_profiles.username%TYPE,
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 pw varchar,
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
56 country internal.user_profiles.country%TYPE,
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
57 map_extent internal.user_profiles.map_extent%TYPE,
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
58 email_address internal.user_profiles.email_address%TYPE
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 )
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 RETURNS void
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 AS $$
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 BEGIN
212
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
63 IF map_extent IS NULL
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
64 THEN
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
65 map_extent = ST_Extent(area) FROM users.responsibility_areas ra
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
66 WHERE ra.country = create_user.country;
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)
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
70 VALUES (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
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',
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
73 username,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
74 userrole,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
75 users.check_password(pw));
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 END;
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 $$
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
78 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
79 SECURITY DEFINER;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
80
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
81
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
82 CREATE OR REPLACE FUNCTION sys_admin.update_user(
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
83 username internal.user_profiles.username%TYPE,
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
84 new_userrole varchar,
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
85 new_username internal.user_profiles.username%TYPE,
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
86 new_pw varchar,
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
87 new_country internal.user_profiles.country%TYPE,
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
88 new_map_extent internal.user_profiles.map_extent%TYPE,
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
89 new_email_address internal.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
90 )
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
91 RETURNS void
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
92 AS $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
93 DECLARE
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
94 cur_username name;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
95 cur_userrole name;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
96 BEGIN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
97 cur_username = username;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
98
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
99 UPDATE internal.user_profiles p
233
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
100 SET (username, country, map_extent, email_address)
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
101 = (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
102 WHERE p.username = cur_username;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
103
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
104 IF new_username <> cur_username
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
105 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
106 EXECUTE format(
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
107 '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
108 cur_username = new_username;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
109 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
110
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
111 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
112 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
113 WHERE member = (
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
114 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
115 IF new_userrole <> cur_userrole
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(
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
118 '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
119 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
120 -- 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
121 -- 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
122 EXECUTE format(
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
123 '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
124
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
125 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
126 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
127 EXECUTE format(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
128 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
129 cur_username,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
130 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
131 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
132 END;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
133 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
134 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
135 SECURITY DEFINER;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
136
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
137
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
138 CREATE OR REPLACE FUNCTION sys_admin.delete_user(
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
139 username internal.user_profiles.username%TYPE
232
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 RETURNS void
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
142 AS $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
143 DECLARE
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
144 bid int;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
145 BEGIN
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
146 -- 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
147 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
148 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
149 END LOOP;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
150 -- 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
151 -- 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
152 -- 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
153
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
154 -- Delete user
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
155 EXECUTE format('DROP ROLE %I', username);
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
156 DELETE FROM internal.user_profiles p
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
157 WHERE p.username = delete_user.username;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
158 END;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
159 $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
160 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
161 SECURITY DEFINER;