Mercurial > gemma

annotate schema/updates/1465/01.delete_user.sql @ 5502:26b7921190fa deactivate-users

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Login rights are now tracked by triggers.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 22 Sep 2021 16:57:45 +0200
parents 2ce85b6fcb76
children 076b6b17c4a9
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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;
2ce85b6fcb76 Added missing trigger functions to migration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5499
diff changeset
99
5499
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
100
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
101 CREATE OR REPLACE FUNCTION delete_user(rolename text) RETURNS int
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
102 AS $$
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
103 DECLARE
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
104 user_exists bool;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
105 is_admin bool;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
106 has_imports bool;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
107 BEGIN
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
108 SELECT EXISTS (SELECT 1 FROM pg_roles
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
109 WHERE rolname = rolename)
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
110 INTO user_exists;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
111
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
112 IF NOT user_exists THEN
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
113 RETURN 0;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
114 END IF;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
115
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
116 SELECT EXISTS (SELECT 1 FROM pg_roles
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
117 WHERE pg_has_role(rolename, oid, 'member') AND
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
118 rolname IN ('waterway_admin', 'sys_admin'))
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
119 INTO is_admin;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
120
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
121 -- None admins can be deleted.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
122 IF NOT is_admin THEN
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
123 DELETE FROM users.list_users WHERE username = rolename;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
124 RETURN 1;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
125 END IF;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
126
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
127 SELECT EXISTS (SELECT 1 FROM import.imports
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
128 WHERE username = rolename)
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
129 INTO has_imports;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
130
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
131 -- Admins w/o imports can be deleted.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
132 IF NOT has_imports THEN
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
133 DELETE FROM users.list_users WHERE username = rolename;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
134 RETURN 1;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
135 END IF;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
136
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
137 -- Admins w/ imports have to be deactivated.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
138 UPDATE users.list_users
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
139 SET (email_address, report_reciever, active) =
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
140 ('nomail@example.com', false, false)
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
141 WHERE username = rolename;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
142
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
143 RETURN 2;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
144 END;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
145 $$
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
146 LANGUAGE plpgsql
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
147 SECURITY DEFINER;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
148
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
149 GRANT EXECUTE ON FUNCTION delete_user to sys_admin;