Mercurial > gemma

annotate schema/updates/1307/01.improve_rolename_check.sql @ 5267:aca4bf7af270

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: remove mapState from import statement
author Fadi Abbud <fadi.abbud@intevation.de>
date Wed, 10 Jun 2020 16:33:10 +0200
parents baabc2b2f094
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4723
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 ALTER TABLE internal.user_profiles
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 ADD CHECK(to_regrole(quote_ident(username)) IS NOT NULL);
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 AS $$
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 BEGIN
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 IF NEW.map_extent IS NULL
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 THEN
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 NEW.map_extent = ST_Extent(CAST(area AS geometry))
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 FROM users.stretches st
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 JOIN users.stretch_countries stc ON stc.stretch_id = st.id
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 WHERE stc.country = NEW.country;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 END IF;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 IF NEW.username IS NOT NULL
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 -- otherwise let the constraint on user_profiles speak
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 THEN
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 EXECUTE format(
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 NEW.username,
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 NEW.rolname,
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 internal.check_password(NEW.pw));
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 END IF;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 INSERT INTO internal.user_profiles (
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 username, country, map_extent, email_address)
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 -- Do not leak new password
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 NEW.pw = '';
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 RETURN NEW;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 END;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 $$
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 LANGUAGE plpgsql
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 SECURITY DEFINER;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 AS $$
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 DECLARE
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 cur_username varchar;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 BEGIN
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 cur_username = OLD.username;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 IF NEW.username <> cur_username
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 THEN
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 EXECUTE format(
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username);
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 cur_username = NEW.username;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 END IF;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 UPDATE internal.user_profiles p
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 SET (username, country, map_extent, email_address)
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address)
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 WHERE p.username = cur_username;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 IF NEW.rolname <> OLD.rolname
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 THEN
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 EXECUTE format(
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 'REVOKE %I FROM %I', OLD.rolname, cur_username);
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 EXECUTE format(
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 'GRANT %I TO %I', NEW.rolname, cur_username);
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 END IF;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 IF NEW.pw IS NOT NULL AND NEW.pw <> ''
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 THEN
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 EXECUTE format(
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 'ALTER ROLE %I PASSWORD %L',
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 cur_username,
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 internal.check_password(NEW.pw));
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 END IF;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 -- Do not leak new password
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 NEW.pw = '';
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 RETURN NEW;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 END;
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 $$
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 LANGUAGE plpgsql
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 SECURITY DEFINER;