PostgreSQL Security

PostgreSQL security is usually described as a checklist, and a checklist has no way of saying which item matters. A threat model does: it names the paths an attacker actually takes to a database, ranks them by how often they are used and how much they yield, and pairs each with the control that closes it and the evidence that the control is in place. Seven paths cover nearly every PostgreSQL breach a database team is likely to meet, and they are not the seven that a compliance questionnaire asks about.

This page is that threat model. Each of the seven sections names the attack path, describes how it is taken against a PostgreSQL server as deployed in practice, states the control that closes it with the exact setting or catalog object, and names the query that proves the control is present.

The archive it introduces covers user-account hardening, LDAP integration on PostgreSQL 16, key management for encryption, GDPR-compliant data obfuscation, threat modelling for fintech, the security-relevant changes in PostgreSQL 18, and the UPDATE-with-LIMIT pattern that a compromised account most often abuses. The order is by how often the path is used in the field, not by how alarming it sounds.

PostgreSQL security path 1: the credential that was never rotated

The most used path is the least technical. An application role’s password is set at deployment, checked into a configuration repository or a container image, shared with a reporting tool, and never changed; the attacker does not break in, they log in.

The control is short-lived credentials: certificate or Kerberos authentication where the platform allows it, an external identity provider where it does not, and a rotation schedule with the previous password invalid within an hour of the new one for everything else. password_encryption = 'scram-sha-256' (the default since PostgreSQL 14) closes the related path of an MD5 hash captured from an old dump, and pg_hba.conf is where the method is enforced per role and per source network.

securing user accounts in PostgreSQL is the archive’s post on the role model, the password policy and the VALID UNTIL clause that makes a credential expire by itself, and PostgreSQL 16 LDAP integration: best practices and pitfalls covers moving authentication to a directory so that leaving the company revokes database access on the same day. The evidence is pg_authid.rolvaliduntil and the pg_hba.conf method column: any application role with no expiry and a md5 or password method is this path, open.

PostgreSQL security path 2: the superuser that everything runs as

The second path is privilege: an application connects as a superuser, or as a role with CREATEROLE or membership in pg_read_server_files, because that was easiest at setup, and an injection in the application becomes a read of any file the server can reach.

The control is least privilege applied at three levels: the application role owns nothing and has only the table privileges it uses, the schema owner is a separate role that the application cannot become, and superuser is reserved for the platform team with its use logged. PostgreSQL 16’s CREATEROLE changes (a role can only administer roles it created) and the predefined roles (pg_read_all_data, pg_write_all_data, pg_monitor) are what make least privilege practical without dozens of grants.

The evidence is a query against pg_roles for rolsuper, rolcreaterole and rolbypassrls, joined to pg_stat_activity to see which of those roles are actually used by connections from application hosts. PostgreSQL threat modelling for fintech is the archive’s post on ranking this path against the others in a regulated environment, and its finding is the usual one: the privilege path is opened by convenience during a migration and never closed.

-- the seven paths, audited from the catalog (PostgreSQL 14+; run as a monitoring role, read-only)

-- path 1: roles that can log in with no expiry, and the auth method each source uses
SELECT r.rolname, r.rolvaliduntil, r.rolcanlogin
FROM   pg_roles AS r
WHERE  r.rolcanlogin
  AND  r.rolvaliduntil IS NULL
  AND  r.rolname NOT LIKE 'pg\_%'
ORDER  BY r.rolname;
SELECT type, database, user_name, address, auth_method
FROM   pg_hba_file_rules
WHERE  auth_method IN ('md5', 'password', 'trust');

-- path 2: privileged roles, and whether an application host is connecting as one
SELECT r.rolname, r.rolsuper, r.rolcreaterole, r.rolbypassrls,
       COUNT(a.pid) AS live_connections,
       array_agg(DISTINCT host(a.client_addr)) FILTER (WHERE a.client_addr IS NOT NULL) AS from_hosts
