Superuser, and what it really grants · PostgreSQL

A superuser bypasses every permission check in the database. That much is expected. What is frequently not appreciated is that several superuser capabilities do not stop at the database boundary: they reach the filesystem and the host, as the operating-system account PostgreSQL runs as.

That makes a superuser grant a host access decision, and it should be reviewed by whoever reviews host access.

What it bypasses inside the database

Every privilege check. SELECT on any table in any database, DROP on any object, ALTER on any role including changing its password. Row-level security policies do not apply. GRANT and REVOKE need no ownership.

Also every safety mechanism that is implemented as a privilege check, which is a longer list than it sounds: the restrictions on ALTER SYSTEM, on creating extensions, on reading the statistics of other databases, and on terminating other sessions.

What it reaches outside the database

This is the part that changes the risk assessment.

Capability	Reaches
`COPY ... FROM PROGRAM` / `TO PROGRAM`	Executes shell commands as the database OS user
`COPY ... FROM '/path'` / `TO '/path'`	Reads and writes any file that user can
`pg_read_server_files`-equivalent functions	Reads arbitrary files
`CREATE EXTENSION` of an untrusted extension	Loads native code into the server process
`CREATE LANGUAGE` for an untrusted language	Runs arbitrary code in the backend
`ALTER SYSTEM`	Changes what the server does at next restart

COPY TO PROGRAM is the clearest case. A superuser can run a shell command on the database host as the postgres account. From there, reading the data directory, reading any credentials that account can reach, and establishing persistence are ordinary operations.

A superuser grant is equivalent to shell access on the database host

Not “similar to”. A superuser can execute commands as the operating system account that owns the data directory, which is the account with complete access to every database file, the write-ahead log, and any credential material readable by it.

The practical consequences for how such a grant is handled:

It should be approved by whoever approves host access, not by whoever approves database access, because that is what it is.

It should not be held by an application. An application credential appears in configuration files, environment variables, container images, CI logs and error reports, and each of those becomes host access.

It should not be shared. A shared superuser account destroys attribution: session_user records the account, so an audit trail cannot distinguish who used it.

And its use should be alerted on rather than merely logged, because legitimate superuser use is rare enough that each occurrence is worth a human noticing.

Superuser-equivalent grants

An audit that counts rolsuper misses most of the exposure, because several ordinary grants confer the same reach by a different route.

Read-only / Safethe predefined roles that are effectively host access— These three are not administrative conveniences. Captured from a live 18.6 cluster.

$ psql -U postgres -tAc "SELECT string_agg(rolname, ', ' ORDER BY rolname) FROM pg_roles WHERE rolname LIKE 'pg\_%server%' OR rolname = 'pg_execute_server_program'"

pg_execute_server_program, pg_read_server_files, pg_write_server_files

Grant	Why it is superuser-equivalent
`pg_execute_server_program`	Runs shell commands as the database OS user
`pg_write_server_files`	Writes any file that user can, including its own authorised keys
`pg_read_server_files`	Reads any file that user can, including the data files
`CREATEROLE`	Historically could grant itself membership of other roles; restricted in 16 and still powerful
Ownership of a function called by a superuser	The function runs with the caller’s privileges unless `SECURITY DEFINER` says otherwise
Membership of a role that owns everything	Can drop or alter every object

The audit query has to cover all of them:

# Superusers
psql -U postgres -c \
  "SELECT rolname FROM pg_roles WHERE rolsuper ORDER BY 1"

# Superuser-equivalent memberships, resolved transitively
psql -U postgres -c \
  "SELECT r.rolname AS role, g.rolname AS grants
     FROM pg_auth_members m
     JOIN pg_roles r ON r.oid = m.member
     JOIN pg_roles g ON g.oid = m.roleid
    WHERE g.rolname IN ('pg_execute_server_program',
                        'pg_read_server_files',
                        'pg_write_server_files')
    ORDER BY 1"

# Roles with dangerous attributes
psql -U postgres -c \
  "SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolbypassrls
     FROM pg_roles
    WHERE rolsuper OR rolcreaterole OR rolbypassrls ORDER BY 1"

Granting superuser so that a tool can run VACUUM or read statistics

The need is real and narrow, and superuser is the answer that comes to mind because it certainly works.

Every common case has a specific predefined role, introduced precisely so that this grant does not have to happen:

