postgresqlroles

How to create Postgres role that is not "on" by default

I'm trying to create a role that is not "on" by default but needs to "turn on" by SET ROLE command. I thought NOINHERIT would be the thing but this is not working (in Postgres v. 15):

create role admin_role NOINHERIT;
create role user login password 'secret';
grant admin_role to user;

create table t(i integer);
alter table revoke insert from user;

-- login as user1
insert into t values (1); -- does happen!!!

I would like user1 to be able to insert data only after SET ROLE admin_role.

Solution

  • You have to use NOINHERIT on the member, not on admin_role.

    NOINHERIT means "does not inherit privileges from roles where it is member".

    Before PostgreSQL v16, you can only disable all inheritance for a certain role:

    CREATE ROLE myuser LOGIN NOINHERIT IN ROLE admin_role;

    If you want to inherit from some roles but not for others, use an intermediary role:

    CREATE ROLE noinherit_proxy NOLOGIN NOINHERIT IN ROLE admin_role;
CREATE ROLE myuser LOGIN INHERIT IN ROLE noinherit_proxy;

    From PostgreSQL v16 on, you can configure inheritance per role you are a member of:

    CREATE ROLE myuser LOGIN;
GRANT admin_role TO myuser WITH INHERIT FALSE;