Infrastructure at your Service

Daniel Westermann

Inheriting super user privileges over a role automatically in PostgreSQL

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 …

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure