2

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?

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.

0

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.

  • Thanks @Ziggy but I'm looking more for a nested a solution (if it's possible) where those with elevated privileges are always a part of the company role (SELECT only) but to get their elevated privileges (ALL PRIVILEGES) they would use SET ROLE elevated_priv. The elevated privs wouldn't be available at login but would have to be explicitly set. – Pistol Pete Jun 8 '16 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.