Mercurial > gemma

changeset 103:b29538ac409d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix authorisation of templates for waterway_user. SELECT privilege for relation-table is needed to rely on it in RLS policy. Removed unnecessary JOINs that lead to infinite recursion.
author Tom Gottfried <tom@intevation.de>
date Fri, 15 Jun 2018 18:12:40 +0200
parents 3780a1afdc98
children bc1c7fa3a939
files auth.sql
diffstat 1 files changed, 3 insertions(+), 5 deletions(-) [+]
line wrap: on
line diff
--- a/auth.sql	Fri Jun 15 17:33:39 2018 +0200
+++ b/auth.sql	Fri Jun 15 18:12:40 2018 +0200
@@ -21,7 +21,7 @@
 GRANT USAGE ON SCHEMA wamos, wamos_waterway, wamos_fairway TO waterway_user;
 GRANT SELECT ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway
       TO waterway_user;
-GRANT SELECT ON templates, user_profiles TO waterway_user;
+GRANT SELECT ON templates, user_templates, user_profiles TO waterway_user;
 
 --
 -- Extended privileges for waterway_admin
@@ -63,10 +63,8 @@
 ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
 
 CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user
-       USING (id IN(SELECT t.id FROM templates t
-                    JOIN user_templates ut ON t.id = ut.template_id
-                    JOIN user_profiles p ON ut.username = p.username
-                    WHERE p.username = current_user));
+       USING (id IN(SELECT template_id FROM user_templates
+                    WHERE username = current_user));
 ALTER TABLE templates ENABLE ROW LEVEL SECURITY;
 
 --