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@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