Re: Can't get policy to work correctly

Well I seems I got it working. Still not sure what I did wrong.

I finally got it working by moving the sub select into a view and only use
the view in the policy:

(u): ((hs_group_id IS NULL) OR (hs_group_id IN ( SELECT
allowed_hs_groups.hs_group_id
FROM allowed_hs_groups)))

The allowed_hs_groups:

CREATE OR REPLACE VIEW public.allowed_hs_groups AS
SELECT gsg.hs_group_id
FROM hs_group_sub_group gsg
JOIN hs_system_user_sub_group sh ON sh.hs_sub_group_id =
gsg.hs_sub_group_id
JOIN system_user su ON su.id = sh.system_user_id
WHERE su.login_name = "current_user"()::text;

And now it works...

Thanks for reading!

Best regards,
Ivo Limmen

On Tue, Mar 27, 2018 at 9:20 AM, Ivo Limmen <ivo(at)limmen(dot)org> wrote:

> Hi list!
>
> I am a long postgres user but only since a short time I am using the more
> advanced stuff. And now I use the row level security I run into a problem.
>
> I use postgres 9.5.12. I have multiple users; postgres, root and ivo. I
> have a table called person. It contains multiple rows that should be
> filtered using RLS. The table structure is a bit weird (not mine design) so
> the policy on the table is: (from \z)
>
> (u): ((hs_group_id IS NULL) OR (hs_group_id IN ( SELECT
> gsg.hs_group_id
> FROM ((hs_group_sub_group gsg
>
> JOIN hs_system_user_sub_group sh ON ((sh.hs_sub_group_id =
> gsg.hs_sub_group_id)))
> JOIN system_user su ON ((su.id = sh.system_user_id)))
>
> WHERE (su.login_name = ("current_user"())::text))))
>
> The tables that are used in the policy do not have a policy.
> All users have all privileges on all tables. postgres user is the owner of
> all tables (and has RLS bypass)
>
> When I execute:
> set role ivo;
> select * from person;
>
> I expect 2 rows but I only get 1 (left part of the policy; hs_group_id =
> null).
>
> Now the weird part:
>
> When doing a select * from any of the tables as the user ivo I see all the
> relevant data (nothing is filtered).
> Executing a select current_role also works.
>
> When I run:
>
> set role postgres;
> select * from person where
> ((hs_group_id IS NULL) OR (hs_group_id IN ( SELECT
> gsg.hs_group_id
> FROM ((hs_group_sub_group gsg
>
> JOIN hs_system_user_sub_group sh ON ((sh.hs_sub_group_id =
> gsg.hs_sub_group_id)))
> JOIN system_user su ON ((su.id = sh.system_user_id)))
>
> WHERE (su.login_name = 'ivo'))));
>
> ​I get the two rows I expected. This query is the same as the policy but I
> changed the current_user to a fixed argument as I am postgres in this case.
>
> I can not figure out what I am doing wrong. I hope someone has a clue.​
>
> ​Best regards,
> Ivo Limmen​
>
>
> --
> Met vriendelijke groet,
> Ivo Limmen
>

--
Met vriendelijke groet,
Ivo Limmen