Mercurial > gemma
annotate schema/updates/1307/01.improve_rolename_check.sql @ 5259:680be197844d
Merged branch new-fwa.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 13 May 2020 11:28:34 +0200 |
parents | baabc2b2f094 |
children |
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; |