Mercurial > gemma
annotate schema/updates/1450/01.report_reciever.sql @ 5400:983d6efc04e9
Added report-templates to release tar.
author | wilde@azure1.rgb.intevation.de |
---|---|
date | Wed, 07 Jul 2021 18:41:53 +0200 |
parents | 5b2faff9ec81 |
children |
rev | line source |
---|---|
5323
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
1 ALTER TABLE internal.user_profiles |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
2 ADD COLUMN report_reciever boolean NOT NULL DEFAULT false; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
3 |
5331
5b2faff9ec81
Create list_users view in the users schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5323
diff
changeset
|
4 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS |
5323
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
5 SELECT |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
6 r.rolname, |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
7 p.username, |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
8 CAST('' AS varchar) AS pw, |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
9 p.country, |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
10 p.map_extent, |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
11 p.email_address, |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
12 p.report_reciever |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
13 FROM internal.user_profiles p |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
14 JOIN pg_roles u ON p.username = u.rolname |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
15 JOIN pg_auth_members a ON u.oid = a.member |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
16 JOIN pg_roles r ON a.roleid = r.oid |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
17 WHERE p.username = current_user |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
18 OR pg_has_role('waterway_admin', 'MEMBER') |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
19 AND p.country = ( |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
20 SELECT country FROM internal.user_profiles |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
21 WHERE username = current_user) |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
22 AND r.rolname <> 'sys_admin' |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
23 OR pg_has_role('sys_admin', 'MEMBER') |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
24 ; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
25 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
26 CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
27 AS $$ |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
28 DECLARE |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
29 cur_username varchar; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
30 BEGIN |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
31 cur_username = OLD.username; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
32 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
33 IF NEW.username <> cur_username |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
34 THEN |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
35 EXECUTE format( |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
36 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username); |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
37 cur_username = NEW.username; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
38 END IF; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
39 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
40 UPDATE internal.user_profiles p |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
41 SET (username, country, map_extent, email_address, report_reciever) |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
42 = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, NEW.report_reciever) |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
43 WHERE p.username = cur_username; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
44 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
45 IF NEW.rolname <> OLD.rolname |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
46 THEN |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
47 EXECUTE format( |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
48 'REVOKE %I FROM %I', OLD.rolname, cur_username); |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
49 EXECUTE format( |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
50 'GRANT %I TO %I', NEW.rolname, cur_username); |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
51 END IF; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
52 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
53 IF NEW.pw IS NOT NULL AND NEW.pw <> '' |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
54 THEN |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
55 EXECUTE format( |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
56 'ALTER ROLE %I PASSWORD %L', |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
57 cur_username, |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
58 internal.check_password(NEW.pw)); |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
59 END IF; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
60 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
61 -- Do not leak new password |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
62 NEW.pw = ''; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
63 RETURN NEW; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
64 END; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
65 $$ |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
66 LANGUAGE plpgsql |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
67 SECURITY DEFINER; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
68 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
69 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
70 AS $$ |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
71 BEGIN |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
72 IF NEW.map_extent IS NULL |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
73 THEN |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
74 NEW.map_extent = ST_Extent(CAST(area AS geometry)) |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
75 FROM users.stretches st |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
76 JOIN users.stretch_countries stc ON stc.stretch_id = st.id |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
77 WHERE stc.country = NEW.country; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
78 END IF; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
79 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
80 IF NEW.username IS NOT NULL |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
81 -- otherwise let the constraint on user_profiles speak |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
82 THEN |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
83 EXECUTE format( |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
84 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
85 NEW.username, |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
86 NEW.rolname, |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
87 internal.check_password(NEW.pw)); |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
88 END IF; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
89 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
90 INSERT INTO internal.user_profiles ( |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
91 username, country, map_extent, email_address, report_reciever) |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
92 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, NEW.report_reciever); |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
93 |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
94 -- Do not leak new password |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
95 NEW.pw = ''; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
96 RETURN NEW; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
97 END; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
98 $$ |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
99 LANGUAGE plpgsql |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
100 SECURITY DEFINER; |
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
101 |