Mercurial > gemma

diff 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
line wrap: on
line diff
--- a/schema/manage_users.sql	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/manage_users.sql	Thu Aug 23 16:41:44 2018 +0200
@@ -41,7 +41,6 @@
                 AND p.country = (
                     SELECT country FROM internal.user_profiles
                         WHERE username = current_user)
-            OR pg_has_role('pw_reset', 'MEMBER')
             OR pg_has_role('sys_admin', 'MEMBER');
 
 
@@ -101,14 +100,13 @@
     EXECUTE PROCEDURE internal.update_metamorph();
 
 
--- Prevent roles other than sys_admin and pw_reset to update any user but
+-- Prevent roles other than sys_admin to update any user but
 -- themselves (affects waterway_admin)
 CREATE OR REPLACE FUNCTION internal.authorize_update_user() RETURNS trigger
 AS $$
 BEGIN
     IF OLD.username <> current_user
-        AND NOT (pg_has_role('sys_admin', 'MEMBER')
-            OR pg_has_role('pw_reset', 'MEMBER'))
+        AND NOT pg_has_role('sys_admin', 'MEMBER')
     THEN
         RETURN NULL;
     ELSE
@@ -202,9 +200,6 @@
     EXECUTE PROCEDURE internal.delete_user();
 
 
-CREATE OR REPLACE VIEW pw_reset.list_users AS
-    SELECT username, pw, email_address FROM users.list_users;
-
 -- To set a role from a hex-encoded user name (which is save from SQL injections).
 CREATE OR REPLACE FUNCTION public.setrole(role text) RETURNS void
 AS $$