Mercurial > gemma

annotate schema/updates/1450/01.report_reciever.sql @ 5361:ce1fe22bda5a extented-report

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Backed out changeset f845c3b7b68e
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 22 Jun 2021 17:12:17 +0200
parents 5b2faff9ec81
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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