Mercurial > gemma

annotate schema/manage_users.sql @ 5499:a30b6c6541e0 deactivate-users

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved logic to delete deactivate users into plpgsql function.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 21 Sep 2021 22:06:43 +0200
parents 388947a3050d
children f0c668bc4082
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 (
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
85 username, country, map_extent, email_address)
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
86 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
87
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
88 -- Do not leak new password
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
89 NEW.pw = '';
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
90 RETURN NEW;
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 END;
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 $$
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
93 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
94 SECURITY DEFINER;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
95
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
96 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
97 EXECUTE PROCEDURE internal.create_user();
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
98
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
99 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
100 AS $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
101 BEGIN
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
102 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
103 RETURN NEW;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
104 END;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
105 $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
106 LANGUAGE plpgsql
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
107 SECURITY DEFINER;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
108
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
109 -- 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
110 -- 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
111 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
112 FOR EACH ROW
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
113 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
114
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
115
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 468
diff changeset
116 -- 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
117 -- themselves (affects waterway_admin)
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
118 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
119 AS $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
120 BEGIN
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
121 IF OLD.username <> current_user
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 468
diff changeset
122 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
123 THEN
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
124 RETURN NULL;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
125 ELSE
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
126 RETURN NEW;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
127 END IF;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
128 END;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
129 $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
130 LANGUAGE plpgsql;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
131
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
132 -- 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
133 -- 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
134 -- 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
135 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
136 FOR EACH ROW
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
137 EXECUTE PROCEDURE internal.authorize_update_user();
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
138
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
139
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
140 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
141 AS $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
142 DECLARE
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
143 cur_username varchar;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
144 BEGIN
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
145 cur_username = OLD.username;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
146
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
147 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
148 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
149 EXECUTE format(
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
150 '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
151 cur_username = NEW.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
4723
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
154 UPDATE internal.user_profiles p
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
155 SET (username, country, map_extent, email_address)
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
156 = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address)
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
157 WHERE p.username = cur_username;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
158
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
159 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
160 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
161 EXECUTE format(
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
162 'REVOKE %I FROM %I', OLD.rolname, cur_username);
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
163 EXECUTE format(
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
164 '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
165 END IF;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
166
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
167 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
168 THEN
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
169 EXECUTE format(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
170 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
171 cur_username,
312
0745b4d336c4 Place functions in more matching schemas
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
172 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
173 END IF;
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
174
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
175 -- Do not leak new password
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
176 NEW.pw = '';
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
177 RETURN NEW;
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
178 END;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
179 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
180 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
181 SECURITY DEFINER;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
182
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
183 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
184 EXECUTE PROCEDURE internal.update_user();
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 269
diff changeset
185
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
186
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
187 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
188 AS $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
189 DECLARE
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
190 bid int;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
191 BEGIN
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
192 -- 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
193 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
194 LOOP
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
195 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
196 END LOOP;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
197 -- 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
198 -- 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
199 -- 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
200
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
201 -- Delete user
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
202 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
203 DELETE FROM internal.user_profiles p
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
204 WHERE p.username = OLD.username;
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
205
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
206 RETURN OLD;
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
207 END;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
208 $$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
209 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
210 SECURITY DEFINER;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 312
diff changeset
211
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
212 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
213 EXECUTE PROCEDURE internal.delete_user();
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
214
5499
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
215 --
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
216 -- delete_user deletes a user if she/he is not an admin or an admin w/o imports.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
217 -- It deactivates admins w/ imports.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
218 -- Returns 0 if the role does not exsist, 1 if the role was deleted, 2 if it was
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
219 -- deactivated.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
220 --
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
221 CREATE OR REPLACE FUNCTION delete_user(rolename text) RETURNS int
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
222 AS $$
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
223 DECLARE
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
224 user_exists bool;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
225 is_admin bool;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
226 has_imports bool;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
227 BEGIN
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
228 SELECT EXISTS (SELECT 1 FROM pg_roles
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
229 WHERE rolname = rolename)
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
230 INTO user_exists;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
231
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
232 IF NOT user_exists THEN
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
233 RETURN 0;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
234 END IF;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
235
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
236 SELECT EXISTS (SELECT 1 FROM pg_roles
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
237 WHERE pg_has_role(rolename, oid, 'member') AND
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
238 rolname IN ('waterway_admin', 'sys_admin'))
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
239 INTO is_admin;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
240
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
241 -- None admins can be deleted.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
242 IF NOT is_admin THEN
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
243 DELETE FROM users.list_users WHERE username = rolename;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
244 RETURN 1;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
245 END IF;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
246
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
247 SELECT EXISTS (SELECT 1 FROM import.imports
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
248 WHERE username = rolename)
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
249 INTO has_imports;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
250
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
251 -- Admins w/o imports can be deleted.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
252 IF NOT has_imports THEN
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
253 DELETE FROM users.list_users WHERE username = rolename;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
254 RETURN 1;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
255 END IF;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
256
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
257 -- Admins w/ imports have to be deactivated.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
258 UPDATE users.list_users
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
259 SET (email_address, report_reciever, active) =
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
260 ('nomail@example.com', false, false)
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
261 WHERE username = rolename;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
262
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
263 -- Do not allow to login any more.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
264 EXECUTE format(
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
265 'ALTER ROLE %I NOLOGIN', rolename);
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
266
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
267 RETURN 2;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
268 END;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
269 $$
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
270 LANGUAGE plpgsql
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
271 SECURITY DEFINER;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
272
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5014
diff changeset
273
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 312
diff changeset
274
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
275 -- 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
276 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
277 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
278 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
279 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
280 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
281 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
282 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
283 $$
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
284 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
285
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
286 -- 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
287 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
288 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
289 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
290 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
291 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
292 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
293 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
294 $$
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
295 LANGUAGE plpgsql;