Mercurial > gemma

changeset 312:0745b4d336c4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Place functions in more matching schemas check_password is not meant to be user-visible API. current_user_country smells like user management related.
author Tom Gottfried <tom@intevation.de>
date Wed, 01 Aug 2018 17:35:12 +0200
parents 74559e12a59f
children 10b93a8ee057
files schema/auth.sql schema/manage_users.sql
diffstat 2 files changed, 7 insertions(+), 7 deletions(-) [+]
line wrap: on
line diff
--- a/schema/auth.sql	Wed Aug 01 17:29:52 2018 +0200
+++ b/schema/auth.sql	Wed Aug 01 17:35:12 2018 +0200
@@ -80,10 +80,10 @@
 CREATE POLICY responsibility_area ON waterway.bottlenecks
     FOR ALL TO waterway_admin
     USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
-        WHERE country = current_user_country())));
+        WHERE country = users.current_user_country())));
 CREATE POLICY responsibility_area ON waterway.sounding_results
     FOR ALL TO waterway_admin
     USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
-        WHERE country = current_user_country())));
+        WHERE country = users.current_user_country())));
 
 COMMIT;
--- a/schema/manage_users.sql	Wed Aug 01 17:29:52 2018 +0200
+++ b/schema/manage_users.sql	Wed Aug 01 17:35:12 2018 +0200
@@ -3,7 +3,7 @@
 -- exposing it to appropriately privileged users
 --
 
-CREATE OR REPLACE FUNCTION users.check_password(
+CREATE OR REPLACE FUNCTION internal.check_password(
     pw varchar
     )
     RETURNS varchar
@@ -26,7 +26,7 @@
 
 -- Security-definer function to get current users country, which allows to
 -- restrict the view on user_profiles by country without infinite recursion
-CREATE FUNCTION current_user_country()
+CREATE FUNCTION users.current_user_country()
     RETURNS internal.user_profiles.country%TYPE
     AS $$
         SELECT country FROM internal.user_profiles
@@ -51,7 +51,7 @@
             JOIN pg_roles r ON a.roleid = r.oid
         WHERE p.username = current_user
             OR pg_has_role('waterway_admin', 'MEMBER')
-                AND p.country = current_user_country()
+                AND p.country = users.current_user_country()
             OR pg_has_role('sys_admin', 'MEMBER');
 
 
@@ -78,7 +78,7 @@
         'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
         username,
         userrole,
-        users.check_password(pw));
+        internal.check_password(pw));
 END;
 $$
     LANGUAGE plpgsql
@@ -117,7 +117,7 @@
         EXECUTE format(
             'ALTER ROLE %I PASSWORD %L',
             cur_username,
-            users.check_password(NEW.pw));
+            internal.check_password(NEW.pw));
     END IF;
 
     -- Do not leak new password