Mercurial > gemma

annotate schema/manage_users.sql @ 1234:1a5564655f2a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
refac: Sidebar reorganized In order to make context switches between administrative tasks which are map related and those which are system related, we now have a category "administration" and "systemadministration". The Riverbedmorphology does nothing than display the map, so it is renamed to that (map). In case the context of "systemadministration" is chosen, the "map" brings you just back to the map.
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 20 Nov 2018 09:54:53 +0100
parents 3af7ca761f6a
children 6590208e3ee1
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
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 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
28 SELECT
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
29 r.rolname,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
30 p.username,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
31 CAST('' AS varchar) AS pw,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
32 p.country,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
33 p.map_extent,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
34 p.email_address
269
0b2d9f96ddb8 Replace CTE with access privilege inquiry function call.
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
35 FROM internal.user_profiles p
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
36 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
37 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
38 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
39 WHERE p.username = current_user
269
0b2d9f96ddb8 Replace CTE with access privilege inquiry function call.
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
40 OR pg_has_role('waterway_admin', 'MEMBER')
410
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
41 AND p.country = (
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
42 SELECT country FROM internal.user_profiles
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
43 WHERE username = current_user)
269
0b2d9f96ddb8 Replace CTE with access privilege inquiry function call.
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
44 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
45
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
46
410
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
47 CREATE OR REPLACE FUNCTION users.current_user_country()
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
48 RETURNS internal.user_profiles.country%TYPE
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
49 AS $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
50 SELECT country FROM users.list_users
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
51 WHERE username = current_user
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
52 $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
53 LANGUAGE SQL
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
54 STABLE PARALLEL SAFE;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
55
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
56
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
57 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
58 AS $$
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 BEGIN
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
60 IF NEW.map_extent IS NULL
212
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
61 THEN
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
62 NEW.map_extent = ST_Extent(CAST(area AS geometry))
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
63 FROM users.responsibility_areas ra
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
64 WHERE ra.country = NEW.country;
212
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
65 END IF;
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
66 INSERT INTO internal.user_profiles (
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
67 username, country, map_extent, email_address)
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
68 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
69 EXECUTE format(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
70 '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
71 NEW.username,
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
72 NEW.rolname,
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
73 internal.check_password(NEW.pw));
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
74
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
75 -- Do not leak new password
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
76 NEW.pw = '';
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
77 RETURN NEW;
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 END;
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 $$
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
80 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
81 SECURITY DEFINER;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
82
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
83 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
84 EXECUTE PROCEDURE internal.create_user();
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
85
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
86 CREATE OR REPLACE FUNCTION internal.update_metamorph() RETURNS trigger
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
87 AS $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
88 BEGIN
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
89 EXECUTE format('GRANT %I TO metamorph', NEW.username);
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
90 RETURN NEW;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
91 END;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
92 $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
93 LANGUAGE plpgsql
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
94 SECURITY DEFINER;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
95
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
96 -- Note that PostgreSQL fires triggers for the same event in alphabetical
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
97 -- order! Make sure that the new role is created before this trigger is fired.
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
98 CREATE TRIGGER update_metamorph INSTEAD OF INSERT ON users.list_users
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
99 FOR EACH ROW
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
100 EXECUTE PROCEDURE internal.update_metamorph();
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
101
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
102
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 468
diff changeset
103 -- Prevent roles other than sys_admin to update any user but
410
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
104 -- themselves (affects waterway_admin)
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
105 CREATE OR REPLACE FUNCTION internal.authorize_update_user() RETURNS trigger
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
106 AS $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
107 BEGIN
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
108 IF OLD.username <> current_user
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 468
diff changeset
109 AND NOT pg_has_role('sys_admin', 'MEMBER')
410
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
110 THEN
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
111 RETURN NULL;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
112 ELSE
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
113 RETURN NEW;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
114 END IF;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
115 END;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
116 $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
117 LANGUAGE plpgsql;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
118
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
119 -- Note that PostgreSQL fires triggers for the same event in alphabetical
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
120 -- order! Make sure that authorization takes place before any other trigger
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
121 -- is fired that might execute otherwise unauthorized statements!
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
122 CREATE TRIGGER authorize_update_user INSTEAD OF UPDATE ON users.list_users
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
123 FOR EACH ROW
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
124 EXECUTE PROCEDURE internal.authorize_update_user();
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
125
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
126
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
127 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
128 AS $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
129 DECLARE
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
130 cur_username varchar;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
131 BEGIN
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
132 cur_username = OLD.username;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
133
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
134 UPDATE internal.user_profiles p
233
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
135 SET (username, country, map_extent, email_address)
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
136 = (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
137 WHERE p.username = cur_username;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
138
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
139 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
140 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
141 EXECUTE format(
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
142 '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
143 cur_username = NEW.username;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
144 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
145
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
146 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
147 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
148 EXECUTE format(
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
149 'REVOKE %I FROM %I', OLD.rolname, cur_username);
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
150 EXECUTE format(
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
151 '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
152 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
153
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
154 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
155 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
156 EXECUTE format(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
157 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
158 cur_username,
312
0745b4d336c4 Place functions in more matching schemas
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
159 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
160 END IF;
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
161
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
162 -- Do not leak new password
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
163 NEW.pw = '';
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
164 RETURN NEW;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
165 END;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
166 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
167 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
168 SECURITY DEFINER;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
169
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
170 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
171 EXECUTE PROCEDURE internal.update_user();
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
172
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
173
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
174 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
175 AS $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
176 DECLARE
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
177 bid int;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
178 BEGIN
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
179 -- 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
180 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
181 LOOP
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
182 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
183 END LOOP;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
184 -- 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
185 -- 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
186 -- 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
187
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
188 -- Delete user
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
189 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
190 DELETE FROM internal.user_profiles p
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
191 WHERE p.username = OLD.username;
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
192
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
193 RETURN OLD;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
194 END;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
195 $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
196 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
197 SECURITY DEFINER;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 312
diff changeset
198
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
199 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
200 EXECUTE PROCEDURE internal.delete_user();
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
201
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 312
diff changeset
202
467
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
203 -- To set a role from a hex-encoded user name (which is save from SQL injections).
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
204 CREATE OR REPLACE FUNCTION public.setrole(role text) RETURNS void
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
205 AS $$
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
206 BEGIN
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
207 IF role IS NOT NULL AND role <> '' THEN
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
208 EXECUTE format('SET ROLE %I', convert_from(decode(role, 'hex'), 'UTF-8'));
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
209 END IF;
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
210 END;
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
211 $$
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 463
diff changeset
212 LANGUAGE plpgsql;
468
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
213
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
214 -- To set a role in form of a plannable statement (which is save from SQL injections).
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
215 CREATE OR REPLACE FUNCTION public.setrole_plan(role text) RETURNS void
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
216 AS $$
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
217 BEGIN
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
218 IF role IS NOT NULL AND role <> '' THEN
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
219 EXECUTE format('SET ROLE %I', role);
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
220 END IF;
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
221 END;
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
222 $$
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
223 LANGUAGE plpgsql;