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
