Mercurial > gemma

annotate schema/manage_users.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents 5e3e3d9e2c23
children
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
2912
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
7 -- Copyright (C) 2018, 2019 by via donau
1298
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
2912
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
41 CREATE OR REPLACE FUNCTION users.current_user_area_utm()
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
42 RETURNS geometry
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
43 AS $$
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
44 DECLARE utm_area geometry;
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
45 BEGIN
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
46 SELECT ST_Union(ST_Transform(area::geometry, z))
2912
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
47 INTO STRICT utm_area
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
48 FROM (SELECT area,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
49 best_utm(ST_Collect(area::geometry) OVER ()) AS z
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
50 FROM users.stretches st
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
51 JOIN users.stretch_countries stc
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
52 ON stc.stretch_id = st.id
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
53 WHERE country = (SELECT country
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
54 FROM users.list_users
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
55 WHERE username = current_user)) AS st;
4158
5466562cca60 Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents: 2915
diff changeset
56 RETURN utm_area;
2912
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
57 END;
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
58 $$
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
59 LANGUAGE plpgsql
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
60 STABLE PARALLEL SAFE;
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
61
93fa55bce126 Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents: 1338
diff changeset
62
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
63 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
64 AS $$
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 BEGIN
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
66 IF NEW.map_extent IS NULL
212
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
67 THEN
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
68 NEW.map_extent = ST_Extent(CAST(area AS geometry))
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
69 FROM users.stretches st
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
70 JOIN users.stretch_countries stc ON stc.stretch_id = st.id
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4158
diff changeset
71 WHERE stc.country = NEW.country;
212
229f385448fa Make map extent mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
72 END IF;
4723
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
73
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
74 IF NEW.username IS NOT NULL
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
75 -- otherwise let the constraint on user_profiles speak
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
76 THEN
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
77 EXECUTE format(
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
78 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
79 NEW.username,
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
80 NEW.rolname,
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
81 internal.check_password(NEW.pw));
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
82 END IF;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
83
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
84 INSERT INTO internal.user_profiles (
5500
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
85 username, country, map_extent, email_address,
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
86 report_reciever, active)
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
87 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address,
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
88 NEW.report_reciever, NEW.active);
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
89
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
90 IF NEW.active THEN
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
91 EXECUTE format(
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
92 'ALTER ROLE %I LOGIN', NEW.username);
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
93 ELSE
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
94 EXECUTE format(
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
95 'ALTER ROLE %I NOLOGIN', NEW.username);
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
96 END IF;
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
97
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
98 -- Do not leak new password
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
99 NEW.pw = '';
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
100 RETURN NEW;
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 END;
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 $$
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
103 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
104 SECURITY DEFINER;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
105
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
106 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
107 EXECUTE PROCEDURE internal.create_user();
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
108
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
109 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
110 AS $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
111 BEGIN
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
112 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
113 RETURN NEW;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
114 END;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
115 $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
116 LANGUAGE plpgsql
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
117 SECURITY DEFINER;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
118
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
119 -- 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
120 -- 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
121 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
122 FOR EACH ROW
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
123 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
124
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
125
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 468
diff changeset
126 -- 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
127 -- themselves (affects waterway_admin)
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
128 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
129 AS $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
130 BEGIN
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
131 IF OLD.username <> current_user
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 468
diff changeset
132 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
133 THEN
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
134 RETURN NULL;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
135 ELSE
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
136 RETURN NEW;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
137 END IF;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
138 END;
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 LANGUAGE plpgsql;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
141
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
142 -- 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
143 -- 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
144 -- 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
145 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
146 FOR EACH ROW
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
147 EXECUTE PROCEDURE internal.authorize_update_user();
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
148
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
149
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
150 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
151 AS $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
152 DECLARE
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
153 cur_username varchar;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
154 BEGIN
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
155 cur_username = OLD.username;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
156
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
157 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
158 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
159 EXECUTE format(
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
160 '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
161 cur_username = NEW.username;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
162 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
163
4723
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
164 UPDATE internal.user_profiles p
5500
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
165 SET (username, country, map_extent, email_address,
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
166 report_reciever, active)
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
167 = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address,
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
168 NEW.report_reciever, NEW.active)
4723
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
169 WHERE p.username = cur_username;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
170
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
171 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
172 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
173 EXECUTE format(
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
174 'REVOKE %I FROM %I', OLD.rolname, cur_username);
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
175 EXECUTE format(
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
176 '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
177 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
178
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
179 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
180 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
181 EXECUTE format(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
182 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
183 cur_username,
312
0745b4d336c4 Place functions in more matching schemas
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
184 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
185 END IF;
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
186
5500
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
187 IF NEW.active <> OLD.active THEN
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
188 IF NEW.active THEN
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
189 EXECUTE format(
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
190 'ALTER ROLE %I LOGIN', cur_username);
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
191 ELSE
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
192 EXECUTE format(
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
193 'ALTER ROLE %I NOLOGIN', cur_username);
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
194 END IF;
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
195 END IF;
f0c668bc4082 Moved active login/nologin to triggers. Allow /user PATCH endpoint to modify the field.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
196
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
197 -- Do not leak new password
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
198 NEW.pw = '';
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
199 RETURN NEW;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
200 END;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
201 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
202 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
203 SECURITY DEFINER;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
204
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
205 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
206 EXECUTE PROCEDURE internal.update_user();
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
207
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
208
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
209 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
210 AS $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
211 DECLARE
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
212 bid int;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
213 BEGIN
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
214 -- 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
215 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
216 LOOP
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
217 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
218 END LOOP;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
219 -- 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
220 -- 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
221 -- 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
222
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
223 -- Delete user
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
224 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
225 DELETE FROM internal.user_profiles p
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
226 WHERE p.username = OLD.username;
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
227
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
228 RETURN OLD;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
229 END;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
230 $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
231 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
232 SECURITY DEFINER;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 312
diff changeset
233
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
234 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
235 EXECUTE PROCEDURE internal.delete_user();
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
236
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
237 -- 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
238 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
239 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
240 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
241 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
242 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
243 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
244 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
245 $$
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
246 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
247
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
248 -- 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
249 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
250 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
251 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
252 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
253 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
254 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
255 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
256 $$
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
257 LANGUAGE plpgsql;