Mercurial > gemma
changeset 103:b29538ac409d
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; --