Can't get policy to work correctly

From: Ivo Limmen <ivo(at)limmen(dot)org>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Can't get policy to work correctly
Date: 2018-03-27 07:20:48
Message-ID: CAByMQt7eS9uM0G9K9ZnXXi1L1+USmgwR=uEtWb3iqdL-djOGTQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ivo Limmen 2018-03-27 08:17:18 Re: Can't get policy to work correctly
Previous Message Michael Paquier 2018-03-27 04:39:00 Re: Warning of .partial wal file in PITR and Replication Environment