Mercurial > gemma

annotate schema/updates/1465/01.delete_user.sql @ 5736:55892008ec96 default tip

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