Mercurial > gemma

comparison schema/manage_users.sql @ 271:02aaff4b4a66

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 30 Jul 2018 12:32:11 +0200
parents 0b2d9f96ddb8
children 750a9c9cd965
comparison
equal deleted inserted replaced
270:d1b0d964af09 271:02aaff4b4a66
25 25
26 26
27 -- Security-definer function to get current users country, which allows to 27 -- Security-definer function to get current users country, which allows to
28 -- restrict the view on user_profiles by country without infinite recursion 28 -- restrict the view on user_profiles by country without infinite recursion
29 CREATE FUNCTION current_user_country() 29 CREATE FUNCTION current_user_country()
30 RETURNS users.user_profiles.country%TYPE 30 RETURNS internal.user_profiles.country%TYPE
31 AS $$ 31 AS $$
32 SELECT country FROM users.user_profiles WHERE username = session_user 32 SELECT country FROM internal.user_profiles
33 WHERE username = session_user
33 $$ 34 $$
34 LANGUAGE SQL 35 LANGUAGE SQL
35 SECURITY DEFINER 36 SECURITY DEFINER
36 STABLE PARALLEL SAFE; 37 STABLE PARALLEL SAFE;
37 38
38 39
39 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS 40 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
40 WITH cur AS (
41 SELECT rolname
42 FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid
43 WHERE member = (
44 SELECT oid FROM pg_roles WHERE rolname = current_user))
45 SELECT r.rolname, p.* 41 SELECT r.rolname, p.*
46 FROM cur, users.user_profiles p 42 FROM internal.user_profiles p
47 JOIN pg_roles u ON p.username = u.rolname 43 JOIN pg_roles u ON p.username = u.rolname
48 JOIN pg_auth_members a ON u.oid = a.member 44 JOIN pg_auth_members a ON u.oid = a.member
49 JOIN pg_roles r ON a.roleid = r.oid 45 JOIN pg_roles r ON a.roleid = r.oid
50 WHERE p.username = current_user 46 WHERE p.username = current_user
51 OR cur.rolname = 'waterway_admin' 47 OR pg_has_role('waterway_admin', 'MEMBER')
52 AND p.country = current_user_country() 48 AND p.country = current_user_country()
53 OR cur.rolname = 'sys_admin'; 49 OR pg_has_role('sys_admin', 'MEMBER');
54 50
55 51
56 CREATE OR REPLACE FUNCTION sys_admin.create_user( 52 CREATE OR REPLACE FUNCTION sys_admin.create_user(
57 userrole varchar, 53 userrole varchar,
58 username users.user_profiles.username%TYPE, 54 username internal.user_profiles.username%TYPE,
59 pw varchar, 55 pw varchar,
60 country users.user_profiles.country%TYPE, 56 country internal.user_profiles.country%TYPE,
61 map_extent users.user_profiles.map_extent%TYPE, 57 map_extent internal.user_profiles.map_extent%TYPE,
62 email_address users.user_profiles.email_address%TYPE 58 email_address internal.user_profiles.email_address%TYPE
63 ) 59 )
64 RETURNS void 60 RETURNS void
65 AS $$ 61 AS $$
66 BEGIN 62 BEGIN
67 IF map_extent IS NULL 63 IF map_extent IS NULL
68 THEN 64 THEN
69 map_extent = ST_Extent(area) FROM users.responsibility_areas ra 65 map_extent = ST_Extent(area) FROM users.responsibility_areas ra
70 WHERE ra.country = create_user.country; 66 WHERE ra.country = create_user.country;
71 END IF; 67 END IF;
72 INSERT INTO users.user_profiles VALUES ( 68 INSERT INTO internal.user_profiles (
73 username, country, map_extent, email_address); 69 username, country, map_extent, email_address)
70 VALUES (username, country, map_extent, email_address);
74 EXECUTE format( 71 EXECUTE format(
75 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', 72 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
76 username, 73 username,
77 userrole, 74 userrole,
78 users.check_password(pw)); 75 users.check_password(pw));
81 LANGUAGE plpgsql 78 LANGUAGE plpgsql
82 SECURITY DEFINER; 79 SECURITY DEFINER;
83 80
84 81
85 CREATE OR REPLACE FUNCTION sys_admin.update_user( 82 CREATE OR REPLACE FUNCTION sys_admin.update_user(
86 username users.user_profiles.username%TYPE, 83 username internal.user_profiles.username%TYPE,
87 new_userrole varchar, 84 new_userrole varchar,
88 new_username users.user_profiles.username%TYPE, 85 new_username internal.user_profiles.username%TYPE,
89 new_pw varchar, 86 new_pw varchar,
90 new_country users.user_profiles.country%TYPE, 87 new_country internal.user_profiles.country%TYPE,
91 new_map_extent users.user_profiles.map_extent%TYPE, 88 new_map_extent internal.user_profiles.map_extent%TYPE,
92 new_email_address users.user_profiles.email_address%TYPE 89 new_email_address internal.user_profiles.email_address%TYPE
93 ) 90 )
94 RETURNS void 91 RETURNS void
95 AS $$ 92 AS $$
96 DECLARE 93 DECLARE
97 cur_username name; 94 cur_username name;
98 cur_userrole name; 95 cur_userrole name;
99 BEGIN 96 BEGIN
100 cur_username = username; 97 cur_username = username;
101 98
102 UPDATE users.user_profiles p 99 UPDATE internal.user_profiles p
103 SET (username, country, map_extent, email_address) 100 SET (username, country, map_extent, email_address)
104 = (new_username, new_country, new_map_extent, new_email_address) 101 = (new_username, new_country, new_map_extent, new_email_address)
105 WHERE p.username = cur_username; 102 WHERE p.username = cur_username;
106 103
107 IF new_username <> cur_username 104 IF new_username <> cur_username
137 LANGUAGE plpgsql 134 LANGUAGE plpgsql
138 SECURITY DEFINER; 135 SECURITY DEFINER;
139 136
140 137
141 CREATE OR REPLACE FUNCTION sys_admin.delete_user( 138 CREATE OR REPLACE FUNCTION sys_admin.delete_user(
142 username users.user_profiles.username%TYPE 139 username internal.user_profiles.username%TYPE
143 ) 140 )
144 RETURNS void 141 RETURNS void
145 AS $$ 142 AS $$
146 DECLARE 143 DECLARE
147 bid int; 144 bid int;
154 -- in which this function is executed will not be terminated but lost 151 -- in which this function is executed will not be terminated but lost
155 -- without any privileges after commiting this transaction 152 -- without any privileges after commiting this transaction
156 153
157 -- Delete user 154 -- Delete user
158 EXECUTE format('DROP ROLE %I', username); 155 EXECUTE format('DROP ROLE %I', username);
159 DELETE FROM users.user_profiles p WHERE p.username = delete_user.username; 156 DELETE FROM internal.user_profiles p
157 WHERE p.username = delete_user.username;
160 END; 158 END;
161 $$ 159 $$
162 LANGUAGE plpgsql 160 LANGUAGE plpgsql
163 SECURITY DEFINER; 161 SECURITY DEFINER;