Mercurial > gemma

annotate schema/manage_users.sql @ 262:92470caf81fd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add database function to check password against policy Though it is currently only used in sys_admin-exclusive functions, it is exposed to normal users because there is nothing to hide and users should be able to change (and check) their password, too.
author Tom Gottfried <tom@intevation.de>
date Fri, 27 Jul 2018 15:26:16 +0200
parents 946baea3d280
children 13ad969a9138
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
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
27 CREATE OR REPLACE VIEW sys_admin.list_users AS
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
28 SELECT r.rolname, p.*
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
29 FROM users.user_profiles p
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
30 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
31 JOIN pg_auth_members a ON u.oid = a.member
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
32 JOIN pg_roles r ON a.roleid = r.oid;
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
33
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
34
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
35 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
36 userrole varchar,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
37 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
38 pw varchar,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
39 country users.user_profiles.country%TYPE,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
40 map_extent users.user_profiles.map_extent%TYPE,
233
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
41 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
42 )
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 RETURNS void
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 AS $$
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 BEGIN
212
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
46 IF map_extent IS NULL
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
47 THEN
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
48 map_extent = ST_Extent(area) FROM users.responsibility_areas ra
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
49 WHERE ra.country = create_user.country;
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
50 END IF;
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
51 INSERT INTO users.user_profiles VALUES (
233
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
52 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
53 EXECUTE format(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
54 '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
55 username,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
56 userrole,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
57 users.check_password(pw));
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 END;
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 $$
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
60 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
61 SECURITY DEFINER;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
62
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
63
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
64 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
65 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
66 new_userrole varchar,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
67 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
68 new_pw varchar,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
69 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
70 new_map_extent users.user_profiles.map_extent%TYPE,
233
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
71 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
72 )
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
73 RETURNS void
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
74 AS $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
75 DECLARE
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
76 cur_username name;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
77 cur_userrole name;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
78 BEGIN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
79 cur_username = username;
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 UPDATE users.user_profiles p
233
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
82 SET (username, country, map_extent, email_address)
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
83 = (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
84 WHERE p.username = cur_username;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
85
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
86 IF new_username <> cur_username
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
87 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
88 EXECUTE format(
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
89 '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
90 cur_username = new_username;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
91 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
92
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
93 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
94 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
95 WHERE member = (
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
96 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
97 IF new_userrole <> cur_userrole
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
98 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
99 EXECUTE format(
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
100 '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
101 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
102 -- 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
103 -- 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
104 EXECUTE format(
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
105 '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
106
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
107 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
108 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
109 EXECUTE format(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
110 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
111 cur_username,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
112 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
113 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
114 END;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
115 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
116 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
117 SECURITY DEFINER;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
118
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
119
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
120 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
121 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
122 )
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
123 RETURNS void
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
124 AS $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
125 DECLARE
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
126 bid int;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
127 BEGIN
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
128 -- 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
129 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
130 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
131 END LOOP;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
132 -- 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
133 -- 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
134 -- 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
135
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
136 -- Delete user
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
137 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
138 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
139 END;
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 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
142 SECURITY DEFINER;