set_user

1. Overview

set_user is a PostgreSQL security auditing extension maintained by the pgaudit project that enhances the native user-switching capability. It supports switching between ordinary users as well as controlled escalation to superuser; configurable allowlists restrict which users may be switched to, all switching operations are recorded in the audit log, full SQL logging is enforced when switching to superuser, and high-risk operations such as modifying database configuration or invoking system commands are blocked. It standardizes temporary privilege escalation. Deployment requires preloading the plugin and restarting the database, then creating the extension before use.

Project page: https://github.com/pgaudit/set_user

Version: REL4_2_0

License: PostgreSQL License

2. How It Works

PostgreSQL’s native SET ROLE / SET SESSION AUTHORIZATION has two security weaknesses: first, after escalating privileges a user can casually run SET log_statement = 'none' to turn off logging, or RESET ROLE to quietly switch back, leaving no audit trail; second, to let DBAs perform superuser work, they typically must be allowed to log in directly as a superuser account, granting excessive and uncontrolled privileges.

set_user’s approach is "don’t forbid privilege escalation, but make every escalation fully traceable and non-repudiable." Once deployed, all superuser accounts can be set to NOLOGIN; DBAs log in with ordinary accounts and call set_user_u('postgres') when escalation is needed. Throughout the window from escalation until reset_user() restores the original identity: the user switch is written to the log, log_statement is forcibly set to all so that every SQL statement is persisted, and the log prefix automatically gains an AUDIT tag for easy filtering and alerting; at the same time, every channel that could undermine auditing or escape the identity — ALTER SYSTEM, COPY PROGRAM, SET log_statement, SET ROLE, and the set_config() backdoor — is blocked. Because session_user always remains the real login user, "who did what, when, and under which identity" is plainly visible in the logs.

Implementation-wise, it is a C extension loaded via shared_preload_libraries and relies on three kernel mechanisms: ProcessUtility_hook to intercept dangerous statements, object_access_hook to block function-level backdoors, and transaction commit callbacks to guarantee transactional safety of the switch. Access control uses a dual-gate design — at the SQL layer, GRANT EXECUTE determines who may call the functions, while at the configuration layer, allowlists (superuser_allowlist, etc.) can be hot-adjusted at any time to tighten control. In addition, it provides a token-locked set_user(user, token) to prevent escape when a connection pooler holds connections on behalf of clients, and the irreversible set_session_auth() for permanent privilege drop before handing over a connection.

set_user cannot prevent a superuser from acting maliciously — its role is to ensure that no privileged operation can evade auditing. Combined with a log alerting system, it forms a key component of a least-privilege operations framework for PostgreSQL.

3. Usage Notes

set_user must be added to shared_preload_libraries. If other plugins implement post-execution hooks, set_user must be listed before them.
Superuser accounts (e.g., postgres) should be set to NOLOGIN; DBAs log in with ordinary accounts and call set_user_u('postgres') when escalation is needed.

  • Configuration options

Option Description Example Function

set_user.block_alter_system

on (default) or off

on

Block ALTER SYSTEM commands

set_user.block_copy_program

on (default) or off

on

Block COPY PROGRAM commands

set_user.block_log_statement

on (default) or off

on

Block changes to log_statement

set_user.nosuperuser_target_allowlist

Wildcard '*' (default) or string

'dba1, dba2, +admin_group'

List of target users that set_user() may switch to

set_user.superuser_allowlist

Wildcard '*' (default) or string

'dba1, dba2, +admin_group'

List of users allowed to call set_user_u() for escalation

set_user.superuser_audit_tag

String

'AUDIT'

Log prefix tag

set_user.exit_on_error

on (default) or off

on

Whether to exit the current session on error

  • Functions

Function Default privilege Parameters Description

set_user(text)

REMOVE FROM PUBLIC

Target non-superuser username

Switch the current session’s user identity to the specified non-superuser

set_user(text, text)

REMOVE FROM PUBLIC

Target non-superuser username, random token

Same as above, but requires a random token for added security

set_user_u(text)

REMOVE FROM PUBLIC

Target superuser username

Switch the current session’s user identity to the specified superuser

reset_user()

GRANT TO PUBLIC

None

Restore the current session’s user identity to the original user

reset_user(text)

GRANT TO PUBLIC

Random token

Token-protected restore; the matching token must be supplied

set_session_auth(text)

REMOVE FROM PUBLIC

Target non-superuser username

Similar to SET SESSION AUTHORIZATION, but can only switch to an ordinary user and cannot be reverted

4. Installation

The source build was tested on Ubuntu 26.04. IvorySQL must be installed; for installation steps see: https://github.com/IvorySQL/IvorySQL

4.1. Building from Source

  • Set the IvorySQL installation path

export IVY_BIN_DIR=/usr/local/ivorysql/bin
export TEST_DB_DIR=/tmp/test_db

  • Get the source code

git clone https://github.com/pgaudit/set_user.git
cd set_user
git checkout REL4_2_0

  • Build and install

make USE_PGXS=1 PG_CONFIG=${IVY_BIN_DIR}/pg_config clean
make USE_PGXS=1 PG_CONFIG=${IVY_BIN_DIR}/pg_config
make USE_PGXS=1 PG_CONFIG=${IVY_BIN_DIR}/pg_config install

  • Configuration

shared_preload_libraries = 'set_user'

4.2. Creating a Test Database

set_user must be added to shared_preload_libraries.

  • Initialize the database

# Initialize the database
${IVY_BIN_DIR}/initdb -U postgres -D ${TEST_DB_DIR}

# Add set_user to shared_preload_libraries (this can also be done manually)
sed -i "s/\(shared_preload_libraries = '.*\)'/\1, set_user'/g" ${TEST_DB_DIR}/ivorysql.conf