The need	Grant this, not superuser
Read every statistics view	`pg_monitor`
`VACUUM`, `ANALYZE`, `REINDEX` any table	`pg_maintain`
Terminate or cancel other sessions	`pg_signal_backend`
Run `CHECKPOINT` before a backup	`pg_checkpoint`
Connect during saturation	`pg_use_reserved_connections`
Read all application data for reporting	`pg_read_all_data`
Read configuration values	`pg_read_all_settings`

pg_maintain arrived relatively recently and is the one most often missed: before it existed, a maintenance job that vacuumed tables it did not own genuinely needed elevated rights, and many estates still carry a superuser grant made for that reason years ago.

The audit question worth asking of every existing superuser is narrower than “does this need superuser”: it is “what does this actually do”, followed by “which of these roles covers it”.

Reducing an existing estate

The order that avoids breaking things:

Inventory. Every superuser, every superuser-equivalent membership, every role with CREATEROLE or BYPASSRLS.
For each, establish what it actually does. pg_stat_statements and the audit trail answer this better than asking.
Grant the narrow roles alongside the existing superuser. Privileges are additive, so nothing breaks at this stage.
Remove the superuser attribute, one role at a time, with a rollback ready.
Verify that the workload still functions before moving to the next.

ALTER ROLE x NOSUPERUSER takes effect for new sessions; existing sessions keep the privileges they started with, so verification means reconnecting rather than waiting.

Two roles genuinely need superuser and are worth keeping deliberately: a break-glass account whose credential lives in a vault and whose use is alerted on, and whatever the operating-system postgres account uses for local maintenance via peer authentication.

Why an untrusted language or extension is a superuser question

CREATE EXTENSION and CREATE LANGUAGE look like ordinary schema operations and are restricted for a reason that is easy to miss.

PostgreSQL distinguishes trusted and untrusted procedural languages. A trusted language — PL/pgSQL is the canonical example — cannot reach outside the database: no filesystem, no network, no process control. Any role with CREATE on a database may use it, and that is safe by construction.

An untrusted language — PL/Python, PL/Perl in its untrusted form — executes arbitrary code inside the backend process, with the operating system privileges of the database’s OS account. A function written in one can open sockets, read files and execute programs. That is why only a superuser may create one, and why granting EXECUTE on such a function to another role hands that role the same reach.

Extensions raise the same question in a different form. A non-trusted extension loads a shared library into the server process. The library runs as part of the backend, with no sandbox at all, and can do anything the process can. CREATE EXTENSION for such an extension is therefore a superuser operation, and the trusted marker in an extension’s control file is the assertion that it does not need to be.

The operational consequence is that “which extensions are installed” is a security question as much as a compatibility one, and it belongs in the same review as superuser grants:

SELECT e.extname, e.extversion, n.nspname AS schema,
       a.rolname AS owner
  FROM pg_extension e
  JOIN pg_namespace n ON n.oid = e.extnamespace
  JOIN pg_authid a ON a.oid = e.extowner
 ORDER BY 1;

Part XVII returns to extensions from the upgrade angle, where an extension that has not been rebuilt for the target major version blocks the upgrade entirely.

Production discipline

Treat a superuser grant as a host access decision, and route it through whoever approves host access.
Never give an application superuser. Its credential appears in configuration, images, CI logs and error reports.
Audit for superuser-equivalent grants, not just rolsuper. The three server-file and program roles confer the same reach.
Replace with the narrowest predefined role. pg_monitor, pg_maintain, pg_signal_backend and pg_checkpoint cover most historical reasons for the grant.
Reduce additively. Grant the narrow roles first, remove superuser second, verify by reconnecting.
Keep exactly two superusers deliberately: a vaulted break-glass account with alerting on its use, and local peer administration.
Review installed extensions alongside superuser. A non-trusted extension is native code in the backend process.

Cross-course references

Secrets, PKI & Certificate Management — Part I (Foundations) covers blast-radius calculation including transitive authority, which is exactly the reasoning that makes a superuser grant a host access decision, and Part XVIII covers responding to a compromised credential.
Linux for Production Sysadmins — Part V (Sudo) covers the host equivalent, and Part XXXI (Audit) covers alerting on rare privileged actions.
Kubernetes for Production Sysadmins — Part LVIII (RBAC) and Part LXIII (Linux security) cover the same escalation reasoning where a workload privilege becomes node access.