set_user
similar to SET ROLE but with added logging
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
set_user | 4.2.0 | SEC | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 7370 | set_user | No | Yes | No | Yes | No | No | - |
| Related | pg_readonly pg_permissions pgaudit login_hook pgauditlogtofile pg_auth_mon credcheck pgextwlist |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 4.2.0 | 1817161514 | set_user | - |
| RPM | PGDG | 4.2.0 | 1817161514 | set_user_$v | - |
| DEB | PGDG | 4.2.0 | 1817161514 | postgresql-$v-set-user | - |
Install
You can install set_user directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install set_user; # Install for current active PG version
pig ext install -y set_user -v 18 # PG 18
pig ext install -y set_user -v 17 # PG 17
pig ext install -y set_user -v 16 # PG 16
pig ext install -y set_user -v 15 # PG 15
pig ext install -y set_user -v 14 # PG 14
dnf install -y set_user_18 # PG 18
dnf install -y set_user_17 # PG 17
dnf install -y set_user_16 # PG 16
dnf install -y set_user_15 # PG 15
dnf install -y set_user_14 # PG 14
apt install -y postgresql-18-set-user # PG 18
apt install -y postgresql-17-set-user # PG 17
apt install -y postgresql-16-set-user # PG 16
apt install -y postgresql-15-set-user # PG 15
apt install -y postgresql-14-set-user # PG 14
Create Extension:
CREATE EXTENSION set_user;
Usage
set_user allows switching users and optional privilege escalation with enhanced audit logging. It provides an additional layer of control when unprivileged users must escalate to superuser or object owner roles for maintenance tasks.
CREATE EXTENSION set_user;
Configuration
Add to postgresql.conf:
shared_preload_libraries = 'set_user'
| Parameter | Default | Description |
|---|---|---|
set_user.block_alter_system | on | Block ALTER SYSTEM when escalated |
set_user.block_copy_program | on | Block COPY PROGRAM when escalated |
set_user.block_log_statement | on | Block SET log_statement; force log_statement=all for superusers |
set_user.superuser_allowlist | * | Roles allowed to escalate to superuser |
set_user.nosuperuser_target_allowlist | * | Roles allowed as non-superuser targets |
set_user.superuser_audit_tag | AUDIT | Tag appended to log_line_prefix on escalation |
Functions
-- Switch to a non-superuser role
SELECT set_user('dbclient');
-- Escalate to superuser (requires EXECUTE on set_user_u)
SELECT set_user_u('postgres');
-- Switch with a token (token required for reset)
SELECT set_user('dbclient', 'my_secret_token');
-- Reset back to original user
SELECT reset_user();
SELECT reset_user('my_secret_token'); -- if token was used
-- Irrevocable session auth switch
SELECT set_session_auth('target_role');
Permission Setup
-- Allow role to switch to non-superuser roles
GRANT EXECUTE ON FUNCTION set_user(text) TO admin;
-- Allow role to escalate to superuser
GRANT EXECUTE ON FUNCTION set_user_u(text) TO dba;
Behavior on Escalation
When escalating to a superuser role:
- The role transition is logged with a specific notation
ALTER SYSTEMandCOPY PROGRAMare blocked (if configured)log_statementis forced toallfor full audit trail- The
AUDITtag is appended tolog_line_prefix
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.