Postgresql – Postgres inherit from one role and not another

inheritancepostgresql-9.5role

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?

Best Answer

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.

Related Solutions

Sql-server – Easily give role in one database access to tables in another database on the same server

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and role. Fortunately, you can script out this process using the system views:

USE [Y];
CREATE ROLE foo;

--grant all perms on foo

--Use this generate sql to add all your users to the role
select 'CREATE USER '+quotename(u.name)+';' + char(10)+
       'EXEC sp_addrolemember ''foo'','''+u.name+''';'
from (select name,principal_id
        from x.sys.database_principals where type = 'R') r
    join x.sys.database_role_members rm 
        on (r.principal_id = rm.role_principal_id)
    join (select name,type_desc,principal_id
        from x.sys.database_principals where type != 'R') u 
        on (rm.member_principal_id = u.principal_id )
where r.name = 'foo'

You can then take this sort of script/process to a batch job to keep things synchronized, but you will need some sort of external process to keep these roles in sync.

One piece that might be confusing here is that you have logins and other server level principals that are connected to the database principals, but there is a clear distinction between these. I provide some explanation about the differences between these principals in this answer

PostgreSQL Role Configuration

The PostgreSQL 9.3 Documentation has outlined how to alter default permissions. Follow this link to learn more!

Here is an excerpt that demonstrates how one would change the default permissions for a GROUP:

ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ] [ IN SCHEMA schema_name [, ...] ] abbreviated_grant_or_revoke

where abbreviated_grant_or_revoke is:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON TABLES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

You may also benefit from learning more about ROLES (follow link to documentation here). That would make it easier if you ever needed to re-assign permissions to other people to.

It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

A good place to do some practice/tutorial work on permissions, groups, roles, etc. is a site called Tutorials Point. They've got examples that will help you work through setting up permissions.

Best Answer

Related Solutions

Sql-server – Easily give role in one database access to tables in another database on the same server

PostgreSQL Role Configuration

Related Question