# Change the PG port and the Oracle-compatible port to avoid conflicts
cat << EOF >> ${TEST_DB_DIR}/ivorysql.conf
ivorysql.port = 1522
port = 5433
EOF

# Start the test database
${IVY_BIN_DIR}/pg_ctl -D ${TEST_DB_DIR} -l ${TEST_DB_DIR}/logfile start

4.3. Usage Test

  • Connect to the database

PGHOST=127.0.0.1 PGPORT=1522 PGUSER=postgres $IVY_BIN_DIR/psql

  • Test examples

The default configuration is used here, i.e., the allowlist is '*'. 
CREATE EXTENSION set_user;
LOAD 'set_user';

--- Create two users
CREATE USER dba;
CREATE USER bob;

-- Allow dba to execute set_user()
GRANT EXECUTE ON FUNCTION set_user(text) TO dba;
GRANT EXECUTE ON FUNCTION set_user(text,text) TO dba;
GRANT EXECUTE ON FUNCTION set_user_u(text) TO dba;

-- Switch to user dba to simulate a DBA login
SET SESSION AUTHORIZATION dba;
SELECT SESSION_USER, CURRENT_USER;
---  session_user | current_user
--- --------------+--------------
---  dba          | dba
--- (1 row)

--- Escalate privileges: using set_user() here fails because the target user is a superuser
SELECT set_user('postgres');
--- ERROR:  switching to superuser not allowed
--- HINT:  Use 'set_user_u' to escalate.

--- Escalate privileges: using set_user_u() here succeeds
SELECT set_user_u('postgres');
---  set_user_u
--- ------------
---  OK
--- (1 row)

--- Check the user state after escalation
SELECT SESSION_USER, CURRENT_USER;
---  session_user | current_user
--- --------------+--------------
---  dba          | postgres
--- (1 row)

--- Test a repeated set_user; it fails
SELECT set_user('bob');
--- ERROR:  must reset previous user prior to setting again

--- While escalated, ALTER SYSTEM fails
ALTER SYSTEM SET wal_level = minimal;
--- ERROR:  ALTER SYSTEM blocked by set_user config

--- While escalated, COPY PROGRAM fails
COPY (select 42) TO PROGRAM 'cat';
--- ERROR:  COPY PROGRAM blocked by set_user config

--- While escalated, SET log_statement fails
SET log_statement = 'none';
--- ERROR:  "SET log_statement" blocked by set_user config

--- Restore the original user
SELECT reset_user();
SELECT SESSION_USER, CURRENT_USER;
---  session_user | current_user
--- --------------+--------------
---  dba          | dba
--- (1 row)

--- Switch to the ordinary user bob
SELECT set_user('bob');
SELECT SESSION_USER, CURRENT_USER;
---  session_user | current_user
--- --------------+--------------
---  dba          | bob
--- (1 row)

--- Restore the original user
SELECT reset_user();
SELECT SESSION_USER, CURRENT_USER;
---  session_user | current_user
--- --------------+--------------
---  dba          | dba
--- (1 row)

Inspecting logfile shows that every operation performed after escalation was recorded, with the AUDIT tag in the log prefix. Each user switch has a corresponding line: LOG: XXX transitioning to YYY.

2026-07-17 13:55:34.905 CST [2670610] LOG:  Role dba transitioning to Superuser Role postgres
2026-07-17 13:55:34.905 CST [2670610] STATEMENT:  SELECT set_user_u('postgres');
2026-07-16 16:01:21.193 CST [2644289] AUDIT: LOG:  statement: SELECT SESSION_USER, CURRENT_USER;
2026-07-16 16:01:43.825 CST [2644289] AUDIT: LOG:  statement: SELECT set_user('bob');
2026-07-16 16:01:43.829 CST [2644289] AUDIT: ERROR:  must reset previous user prior to setting again
2026-07-16 16:01:43.829 CST [2644289] AUDIT: STATEMENT:  SELECT set_user('bob');
2026-07-16 16:02:20.177 CST [2644289] AUDIT: LOG:  statement: ALTER SYSTEM SET wal_level = minimal;
2026-07-16 16:02:20.177 CST [2644289] AUDIT: ERROR:  ALTER SYSTEM blocked by set_user config
2026-07-16 16:02:20.177 CST [2644289] AUDIT: STATEMENT:  ALTER SYSTEM SET wal_level = minimal;
2026-07-17 13:56:26.924 CST [2670610] STATEMENT:  SELECT set_user_u('postgres');
2026-07-17 14:01:12.017 CST [2670610] AUDIT: LOG:  statement: select reset_user();
2026-07-17 14:01:12.017 CST [2670610] AUDIT: LOG:  Superuser Role postgres transitioning to Role dba
2026-07-17 14:18:42.884 CST [2671784] LOG:  Role dba transitioning to Role bob
2026-07-17 14:18:42.884 CST [2671784] STATEMENT:  select set_user('bob');
2026-07-17 14:18:53.301 CST [2671784] LOG:  Role bob transitioning to Role dba
2026-07-17 14:18:53.301 CST [2671784] STATEMENT:  select reset_user();
2026-07-17 14:26:16.737 CST [2671784] LOG:  Role dba transitioning to Role bob
2026-07-17 14:26:16.737 CST [2671784] STATEMENT:  SELECT set_user('bob');

4.4. Cleaning Up the Test Database

# Stop the test database
${IVY_BIN_DIR}/pg_ctl -D ${TEST_DB_DIR} -l ${TEST_DB_DIR}/logfile stop

# Remove the test database
rm -rf ${TEST_DB_DIR}