Re: Can't get policy to work correctly

[Ivo Limmen <ivo@xxxxxxxxxx>]

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