In a recent project at a customer where we synchronize the users and group out of Active Directory we hit a little issue I was not aware of before. Suppose you have created a role in PostgreSQL, you made that role a superuser and then granted that role to another role. What happens when you login using the other role? Will you have the super user privileges by default? Sounds confusing, I know, so lets do a test.
To start with we create a simple role and make that role a super user:
postgres=# create role my_admin; CREATE ROLE postgres=# alter role my_admin superuser; ALTER ROLE
Of course you could also do that in one step:
postgres=# create role my_admin superuser; CREATE ROLE
As a second step lets create a new user that is a member of the admin group and inherits the permissions of that role automatically:
postgres=# create user my_dba login password 'admin' in role my_admin inherit; CREATE ROLE postgres=# du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+------------ my_admin | Superuser, Cannot login | {} my_dba | | {my_admin} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
The questions now is: When we login using the my_dba user are we superuser automatically?
postgres@pgbox:/home/postgres/ [PGDEV] psql -X -U my_dba postgres psql (12devel) Type "help" for help. postgres=> du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+------------ my_admin | Superuser, Cannot login | {} my_dba | | {my_admin} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres=> create database db1; ERROR: permission denied to create database postgres=>
… and we are not. What we can do is:
postgres=> set role my_admin; SET postgres=# create database db1; CREATE DATABASE
The reason for that is that some privileges are not inherited automatically and these are: LOGIN, SUPERUSER, CREATEDB, and CREATEROLE.
What you can do is put something like that into “.psqlrc”:
set role my_admin
… or do it like that:
postgres=# alter user my_dba set role my_admin; ALTER ROLE
This will explicitly set the role with each login and the super user privileges will be there. When you have a bit more complicated scenario where roles are assigned based on patterns in the username you could do something like this and add it to .psqlrc as well (or put that into a file and then execute that file in .psqlrc):
DO $$ DECLARE lv_username pg_roles.rolname%TYPE := current_user; BEGIN if ( substr(lv_username,1,2) = 'xx' and position ('yy' in lv_username) > 0 ) then execute 'set role my_admin'; end if; perform 1; END $$;
… or whatever checks you need to identify the correct user names. Hope that helps …