Mercurial > gemma

comparison schema/manage_users.sql @ 478:3af7ca761f6a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Purge password reset role The risk of SQL-injections and thus privilege escalation via the metamorphic user was estimated not high enough to justify the extra role. Thus, bring database back in line with rev. ffdb507d5b42 and re-enable password reset.
author Tom Gottfried <tom@intevation.de>
date Thu, 23 Aug 2018 16:41:44 +0200
parents ff9dbe14f033
children 6590208e3ee1
comparison
equal deleted inserted replaced
477:00b52d653039 478:3af7ca761f6a
39 WHERE p.username = current_user 39 WHERE p.username = current_user
40 OR pg_has_role('waterway_admin', 'MEMBER') 40 OR pg_has_role('waterway_admin', 'MEMBER')
41 AND p.country = ( 41 AND p.country = (
42 SELECT country FROM internal.user_profiles 42 SELECT country FROM internal.user_profiles
43 WHERE username = current_user) 43 WHERE username = current_user)
44 OR pg_has_role('pw_reset', 'MEMBER')
45 OR pg_has_role('sys_admin', 'MEMBER'); 44 OR pg_has_role('sys_admin', 'MEMBER');
46 45
47 46
48 CREATE OR REPLACE FUNCTION users.current_user_country() 47 CREATE OR REPLACE FUNCTION users.current_user_country()
49 RETURNS internal.user_profiles.country%TYPE 48 RETURNS internal.user_profiles.country%TYPE
99 CREATE TRIGGER update_metamorph INSTEAD OF INSERT ON users.list_users 98 CREATE TRIGGER update_metamorph INSTEAD OF INSERT ON users.list_users
100 FOR EACH ROW 99 FOR EACH ROW
101 EXECUTE PROCEDURE internal.update_metamorph(); 100 EXECUTE PROCEDURE internal.update_metamorph();
102 101
103 102
104 -- Prevent roles other than sys_admin and pw_reset to update any user but 103 -- Prevent roles other than sys_admin to update any user but
105 -- themselves (affects waterway_admin) 104 -- themselves (affects waterway_admin)
106 CREATE OR REPLACE FUNCTION internal.authorize_update_user() RETURNS trigger 105 CREATE OR REPLACE FUNCTION internal.authorize_update_user() RETURNS trigger
107 AS $$ 106 AS $$
108 BEGIN 107 BEGIN
109 IF OLD.username <> current_user 108 IF OLD.username <> current_user
110 AND NOT (pg_has_role('sys_admin', 'MEMBER') 109 AND NOT pg_has_role('sys_admin', 'MEMBER')
111 OR pg_has_role('pw_reset', 'MEMBER'))
112 THEN 110 THEN
113 RETURN NULL; 111 RETURN NULL;
114 ELSE 112 ELSE
115 RETURN NEW; 113 RETURN NEW;
116 END IF; 114 END IF;
200 198
201 CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW 199 CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW
202 EXECUTE PROCEDURE internal.delete_user(); 200 EXECUTE PROCEDURE internal.delete_user();
203 201
204 202
205 CREATE OR REPLACE VIEW pw_reset.list_users AS
206 SELECT username, pw, email_address FROM users.list_users;
207
208 -- To set a role from a hex-encoded user name (which is save from SQL injections). 203 -- To set a role from a hex-encoded user name (which is save from SQL injections).
209 CREATE OR REPLACE FUNCTION public.setrole(role text) RETURNS void 204 CREATE OR REPLACE FUNCTION public.setrole(role text) RETURNS void
210 AS $$ 205 AS $$
211 BEGIN 206 BEGIN
212 IF role IS NOT NULL AND role <> '' THEN 207 IF role IS NOT NULL AND role <> '' THEN