Mercurial > gemma

comparison schema/manage_users.sql @ 268:72062ca52746

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Make user_profiles table invisible for users users.list_users should be the single point to access user profile data. Keeping user_profiles visible would imply having to maintain RLS policies that are otherwise obsolete. Tests run as superuser still use user_profiles, because list_users does not show any data to a superuser.
author Tom Gottfried <tom@intevation.de>
date Mon, 30 Jul 2018 11:38:09 +0200
parents 13ad969a9138
children 0b2d9f96ddb8
comparison
equal deleted inserted replaced
267:7f030ec3472d 268:72062ca52746
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
41 SELECT rolname 42 SELECT rolname
42 FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid 43 FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid
43 WHERE member = ( 44 WHERE member = (
44 SELECT oid FROM pg_roles WHERE rolname = current_user)) 45 SELECT oid FROM pg_roles WHERE rolname = current_user))
45 SELECT r.rolname, p.* 46 SELECT r.rolname, p.*
46 FROM cur, users.user_profiles p 47 FROM cur, internal.user_profiles p
47 JOIN pg_roles u ON p.username = u.rolname 48 JOIN pg_roles u ON p.username = u.rolname
48 JOIN pg_auth_members a ON u.oid = a.member 49 JOIN pg_auth_members a ON u.oid = a.member
49 JOIN pg_roles r ON a.roleid = r.oid 50 JOIN pg_roles r ON a.roleid = r.oid
50 WHERE p.username = current_user 51 WHERE p.username = current_user
51 OR cur.rolname = 'waterway_admin' 52 OR cur.rolname = 'waterway_admin'
53 OR cur.rolname = 'sys_admin'; 54 OR cur.rolname = 'sys_admin';
54 55
55 56
56 CREATE OR REPLACE FUNCTION sys_admin.create_user( 57 CREATE OR REPLACE FUNCTION sys_admin.create_user(
57 userrole varchar, 58 userrole varchar,
58 username users.user_profiles.username%TYPE, 59 username internal.user_profiles.username%TYPE,
59 pw varchar, 60 pw varchar,
60 country users.user_profiles.country%TYPE, 61 country internal.user_profiles.country%TYPE,
61 map_extent users.user_profiles.map_extent%TYPE, 62 map_extent internal.user_profiles.map_extent%TYPE,
62 email_address users.user_profiles.email_address%TYPE 63 email_address internal.user_profiles.email_address%TYPE
63 ) 64 )
64 RETURNS void 65 RETURNS void
65 AS $$ 66 AS $$
66 BEGIN 67 BEGIN
67 IF map_extent IS NULL 68 IF map_extent IS NULL
68 THEN 69 THEN
69 map_extent = ST_Extent(area) FROM users.responsibility_areas ra 70 map_extent = ST_Extent(area) FROM users.responsibility_areas ra
70 WHERE ra.country = create_user.country; 71 WHERE ra.country = create_user.country;
71 END IF; 72 END IF;
72 INSERT INTO users.user_profiles VALUES ( 73 INSERT INTO internal.user_profiles (
73 username, country, map_extent, email_address); 74 username, country, map_extent, email_address)
75 VALUES (username, country, map_extent, email_address);
74 EXECUTE format( 76 EXECUTE format(
75 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', 77 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
76 username, 78 username,
77 userrole, 79 userrole,
78 users.check_password(pw)); 80 users.check_password(pw));
81 LANGUAGE plpgsql 83 LANGUAGE plpgsql
82 SECURITY DEFINER; 84 SECURITY DEFINER;
83 85
84 86
85 CREATE OR REPLACE FUNCTION sys_admin.update_user( 87 CREATE OR REPLACE FUNCTION sys_admin.update_user(
86 username users.user_profiles.username%TYPE, 88 username internal.user_profiles.username%TYPE,
87 new_userrole varchar, 89 new_userrole varchar,
88 new_username users.user_profiles.username%TYPE, 90 new_username internal.user_profiles.username%TYPE,
89 new_pw varchar, 91 new_pw varchar,
90 new_country users.user_profiles.country%TYPE, 92 new_country internal.user_profiles.country%TYPE,
91 new_map_extent users.user_profiles.map_extent%TYPE, 93 new_map_extent internal.user_profiles.map_extent%TYPE,
92 new_email_address users.user_profiles.email_address%TYPE 94 new_email_address internal.user_profiles.email_address%TYPE
93 ) 95 )
94 RETURNS void 96 RETURNS void
95 AS $$ 97 AS $$
96 DECLARE 98 DECLARE
97 cur_username name; 99 cur_username name;
98 cur_userrole name; 100 cur_userrole name;
99 BEGIN 101 BEGIN
100 cur_username = username; 102 cur_username = username;
101 103
102 UPDATE users.user_profiles p 104 UPDATE internal.user_profiles p
103 SET (username, country, map_extent, email_address) 105 SET (username, country, map_extent, email_address)
104 = (new_username, new_country, new_map_extent, new_email_address) 106 = (new_username, new_country, new_map_extent, new_email_address)
105 WHERE p.username = cur_username; 107 WHERE p.username = cur_username;
106 108
107 IF new_username <> cur_username 109 IF new_username <> cur_username
137 LANGUAGE plpgsql 139 LANGUAGE plpgsql
138 SECURITY DEFINER; 140 SECURITY DEFINER;
139 141
140 142
141 CREATE OR REPLACE FUNCTION sys_admin.delete_user( 143 CREATE OR REPLACE FUNCTION sys_admin.delete_user(
142 username users.user_profiles.username%TYPE 144 username internal.user_profiles.username%TYPE
143 ) 145 )
144 RETURNS void 146 RETURNS void
145 AS $$ 147 AS $$
146 DECLARE 148 DECLARE
147 bid int; 149 bid int;
154 -- in which this function is executed will not be terminated but lost 156 -- in which this function is executed will not be terminated but lost
155 -- without any privileges after commiting this transaction 157 -- without any privileges after commiting this transaction
156 158
157 -- Delete user 159 -- Delete user
158 EXECUTE format('DROP ROLE %I', username); 160 EXECUTE format('DROP ROLE %I', username);
159 DELETE FROM users.user_profiles p WHERE p.username = delete_user.username; 161 DELETE FROM internal.user_profiles p
162 WHERE p.username = delete_user.username;
160 END; 163 END;
161 $$ 164 $$
162 LANGUAGE plpgsql 165 LANGUAGE plpgsql
163 SECURITY DEFINER; 166 SECURITY DEFINER;