For this last post in our set_user series, let’s look at how we can limit who is allowed to escalate to superuser at a super granular level.

Whitelist

By default, the whitelist parameter is set to allow all (*) users with set_user permissions to escalate to superuser. But what if you want to limit these privileges to an elite few? Like in our last post where we added block setting parameters to the postgres.conf file, you can control who gets to be part of the “in crowd” with the whitelist parameter:

set_user.superuser_whitelist = '<user1>,<user2>,...,<userN>'

Exercise: Setting the whitelist parameter

Edit postgresql.conf

Add set_user.superuser_whitelist to the postgresql.conf using your favorite editor:

Linux / OSX:

vi $PGDATA/postgresql.conf

Windows:

start notepad %PGDATA%/postgresql.conf

Earlier you added this line to the end of the file:

shared_preload_libraries = 'set_user'

Now underneath that entry, you will add the following:

set_user.superuser_whitelist = 'dba_super' 

Save the change you made and restart PostgreSQL by navigating to the directory (bigsql or PostgreSQL) and run:

./pgc restart

Create the database and users

If you haven’t already created the testdb database from the Installation and Set-Up exercise, do that now and create the set_user extension as well:

postgres=# CREATE DATABASE testdb;
CREATE DATABASE

postgres=# \connect testdb;
You are now connected to database "testdb" as user "postgres".

testdb=# CREATE EXTENSION set_user;

Then create the following two roles:

testdb=# CREATE USER dba_notsuper with PASSWORD 'passnotsuper';
CREATE ROLE

testdb=# CREATE USER dba_super with PASSWORD 'passsuper';
CREATE ROLE

Grant execute privileges

Grant the new roles priviliges to execute the set_user and set_user_u functions:

testdb=# GRANT EXECUTE ON FUNCTION set_user(text) TO dba_notsuper;
testdb=# GRANT EXECUTE ON FUNCTION set_user_u(text) TO dba_notsuper;
testdb=# GRANT EXECUTE ON FUNCTION set_user(text) TO dba_super;
testdb=# GRANT EXECUTE ON FUNCTION set_user_u(text) TO dba_super;

Exit out of this psql session:

testdb=# \q 

Try to escalate dba_notsuper to superuser

Login to testdb as dba_notsuper and enter password when prompted:

psql -U dba_notsuper testdb

Check to see that current user is dba_notsuper:

testdb=> SELECT CURRENT_USER, SESSION_USER;
 current_user | session_user
--------------+--------------
 dba_notsuper | dba_notsuper
(1 row)

Using setuseru(), try switching current user to postgres. You should get the following error alerting you that it is not allowed:

testdb=> SELECT set_user_u('postgres');
ERROR:  switching to superuser not allowed
HINT:  Add current user to set_user.superuser_whitelist.

Exit out of this psql session:

testdb=# \q

Try to escalate dba_super to superuser

Now, login to testdb as dba_super and enter password when prompted:

psql -U dba_super testdb

Check to see that current user is dba_super:

testdb=> SELECT CURRENT_USER, SESSION_USER;
 current_user | session_user
--------------+--------------
 dba_super    | dba_super
(1 row)

Using set_user_u(), try switching current user to postgres.

testdb=> SELECT set_user_u('postgres');
 set_user_u
------------
 OK
(1 row)

And… it works!

testdb=# SELECT CURRENT_USER, SESSION_USER;
 current_user | session_user
--------------+--------------
 postgres     | dba_super
(1 row)

This concludes our set_user series comprised of the following posts:

The set_user extension is also available with our PostgreSQL 10 binaries as well.

Control who has the power and make sure you know what they are doing with the set_user extension!