Mercurial > gemma

annotate schema/manage_users.sql @ 233:531d1f8a2b4b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix spelling
author Tom Gottfried <tom@intevation.de>
date Thu, 26 Jul 2018 15:02:19 +0200
parents 4859aa6c96be
children 946baea3d280
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
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
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
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
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
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
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
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
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
50 SET (username, country, map_extent, email_address)
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
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;