skip to Main Content

In my Postgres database, I create a role group with BYPASSRLS. I assign group to user archibald. I expect user to have BYPASSRLS. However, I find the user cannot bypass the row-level security.

I can work around it by granting BYPASSRLS to archibald directly, but I would like to manage this by group.

Code:

create role group nologin bypassrls;
grant usage on schema public to group;
grant select on all tables in schema public to group;

create user archibald password 'password';
grant group to archibald;

Expected result: archibald should be able to see all rows in all tables.
Actual result: He sees nothing due to row-level security.

2

Answers


  1. Like all other properties of a role, you cannot inherit BYPASSRLS. Only privileges and ownership on objects and membership in roles are inherited.

    Login or Signup to reply.
  2. Strictly speaking, BYPASSRLS is a role attribute and those are not inheritable. That being said, a GRANT establishing group membership by default comes with SET option which allows the direct member to impersonate/switch to the group role and make use of the group role’s attributes:

    create table test(a int);
    insert into test values (1),(2);
    alter table test enable row level security;
    create policy test_policy on test as permissive for all using(a=1);
    
    create role group_ nologin bypassrls;
    grant usage on schema public to group_;
    grant select on all tables in schema public to group_;
    
    create user archibald password 'password';
    grant group_ to archibald with set option /*that's default, it's here for clarity*/;
    

    As user postgres, you can see a=2 listed because of bypassrls. Otherwise the policy would hide it.

    set role postgres;
    select * from test;
    
    a
    1
    2

    User archibald won’t see a=2 because bypassrls wasn’t inherited from group_ they belong to:

    set role archibald;
    select * from test;
    
    a
    1

    archibald can impersonate the group_ role and use their bypassrls to see a=2

    set role group_;
    select * from test;
    
    a
    1
    2

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search