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 <i...@limmen.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