FROM   pg_roles AS r
LEFT JOIN pg_stat_activity AS a ON a.usename = r.rolname
WHERE  r.rolsuper OR r.rolcreaterole OR r.rolbypassrls
GROUP  BY r.rolname, r.rolsuper, r.rolcreaterole, r.rolbypassrls;

-- path 4: tables with sensitive columns and no row-level security
SELECT n.nspname, c.relname, c.relrowsecurity, c.relforcerowsecurity
FROM   pg_class AS c
JOIN   pg_namespace AS n ON n.oid = c.relnamespace
WHERE  c.relkind = 'r'
  AND  n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND  NOT c.relrowsecurity
ORDER  BY 1, 2;

-- path 5: is the wire encrypted, and are any connections still plaintext?
SELECT ssl, COUNT(*) FROM pg_stat_ssl GROUP BY ssl;

-- path 7: is anything being audited at all?
SELECT name, setting FROM pg_settings
WHERE  name IN ('log_connections', 'log_disconnections', 'log_statement', 'shared_preload_libraries');

Every query above is a read; nothing here changes a role or a setting. The point of running them is the list they produce, which is the finding list of a security review in the order of the paths.

PostgreSQL security path 3: the application that concatenates

The third path is injection, and it is still open on estates that believe they closed it years ago, because the ORM is parameterised and the reporting scripts are not.

The control on the database side is defence in depth for the day the application fails: the application role cannot read tables it does not need, cannot execute functions that shell out, and cannot see other schemas; search_path is pinned per role so that a crafted object name in a public schema cannot shadow a real one; and statement logging is on for the role at a duration threshold, so that the first injected statement is in the log.

how to do UPDATE with LIMIT in PostgreSQL is in this archive for a reason that is not obvious from its title: a bounded update is the pattern a defended application uses so that a compromised statement cannot touch every row, and the post’s technique (a subquery with ctid or a key range and a limit) is also the technique a security review recommends for every batch job that runs as a privileged role. The evidence for this path is the application’s own code review, and on the database side the search_path setting per role in pg_db_role_setting.

PostgreSQL security path 4: the row the role should not see

The fourth path is authorisation at the row and column level: a role that legitimately reads a table reads rows belonging to a tenant, a region or a customer it has no business with, because the table has no row-level security and the filtering was left to the application. The control is row-level security (ALTER TABLE ...

ENABLE ROW LEVEL SECURITY with a policy per role, and FORCE ROW LEVEL SECURITY so that the table owner is not exempt), column privileges for the columns that are sensitive within a row, and views or security-barrier views for the cases where the policy is too complex for a USING clause.

GDPR-compliant data obfuscation in PostgreSQL is the archive’s post on the column side: masking and pseudonymisation for the roles (analytics, support, non-production copies) that need the row but not the identity in it, and the evidence is the policy catalogue: pg_policies for the tables that hold personal data, joined to the list of roles that can read them. A table of personal data with relrowsecurity = false and a SELECT grant to more than one role is this path, open.

PostgreSQL security path 5: the wire

The fifth path is the network: a connection that carries credentials and rows in clear text across a segment that someone else can observe, or a client that accepts any certificate and is redirected to a server that is not the database. The control is TLS with the server certificate verified by the client (sslmode=verify-full in the connection string, a certificate chain the client trusts, and hostssl rather than host in pg_hba.conf so that a plaintext connection is refused rather than merely discouraged).

The evidence is pg_stat_ssl, where every client backend should show ssl = true, and the pg_hba.conf rule type column, where any host line for a non-local address is a plaintext path left open.

PostgreSQL security path 6: the backup and the key

The sixth path bypasses the running server entirely: the base backup in an object-storage bucket, the WAL archive, the logical dump on a developer’s laptop, or the disk snapshot in the cloud account, any of which contains every row without the row-level security that protects it on the server.

