If you haven’t installed the BigSQL Postgres Distribution, you can start here to use an installer or here if you want to create a sandbox via command line.

Next, via command line, navigate to the BigSQL or PostgreSQL directory where pgc is located run the update command (to get the latest releases), install PostgreSQL and install the latest set_user component by running the following command (Windows users don’t prefix the pgc command with ./ as shown in the below examples):

./pgc update
./pgc install pg96
./pgc start pg96
./pgc install setuser1-pg96

Invoke session environment variables

Navigate to the pg96 directory. And run the environment variable file.

Linux / OSX:

 source pg96.env

Windows:

 pg96-env.bat

Edit postgresql.conf

Add set_user to the shared_preload_libraries line to postgresql.conf using your favorite editor. There are many ways of doing this (including the ALTER SYSTEM command):

Linux / OSX:

vi $PGDATA/postgresql.conf

Windows:

start notepad %PGDATA%/postgresql.conf

Add this line to the end of the file:

shared_preload_libraries = 'set_user'

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

./pgc restart

Create a database

psql -U postgres postgres
postgres=# CREATE DATABASE testdb;
    CREATE DATABASE

Create the set_user extension

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

testdb=# CREATE EXTENSION set_user;
    CREATE EXTENSION

testdb=# \dx
                     List of installed extensions
   Name   | Version |   Schema   |                Description
----------+---------+------------+--------------------------------------------
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
 set_user | 1.4     | public     | similar to SET ROLE but with added logging

Create a new user

Create user with a password:

testdb=# CREATE USER dba_user WITH PASSWORD 'passuser';

Give the new user privileges to execute set_user functions

From https://github.com/pgaudit/set_user

The concept is to grant the EXECUTE privilege to the set_user() and/or set_user_u() function to otherwise unprivileged postgres users. These users can then transition to other roles, possibly escalating themselves to superuser through use of set_user_u(), when needed to perform specific actions.

The set_user function is used when switching to non-superuser roles (to be discussed in a later post).

Grant dba_user priviliges to execute the set_user and set_user_u functions:

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

Exit out of this psql session:

testdb=# \q 

Get me outta here: How to drop a user with set_user privileges

If you try to drop user before revoking the set_user EXECUTE privileges, you will get this error:

testdb=# drop user dba_user;
    ERROR:  role "dba_user" cannot be dropped because some objects depend on it
    DETAIL:  privileges for function set_user_u(text)
    privileges for function set_user(text)

So, first you will need to revoke the EXECUTE ON FUNCTION you GRANTED during the installation and setup.

testdb=# REVOKE EXECUTE ON FUNCTION set_user(text) FROM dba_user;
    REVOKE
testdb=# REVOKE EXECUTE ON FUNCTION set_user_u(text) FROM dba_user;
    REVOKE

Now, you can successfully drop the user:

testdb=# drop user dba_user;
    DROP ROLE

In our next post, Understanding the set_user_u Function, we will dig deeper into the set_user_u function and how and when to use it.