Mercurial > gemma

comparison schema/updates/1465/01.delete_user.sql @ 5501:2ce85b6fcb76 deactivate-users

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added missing trigger functions to migration.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 22 Sep 2021 16:55:15 +0200
parents a30b6c6541e0
children 26b7921190fa
comparison
equal deleted inserted replaced
5500:f0c668bc4082 5501:2ce85b6fcb76
1
2 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
3 AS $$
4 BEGIN
5 IF NEW.map_extent IS NULL
6 THEN
7 NEW.map_extent = ST_Extent(CAST(area AS geometry))
8 FROM users.stretches st
9 JOIN users.stretch_countries stc ON stc.stretch_id = st.id
10 WHERE stc.country = NEW.country;
11 END IF;
12
13 IF NEW.username IS NOT NULL
14 -- otherwise let the constraint on user_profiles speak
15 THEN
16 EXECUTE format(
17 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
18 NEW.username,
19 NEW.rolname,
20 internal.check_password(NEW.pw));
21 END IF;
22
23 INSERT INTO internal.user_profiles (
24 username, country, map_extent, email_address,
25 report_reciever, active)
26 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address,
27 NEW.report_reciever, NEW.active);
28
29 IF NEW.active THEN
30 EXECUTE format(
31 'ALTER ROLE %I LOGIN', NEW.username);
32 ELSE
33 EXECUTE format(
34 'ALTER ROLE %I NOLOGIN', NEW.username);
35 END IF;
36
37 -- Do not leak new password
38 NEW.pw = '';
39 RETURN NEW;
40 END;
41 $$
42 LANGUAGE plpgsql
43 SECURITY DEFINER;
44
45 CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger
46 AS $$
47 DECLARE
48 cur_username varchar;
49 BEGIN
50 cur_username = OLD.username;
51
52 IF NEW.username <> cur_username
53 THEN
54 EXECUTE format(
55 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username);
56 cur_username = NEW.username;
57 END IF;
58
59 UPDATE internal.user_profiles p
60 SET (username, country, map_extent, email_address,
61 report_reciever, active)
62 = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address,
63 NEW.report_reciever, NEW.active)
64 WHERE p.username = cur_username;
65
66 IF NEW.rolname <> OLD.rolname
67 THEN
68 EXECUTE format(
69 'REVOKE %I FROM %I', OLD.rolname, cur_username);
70 EXECUTE format(
71 'GRANT %I TO %I', NEW.rolname, cur_username);
72 END IF;
73
74 IF NEW.pw IS NOT NULL AND NEW.pw <> ''
75 THEN
76 EXECUTE format(
77 'ALTER ROLE %I PASSWORD %L',
78 cur_username,
79 internal.check_password(NEW.pw));
80 END IF;
81
82 IF NEW.active <> OLD.active THEN
83 IF NEW.active THEN
84 EXECUTE format(
85 'ALTER ROLE %I LOGIN', cur_username);
86 ELSE
87 EXECUTE format(
88 'ALTER ROLE %I NOLOGIN', cur_username);
89 END IF;
90 END IF;
91
92 -- Do not leak new password
93 NEW.pw = '';
94 RETURN NEW;
95 END;
96 $$
97 LANGUAGE plpgsql
98 SECURITY DEFINER;
99
1 100
2 CREATE OR REPLACE FUNCTION delete_user(rolename text) RETURNS int 101 CREATE OR REPLACE FUNCTION delete_user(rolename text) RETURNS int
3 AS $$ 102 AS $$
4 DECLARE 103 DECLARE
5 user_exists bool; 104 user_exists bool;