# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1529079160 -7200
# Node ID b29538ac409d0f91f58c941e3c91254b6d268f84
# Parent  3780a1afdc98d53806ea689cf7c6ec14b3382188
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.

diff -r 3780a1afdc98 -r b29538ac409d auth.sql
--- 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;
 
 --