Postgres inherit from one role and not another

Question

I'm a little new with postgres but have a decent understanding of roles. What I have are users that inherit from their normal "data_analyst" group. This group is used to manage all data analyst users with basic permissions. But I have a few users that will have elevated privileges. My initial thought was to create a new group for the elevated users and when they need their elevated privileges they would run SET ROLE elevated_role. They want to have to explicitly call those privileges.

They would essentially be a part of two roles, their limited role that they are granted and inherit from and the privileged role they have to set The business and users don't want their elevated privileges to be inherited to their main role. But this is problem based on my understanding of roles. If the user has been granted inherit they wouldn't need to SET their role because they would already have inherited it. How could I set this up? Is there another way I'm not thinking of?

Answer 1

I solved this by nesting the two roles together.

    CREATE role elevated_role nologin noinherit;

    GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA db to elevated_role;
    GRANT usage ON SCHEMA db to elevated_role;

    CREATE role normal_role nologin noinherit;
    GRANT usage ON SCHEMA db to normal_role;
    GRANT select on ALL tables in SCHEMA db to normal_role;

    GRANT elevated_role TO normal_role;

    CREATE new_user with password 'some_pass';
    GRANT normal_role TO new_user;

This allows me to manage the users in a group by just adding and deleting them from the group but gives them separate roles that they have to explicitly set if they want to insert, delete, etc.

Answer 2

Any user (ROLE) can be a member of one or more user groups (which are also ROLES).

According to the documentation for GRANT, "Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.". So you can have all of your personnel belonging to a company group (ROLE) with basic privileges, some in another group with elevated privileges, and a few with superuser privileges.