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