Mercurial > gemma

annotate schema/updates/1465/01.delete_user.sql @ 5499:a30b6c6541e0 deactivate-users

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved logic to delete deactivate users into plpgsql function.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 21 Sep 2021 22:06:43 +0200
parents
children 2ce85b6fcb76
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5499
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 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
3 AS $$
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 DECLARE
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 user_exists bool;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 is_admin bool;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 has_imports bool;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 BEGIN
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 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
10 WHERE rolname = rolename)
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 INTO user_exists;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 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
14 RETURN 0;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15 END IF;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 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
18 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
19 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
20 INTO is_admin;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 -- 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
23 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
24 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
25 RETURN 1;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26 END IF;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 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
29 WHERE username = rolename)
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 INTO has_imports;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32 -- 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
33 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
34 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
35 RETURN 1;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36 END IF;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 -- 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
39 UPDATE users.list_users
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 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
41 ('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
42 WHERE username = rolename;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44 -- Do not allow to login any more.
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
45 EXECUTE format(
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46 'ALTER ROLE %I NOLOGIN', rolename);
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
47
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
48 RETURN 2;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49 END;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50 $$
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51 LANGUAGE plpgsql
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
52 SECURITY DEFINER;
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
53
a30b6c6541e0 Moved logic to delete deactivate users into plpgsql function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
54 GRANT EXECUTE ON FUNCTION delete_user to sys_admin;