Mercurial > gemma
annotate schema/manage_users.sql @ 244:4ebb791b8278
Added simple script to install db schema.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Thu, 26 Jul 2018 18:51:24 +0200 |
parents | 531d1f8a2b4b |
children | 946baea3d280 |
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 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 -- exposing it to privileged users |
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 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
6 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
|
7 userrole varchar, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
8 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
|
9 pw varchar, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
10 country users.user_profiles.country%TYPE, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
11 map_extent users.user_profiles.map_extent%TYPE, |
233 | 12 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
|
13 ) |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 RETURNS void |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 AS $$ |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 BEGIN |
212
229f385448fa
Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
17 IF map_extent IS NULL |
229f385448fa
Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
18 THEN |
229f385448fa
Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
19 map_extent = ST_Extent(area) FROM users.responsibility_areas ra |
229f385448fa
Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
20 WHERE ra.country = create_user.country; |
229f385448fa
Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
21 END IF; |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
22 INSERT INTO users.user_profiles VALUES ( |
233 | 23 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
|
24 EXECUTE format( |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 END; |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 $$ |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
195
diff
changeset
|
28 LANGUAGE plpgsql |
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
195
diff
changeset
|
29 SECURITY DEFINER; |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
30 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
31 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
32 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
|
33 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
|
34 new_userrole varchar, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
35 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
|
36 new_pw varchar, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
37 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
|
38 new_map_extent users.user_profiles.map_extent%TYPE, |
233 | 39 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
|
40 ) |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
41 RETURNS void |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
42 AS $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
43 DECLARE |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
44 cur_username name; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
45 cur_userrole name; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
46 BEGIN |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
47 cur_username = username; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
48 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
49 UPDATE users.user_profiles p |
233 | 50 SET (username, country, map_extent, email_address) |
51 = (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
|
52 WHERE p.username = cur_username; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
53 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
54 IF new_username <> cur_username |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
55 THEN |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
56 EXECUTE format( |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
57 '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
|
58 cur_username = new_username; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
59 END IF; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
60 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
61 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
|
62 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
|
63 WHERE member = ( |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
64 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
|
65 IF new_userrole <> cur_userrole |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
66 THEN |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
67 EXECUTE format( |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
68 '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
|
69 END IF; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
70 -- 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
|
71 -- 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
|
72 EXECUTE format( |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
73 '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
|
74 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
75 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
|
76 THEN |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
77 EXECUTE format( |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
78 'ALTER ROLE %I PASSWORD %L', cur_username, new_pw); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
79 END IF; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
80 END; |
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 LANGUAGE plpgsql |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
83 SECURITY DEFINER; |
232
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
84 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
85 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
86 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
|
87 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
|
88 ) |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
89 RETURNS void |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
90 AS $$ |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
91 DECLARE |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
92 bid int; |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
93 BEGIN |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
94 -- 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
|
95 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
|
96 PERFORM pg_terminate_backend(bid); |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
97 END LOOP; |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
98 -- 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
|
99 -- 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
|
100 -- 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
|
101 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
102 -- Delete user |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
103 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
|
104 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
|
105 END; |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
106 $$ |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
107 LANGUAGE plpgsql |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
108 SECURITY DEFINER; |