The control is encryption at rest with keys the operator controls, on the storage (filesystem or volume encryption, or the managed service’s customer-managed keys) and on the backup tool’s output (pgBackRest and Barman both encrypt), together with a retention policy that deletes copies on a schedule and a key rotation procedure that has been rehearsed.

key management in PostgreSQL: why encryption depends on it is the archive’s post on the part most estates get wrong: the data is encrypted and the key is beside it, in the same bucket or the same host, so that the path is open to anyone who reaches either.

The post’s rule is that the key lives in a management service with its own audit log, the database and backup tools fetch it at start, and rotation is a scheduled event with a runbook. The evidence is the key service’s audit log showing the fetches, and a restore drill from an encrypted backup into an environment that does not hold the key, which should fail.

PostgreSQL security path 7: the change nobody can see

The seventh path is the absence of a record. A privilege granted at 2 a.m., a role created by an application account, a table exported by a departing engineer: without an audit trail, none of these is a security event, because none is visible.

The control is auditing at the statement level for the events that matter (pgaudit for role, grant and DDL statements and for reads of sensitive tables, log_connections and log_disconnections always) with the log shipped off the host to a store that the database’s own roles cannot alter. The evidence is the audit log itself: a review asks for the last ten GRANT statements and the last ten reads of the most sensitive table, and a stack that cannot produce them has this path open.

PostgreSQL 18 consulting: the top 25 features is in this archive for its security items: the release’s changes to authentication (OAuth support), to the predefined roles and to logging move several of the controls above from extensions into core, and an estate planning its upgrade should read the security items as the reason to schedule it.

The seven PostgreSQL security paths, side by side

The paths are ordered by how often they are used, which is also the order a review works them; the sixth and seventh are the ones most often found open on an estate that passed its questionnaire.

Running a PostgreSQL security review from the threat model

A review is the seven queries, run read-only by a monitoring role on every instance in the estate, with the results filed as the evidence pack. The findings are the paths the queries show open, ranked by the order above, and each finding’s fix is a change procedure with a verification query (the same one that found it), a validation query after, and a rollback.

The blast radius of a security change is different from a performance change and is stated in the procedure: closing path 1 can lock out a service whose credential was not rotated in time, closing path 5 can refuse a client that did not have a certificate, and closing path 4 can hide rows an application legitimately needed. Every one of these is staged: the new rule beside the old, the old removed after the log shows nothing is using it.

The review is repeated on a calendar, because the paths reopen: a migration creates a superuser connection “temporarily”, a new reporting tool connects with md5, a new table of personal data is created without a policy. The queries scheduled as a nightly job, with a diff against the previous night, catch the reopening the day it happens rather than at the next annual review.

Version notes: SCRAM became the default in PostgreSQL 14, the CREATEROLE semantics and the predefined role changes are 16, the security items referenced above (OAuth authentication among them) are 18; pgaudit is an extension with its own version per major release, and managed services expose a subset of the settings named here. Confirm the running version and edition before applying any control from an archive post, stage every change as described, and keep the DR posture in view: an encrypted backup whose key has been rotated without a rehearsed restore is the seventh path’s cousin, a change nobody tested.

Where this PostgreSQL security archive sits

This archive is the security layer of the PostgreSQL archive, whose operations calendar carries the quarterly security review as an entry, and it draws on the PostgreSQL backup archive for path six, the observability archive for the log prefix and shipping that path seven depends on, and the DBaaS archive for which of the controls a managed service takes out of the operator’s hands. The authoritative reference for the authentication methods and rule types named here is the client authentication chapter of the PostgreSQL documentation.

For a PostgreSQL security review run as the seven queries with findings ranked by path, an authentication migration to a directory or certificates staged without locking out a service, or 24×7 support in which the nightly diff of the seven queries is watched by the on-call engineer, the MinervaDB database consulting practice runs this threat model, and states for every recommendation which path it closes and what evidence proves it.