Mercurial > gemma
annotate schema/updates/1465/01.delete_user.sql @ 5624:29d729607ac1
Added tag v5.7 for changeset ff0af91a7f62
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 22 Dec 2022 18:42:14 +0100 |
parents | 5e3e3d9e2c23 |
children |
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; |