Mercurial > gemma

annotate schema/manage_users.sql @ 2455:54c9fe587fe6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.
author Tom Gottfried <tom@intevation.de>
date Fri, 01 Mar 2019 18:38:02 +0100
parents 5bb1e3c1f364
children 93fa55bce126
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
6
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
7 -- Copyright (C) 2018 by via donau
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Tom Gottfried <tom@intevation.de>
1336
f65d1767452c add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1301
diff changeset
13 -- * Sacha Teichmann <sascha.teichmann@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 478
diff changeset
14
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 --
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 -- 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
17 -- 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
18 --
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19
312
0745b4d336c4 Place functions in more matching schemas
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
20 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
21 pw varchar
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
22 )
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
23 RETURNS varchar
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
24 AS $$
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
25 DECLARE
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
26 min_len CONSTANT int = 8;
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
27 BEGIN
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
28 IF char_length(pw) < min_len
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
29 OR pw NOT SIMILAR TO '%[^[:alnum:]]%'
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
30 OR pw NOT SIMILAR TO '%[[:digit:]]%'
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
31 THEN
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
32 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
33 ELSE
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
34 RETURN pw;
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
35 END IF;
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
36 END;
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
37 $$
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
38 LANGUAGE plpgsql;
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
39
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
40
263
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
41 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
42 SELECT
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
43 r.rolname,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
44 p.username,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
45 CAST('' AS varchar) AS pw,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
46 p.country,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
47 p.map_extent,
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
48 p.email_address
269
0b2d9f96ddb8 Replace CTE with access privilege inquiry function call.
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
49 FROM internal.user_profiles p
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
50 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
51 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
52 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
53 WHERE p.username = current_user
269
0b2d9f96ddb8 Replace CTE with access privilege inquiry function call.
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
54 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
55 AND p.country = (
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
56 SELECT country FROM internal.user_profiles
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
57 WHERE username = current_user)
269
0b2d9f96ddb8 Replace CTE with access privilege inquiry function call.
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
58 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
59
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 233
diff changeset
60
410
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
61 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
62 RETURNS internal.user_profiles.country%TYPE
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
63 AS $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
64 SELECT country FROM users.list_users
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
65 WHERE username = current_user
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
66 $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
67 LANGUAGE SQL
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
68 STABLE PARALLEL SAFE;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
69
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
70
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
71 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
72 AS $$
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 BEGIN
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
74 IF NEW.map_extent IS NULL
212
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
75 THEN
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
76 NEW.map_extent = ST_Extent(CAST(area AS geometry))
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
77 FROM users.responsibility_areas ra
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
78 WHERE ra.country = NEW.country;
212
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
79 END IF;
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
80 INSERT INTO internal.user_profiles (
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
81 username, country, map_extent, email_address)
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
82 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
83 EXECUTE format(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
84 '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
85 NEW.username,
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
86 NEW.rolname,
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
87 internal.check_password(NEW.pw));
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
88
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
89 -- Do not leak new password
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
90 NEW.pw = '';
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
91 RETURN NEW;
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 END;
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 $$
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
94 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
95 SECURITY DEFINER;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
96
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
97 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
98 EXECUTE PROCEDURE internal.create_user();
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
99
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
100 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
101 AS $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
102 BEGIN
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
103 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
104 RETURN NEW;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
105 END;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
106 $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
107 LANGUAGE plpgsql
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
108 SECURITY DEFINER;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
109
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
110 -- 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
111 -- 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
112 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
113 FOR EACH ROW
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
114 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
115
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
116
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 468
diff changeset
117 -- 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
118 -- themselves (affects waterway_admin)
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
119 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
120 AS $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
121 BEGIN
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
122 IF OLD.username <> current_user
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 468
diff changeset
123 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
124 THEN
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
125 RETURN NULL;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
126 ELSE
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
127 RETURN NEW;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
128 END IF;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
129 END;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
130 $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
131 LANGUAGE plpgsql;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
132
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
133 -- 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
134 -- 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
135 -- 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
136 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
137 FOR EACH ROW
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
138 EXECUTE PROCEDURE internal.authorize_update_user();
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
139
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
140
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
141 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
142 AS $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
143 DECLARE
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
144 cur_username varchar;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
145 BEGIN
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
146 cur_username = OLD.username;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
147
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
148 UPDATE internal.user_profiles p
233
531d1f8a2b4b Fix spelling
Tom Gottfried <tom@intevation.de>
parents: 232
diff changeset
149 SET (username, country, map_extent, email_address)
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
150 = (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
151 WHERE p.username = cur_username;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
152
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
153 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
154 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
155 EXECUTE format(
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
156 '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
157 cur_username = NEW.username;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
158 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
159
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
160 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
161 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
162 EXECUTE format(
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
163 'REVOKE %I FROM %I', OLD.rolname, cur_username);
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
164 EXECUTE format(
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
165 '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
166 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
167
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
168 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
169 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
170 EXECUTE format(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
171 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
172 cur_username,
312
0745b4d336c4 Place functions in more matching schemas
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
173 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
174 END IF;
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
175
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
176 -- Do not leak new password
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
177 NEW.pw = '';
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
178 RETURN NEW;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
179 END;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
180 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
181 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
182 SECURITY DEFINER;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
183
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
184 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
185 EXECUTE PROCEDURE internal.update_user();
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
186
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
187
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
188 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
189 AS $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
190 DECLARE
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
191 bid int;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
192 BEGIN
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
193 -- 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
194 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
195 LOOP
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
196 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
197 END LOOP;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
198 -- 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
199 -- 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
200 -- 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
201
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
202 -- Delete user
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
203 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
204 DELETE FROM internal.user_profiles p
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
205 WHERE p.username = OLD.username;
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
206
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
207 RETURN OLD;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
208 END;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
209 $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
210 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
211 SECURITY DEFINER;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 312
diff changeset
212
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
213 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
214 EXECUTE PROCEDURE internal.delete_user();
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
215
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 312
diff changeset
216
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
217 -- 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
218 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
219 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
220 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
221 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
222 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
223 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
224 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
225 $$
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
226 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
227
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
228 -- 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
229 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
230 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
231 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
232 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
233 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
234 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
235 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
236 $$
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
237 LANGUAGE plpgsql;