PostgreSQL Security Hardening: A Practitioner's Checklist for PostgreSQL 14-18

Manosh Malai
Mar 17, 2025
18
Mins to Read
All
PostgreSQL Security Hardening
PostgreSQL Security Hardening

A default PostgreSQL cluster is not a hardened one. ssl ships off, a bare initdb configures trust for local connections, and any md5 password that survived an upgrade from PostgreSQL 13 or earlier now sits on a documented removal path.

Hardening PostgreSQL means settling seven controls, in the order an audit asks for them:

  1. Authentication method — scram-sha-256, not md5 (password_encryption)
  2. Host-based access — explicit hostssl rules and a final reject (pg_hba.conf)
  3. Transport encryption — ssl = on on the server, sslmode=verify-full on the client
  4. Roles and privileges — predefined roles before object grants, object grants before superuser
  5. Row-level policy — ENABLE ROW LEVEL SECURITY, plus FORCE for table owners
  6. Audit trail — pgaudit, with log_connections and log_statement beneath it
  7. Patch level — the current minor release of a supported major

Everything below is verified against PostgreSQL 18.6 and the supported majors 14–18 as of September 2026, with the release named wherever a default or a deprecation is version-dependent. Where PostgreSQL documents no mechanism for something - password complexity, transparent data encryption — this guide says so rather than implying one exists.

Environment & version compatibilityVerified against: PostgreSQL 18.6 (released 13 August 2026).
Applies to: PostgreSQL 14, 15, 16, 17, 18 — every major in support as of September 2026.
Extensions: pgaudit matched to the server major — 18.X for PostgreSQL 18, 17.X for 17, 16.X for 16, 1.7.X for 15, 1.6.X for 14. pgcrypto and passwordcheck ship with PostgreSQL as contrib modules.
Platforms: community PostgreSQL from source or PGDG packages. Managed platforms (RDS, Aurora, Cloud SQL, Azure Database) restrict shared_preload_libraries and superuser, so the pgaudit and OS-level items differ — check your provider’s parameter group.
You will need: superuser, or a role able to edit postgresql.conf and pg_hba.conf and reload the server.
Deprecation notice — dated September 2026
  • md5 password authentication is deprecated as of PostgreSQL 18. Not removed — deprecated, with removal committed to a future major release. Treat it as migration debt with a deadline.
  • clientcert=1, clientcert=0 and clientcert=no-verify were removed in PostgreSQL 14. Only verify-ca and verify-full are accepted in pg_hba.conf. If you have clientcert=1 in a config sample, that is broken config, not merely dated.
  • PostgreSQL 13 and earlier are end-of-life and receive no security fixes.
  • PostgreSQL 14 reaches end-of-life on 12 November 2026 — within a quarter of this revision. If you are on 14, the upgrade is now the security item.
  • ssl_ecdh_curve was renamed ssl_groups in PostgreSQL 18; the previous name still works.
PostgreSQL Security

Common security vulnerabilities in PostgreSQL databases

‍

‍

PostgreSQL Database Vulnerabilities

Understanding the database vulnerabilities is the first step towards security. Some of the common vulnerabilities are as follows.

1. Weak authentication

Authentication is where most PostgreSQL exposure starts, and the default depends on how the cluster was created. A cluster initialised by a bare initdb with no -A/--auth option is configured for trust, which the documentation warns “allows any local user to connect as any PostgreSQL user, including the database superuser.” Packaged installations differ: on Debian and Ubuntu, pg_createcluster rewrites the generated pg_hba.conf to use peer for Unix-socket connections and md5 for TCP — and that md5 default is itself now a finding. Check pg_hba.conf on your own cluster rather than assuming either default.

PostgreSQL also has no built-in password-complexity mechanism. passwordcheck is a contrib module, and its own documentation is candid about the limitation: because clients commonly pre-encrypt passwords before sending them, it “can only try to guess the password,” and it is “not recommended if your security requirements are high.” VALID UNTIL expires a password, not a role, and “the expiration time is not enforced when logging in using a non-password-based authentication method” — so cert, GSSAPI, peer and LDAP logins bypass it silently.

Another common issue is the broad range of IP addresses allowed to connect to the database. If the database is accessible from any IP address, this increases the attack surface and provides potential entry points for attackers.

2. Unencrypted connections

PostgreSQL does not enable TLS out of the box: the ssl parameter defaults to off, and the server needs server.crt and server.key present before it will start in SSL mode. Distribution packages may differ — on Debian and Ubuntu, pg_createcluster enables ssl and points it at the shared snakeoil certificate, but only when that certificate exists and the postgres user is in the ssl-cert group.

Enabling the server is also only half the job. libpq’s sslmode defaults to prefer, which tries TLS and then silently falls back to plaintext. See transport encryption below for what each sslmode value actually buys you.

3. Excess user privilege

Granting excessive privileges to database users is a common mistake that can lead to serious security vulnerabilities and unexpected issues. For instance, giving superuser privileges to a user for routine tasks like running a vacuum job via cron can expose the database to unnecessary risks — and since PostgreSQL 17 there is a purpose-built answer to exactly that case, covered in the roles section.

4. Unpatched vulnerabilities

PostgreSQL ships security fixes in minor releases, and the volume is not trivial: the 13 August 2026 set — 18.6, 17.11, 16.15, 15.19, 14.24 — closed 28 vulnerabilities, several of them arbitrary-code-execution at CVSS 8.8. Minor upgrades are in place and do not require a dump/restore, so there is rarely a technical reason to defer one.

Major versions are supported for five years. PostgreSQL 13 and earlier are end-of-life, and PostgreSQL 14 reaches end-of-life on 12 November 2026. (Version state as of September 2026.) Major-version upgrades are a different exercise — see planning a zero-downtime major-version upgrade for the mechanics; this guide covers minor-version cadence and CVE tracking.

5. Insecure backups

Unencrypted backup files or backups stored in unprotected locations can be easily accessed and exploited. Four controls, none of which are the backup tool’s job:

  • Permissions on the target. A base backup contains pg_authid, so it contains every password hash in the cluster. 0700 on the directory, 0600 on the files, owned by the backup role — not the application role.
  • Encrypt the backup, not the cluster. PostgreSQL has no transparent data encryption (see encryption at rest), so an unencrypted dump on shared storage is a complete copy of your data with none of the database’s access controls attached.
  • Separate the backup credential. The role that takes backups needs pg_read_all_data or replication rights and nothing else; it should not be the role your application connects as.
  • Restore-side exposure. A restored dump carries the source cluster’s role grants. A production dump restored into staging reproduces production’s privilege model in an environment with weaker access controls — which is how staging becomes the easiest route into production data.

For the backup and recovery mechanics themselves — pg_basebackup, pgBackRest, WAL archiving and point-in-time recovery — see PostgreSQL backup and point-in-time recovery with pgBackRest and WAL archiving.

Factors leading to PostgreSQL Vulnerability

Configuring secure authentication and access controls in PostgreSQL

‍

PostgreSQL Authentication and access control

PostgreSQL offers various authentication methods that can be configured to suit our security requirements. The most common authentication methods include:

Password authentication — use scram-sha-256

It has been the password_encryption default since PostgreSQL 14, and the documentation calls it “the most secure of the currently provided methods.”

md5 is not merely weaker.

Update as of September, 2026In version 18, md5 password authentication is deprecated: “Support for MD5-encrypted passwords is deprecated and will be removed in a future release of PostgreSQL.” CREATE ROLE and ALTER ROLE now emit deprecation warnings when an md5 password is set, suppressible with md5_password_warnings = off (boolean, default on). PostgreSQL 19 additionally warns on every successful md5 authentication.

There is also a live CVE against it: CVE-2026-6478, “PostgreSQL discloses MD5-hashed passwords via covert timing channel” (CVSS 6.5), fixed on 14 May 2026 in 18.4, 17.10, 16.14, 15.18 and 14.23. Its own description scopes the exposure precisely: “This does not affect scram-sha-256 passwords, the default in all supported releases. However, current databases may have MD5-hashed passwords originating in upgrades from PostgreSQL 13 or earlier.”

So treat any remaining md5 line in pg_hba.conf, or any md5-format password in pg_authid, as migration debt with a deadline rather than a supported alternative.

Find what you actually have:

-- Which roles still have an md5-format password?
SELECT rolname,
       CASE
         WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram-sha-256'
         WHEN rolpassword LIKE 'md5%'            THEN 'md5'
         WHEN rolpassword IS NULL                THEN '(none)'
         ELSE '(other)'
       END AS stored_format
FROM pg_authid
ORDER BY stored_format, rolname;

-- What is the server configured to write for new passwords?
SHOW password_encryption;

pg_authid “must not be publicly readable” — the publicly readable pg_roles view blanks the password field — so run this as a superuser or a role explicitly granted SELECT on the catalogue. A NULL password is a finding in its own right. And the stored prefix reflects what was stored, not what password_encryption says today: a password supplied already in md5 format is stored as-is regardless of the setting, which is exactly why the audit is worth running.

Then migrate, in this order:

-- 1. Change what the server writes for NEW passwords. Takes effect on reload;
--    no restart required.
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

-- 2. Have every affected user set a new password. This is what re-hashes it;
--    the server cannot convert an md5 hash to SCRAM, because it never had
--    the cleartext.
ALTER ROLE app_user PASSWORD 'new-secret-here';

-- 3. Only now change the method in pg_hba.conf, then reload.
--    host  all  all  10.0.0.0/8  scram-sha-256

The order matters, and getting it wrong locks everyone out. Step 2 before step 3 is safe because of a documented convenience: “if md5 is specified as a method in pg_hba.conf but the user’s password on the server is encrypted for SCRAM … then SCRAM-based authentication will automatically be chosen instead.” Step 3 before step 2 is not safe — the method demands SCRAM and the stored hash cannot supply it. Note also that step 1 alone changes nothing about existing passwords: without step 2 every md5 hash in pg_authid survives untouched.

Confirm every client library in use supports SCRAM before you start. That is the one dependency this migration has, and older drivers are where it bites.

SAFE ORDER 1 password_encryption = scram-sha-256 2 new passwords 3 pg_hba.conf scram-sha-256 MIGRATED REVERSE ORDER 1 pg_hba.conf scram-sha-256 stored password still md5 authentication fails every user locked out md5 method + SCRAM-stored password authenticates via SCRAM automatically — which is why step 2 is safe before step 3.
How a cluster moves from md5 to scram-sha-256 — and why changing pg_hba.conf before the stored passwords locks every user out.
Note: scram_iterations (integer, default 4096, added in PostgreSQL 16) raises the SCRAM work factor. If you change it, patch first: CVE-2026-14672 — “observable response discrepancy with non-default scram_iterations provides user existence oracle” — affects 16, 17 and 18 and was fixed in 18.6, 17.11 and 16.15. The iteration count is fixed at the time of encryption, so a changed value only applies to passwords set afterwards.

Host-based access control: pg_hba.conf

PostgreSQL uses the pg_hba.conf file to control which clients can connect to the database and which authentication methods should be used. This file is critical for defining access controls and preventing unauthorized access.

  • Restricting access by IP: ensure that only trusted IP addresses are allowed to connect to the database, reducing the risk of unauthorized access from unknown sources.
  • Using host and network authentication: specify the host, database, user, and IP address ranges that are allowed to connect. Avoid wildcard entries such as 0.0.0.0/0, all databases, all users, which would allow connections from any IP address to any database.

Which is the advice this section has always given, without ever showing the file. Here it is. Rules are evaluated top to bottom and the first match wins — a later, tighter rule never overrides an earlier, looser one:

# TYPE   DATABASE   USER              ADDRESS          METHOD          OPTIONS
# ---------------------------------------------------------------------------
# 1. Local superuser access over the Unix socket, OS-authenticated.
local    all        postgres                           peer

# 2. Application traffic: TLS required, SCRAM, scoped to one CIDR range.
hostssl  appdb      app_user          10.20.0.0/16     scram-sha-256

# 3. Replication, TLS + client certificate as well as SCRAM.
hostssl  replication  replicator      10.20.1.0/24     scram-sha-256   clientcert=verify-full

# 4. Read-only analytics, separate range, no write path.
hostssl  appdb      analyst           10.30.0.0/16     scram-sha-256

# 5. Anything else is refused explicitly, and the refusal is logged.
host     all        all               0.0.0.0/0        reject
host     all        all               ::0/0            reject

Four things in that file are the whole point of it:

  • hostssl, not host. hostssl “only matches connection attempts made using TCP/IP … made with SSL encryption.” A host line accepts plaintext. And note the dependency: if ssl is off in postgresql.conf, a hostssl record “is ignored except for logging a warning that it cannot match any connections” — so the line silently stops protecting anything.
  • The explicit reject at the end. PostgreSQL already denies unmatched connections, but an explicit reject documents the intent and makes the refusal unambiguous in the log.
  • clientcert=verify-full requires the client to present a trusted certificate and enforces that its Common Name matches the username. It pairs certificate verification with any method that supports hostssl entries.
  • replication does not cover logical replication. The documentation is explicit: the value “matches if a physical replication connection is requested, however, it doesn’t match with logical replication connections.” Logical replication connections specify a database, so they match your ordinary hostssl appdb … rules. Note also that replicator without a leading + matches only that role, not roles that are members of it.
Update as of September, 2026: In version 14, the clientcert values 1, 0 and no-verify were removed. Only verify-ca and verify-full are accepted, and verify-ca is additionally disallowed when the method is cert, which requires verify-full checking.

No wildcards in ADDRESS except on the reject lines — 0.0.0.0/0 with a permissive method is the single most common finding in a PostgreSQL security audit. Reload rather than restart after editing: SELECT pg_reload_conf(); or pg_ctl reload. And listen_addresses decides whether the server is on the network at all — see connection and authentication parameters (listen_addresses, max_connections) for how those interact with connection limits.

Transport encryption: TLS for PostgreSQL connections

SSL (Secure Sockets Layer) and TLS (Transport Layer Security) are essential for securing data transmission between PostgreSQL clients and servers. By enabling SSL, you ensure that data is encrypted, preventing interception and man-in-the-middle attacks.

The protocol-generation naming is historical: SSL 2 and 3 are “always disabled” in PostgreSQL, and the parameter that sets the floor is ssl_min_protocol_version.

Update as of September, 2026: In version 13, the default ssl_min_protocol_version changed from TLSv1 to TLSv1.2, which the documentation describes as satisfying “industry best practices as of this writing.” In version 17, sslnegotiation=direct became available, removing a network round-trip (it requires ALPN and a PostgreSQL 17-or-later server). In version 18, ssl_ecdh_curve was renamed ssl_groups and accepts multiple colon-separated curves; the previous name still works.

Why transport encryption matters

  • Data confidentiality: TLS ensures that sensitive data, such as personal information and credentials, is encrypted during transmission.
  • Data integrity: TLS prevents data tampering during transit.
  • Authentication: certificates verify the identities of both the server and client, protecting against man-in-the-middle attacks — but only in the sslmode values that actually check them. That is the part most configurations get wrong.
PostgreSQL SSL Encryption

‍

Configuring it — server, then pg_hba, then client

Enable it on the server (postgresql.conf):

ssl = on                                  # default is off
ssl_cert_file = '/etc/ssl/certs/pg.crt'   # default: server.crt in the data directory
ssl_key_file  = '/etc/ssl/private/pg.key' # default: server.key, must be 0600 and owned by the
                                          # OS user the server runs as
ssl_ca_file   = '/etc/ssl/certs/ca.crt'   # needed for clientcert verification
ssl_min_protocol_version = 'TLSv1.2'      # default since PostgreSQL 13

ssl can only be set in postgresql.conf or on the command line, and enabling it requires a reload. There is no server parameter that rejects non-TLS connections — ssl grants the capability, pg_hba.conf enforces the requirement, which is why hostssl matters.

Then fix the client, because the server cannot. This is where the section has always stopped and where the exposure actually sits:

postgresql://app_user@db.internal:5432/appdb?sslmode=verify-full&sslrootcert=/etc/ssl/certs/ca.crt

sslmode defaults to prefer, which tries TLS and then silently falls back to plaintext. And sslmode=require — the value most often used and most often believed to be sufficient — gives no protection against a man-in-the-middle at all. These ratings are PostgreSQL’s own, from its SSL Mode Descriptions table:

sslmode Eavesdropping protection MITM protection The documentation’s own gloss
disable No No “I don’t care about security, and I don’t want to pay the overhead of encryption.”
allow Maybe No “I don’t care about security, but I will pay the overhead of encryption if the server insists on it.”
prefer (default) Maybe No “I don’t care about encryption, but I wish to pay the overhead of encryption if the server supports it.”
require Yes No “I trust that the network will make sure I always connect to the server I want.”
verify-ca Yes Depends on CA policy “I want to be sure that I connect to a server that I trust.”
verify-full Yes Yes “…and that it’s the one I specify.”
SSLMODE disable allow prefer DEFAULT require verify-ca verify-full EAVESDROP no maybe maybe yes yes yes MITM no no no no CA policy yes STOPPED Ratings are PostgreSQL's own SSL Mode Descriptions table. sslmode=require encrypts but does not authenticate the server.
What each sslmode value actually protects against, using PostgreSQL's own SSL Mode Descriptions ratings. Only verify-full stops a man-in-the-middle.

Three traps worth naming:

  • require behaves like verify-ca if a root CA file happens to exist, for backwards compatibility. The documentation calls relying on that “discouraged” — “applications that need certificate validation should always use verify-ca or verify-full.”
  • sslrootcert=system (PostgreSQL 16+) loads the system trust store — and silently forces sslmode=verify-full, erroring on anything weaker, because “it is trivial for anyone to obtain a certificate trusted by the system for a hostname they control, rendering verify-ca and all weaker modes useless.”
  • sslmode is ignored for Unix-domain-socket connections, and if GSSAPI encryption is available it is used in preference to TLS regardless of sslmode. Set gssencmode=disable if you need TLS specifically.

The same work on MySQL uses different syntax and the same reasoning — see the equivalent SSL/TLS enforcement work on MySQL if you run a mixed estate.

Best practices for role management and privilege assignment

Effective role management and privilege assignment are essential for securing your PostgreSQL database and ensuring that users can only perform actions relevant to their responsibilities. If you want this reviewed rather than self-assessed, 24/7 PostgreSQL support for role and privilege audits covers exactly that.

1. Use predefined roles for common tasks

PostgreSQL ships 16 predefined roles that delegate specific privileged capabilities without granting superuser, so most administrative access needs no custom role at all. (That overview was written against PostgreSQL 14–16; the additions since then are listed below.) If you want the whole role and privilege map produced for you, a PostgreSQL security audit against the CIS Benchmark includes superuser-sprawl detection and a least-privilege redesign.

Example: for a monitoring tool user, instead of granting superuser access, use pg_monitor:

GRANT pg_monitor TO monitor_user;

Know what that actually grants. pg_monitor “is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables” — three roles, and not pg_read_all_data. So it reads configuration and every pg_stat_* view, and it does not read your table contents. That is the right shape for a monitoring agent.

Update as of September, 2026: Four predefined roles postdate most PostgreSQL security guides. pg_checkpoint (PostgreSQL 15) allows running CHECKPOINT. pg_create_subscription and pg_use_reserved_connections arrived in PostgreSQL 16. pg_maintain (PostgreSQL 17) allows VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX and LOCK TABLE on all relations — which is the correct answer to the vacuum-cron-job problem this article opened with, and it did not exist when the problem was described. pg_signal_autovacuum_worker arrived in PostgreSQL 18.

The documentation’s own caution applies: “Care should be taken when granting these roles to ensure they are only used where needed and with the understanding that these roles grant access to privileged information.” pg_read_server_files, pg_write_server_files and pg_execute_server_program are effectively superuser-adjacent and belong to nobody by default.

2. Create custom roles based on requirement

While predefined roles are useful, sometimes it’s necessary to create custom roles for specific use cases. For example, read-only, developer, administrator.

Read-only role: a read-only role may have only SELECT permissions on specific tables or schemas, ensuring that users can view data without modifying it.

The obvious version of this breaks the next time anyone creates a table:

CREATE ROLE read_only NOLOGIN;

-- Without this the role cannot see the schema at all.
GRANT USAGE ON SCHEMA public TO read_only;

-- One-time: covers the tables that exist RIGHT NOW.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- This is the part that is usually missing: cover tables created LATER.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;

-- ...and repeat it per DDL-creating role. Default privileges are keyed to the
-- CREATING role and are NOT inherited through role membership:
--   "new object permissions are only affected by the default privileges of the
--    current role, and are not inherited from any roles in which the current
--    role is a member."
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT SELECT ON TABLES TO read_only;

-- Give humans and services a LOGIN role that inherits the group. IN ROLE creates
-- the membership with INHERIT and SET enabled and ADMIN disabled.
CREATE ROLE analyst LOGIN PASSWORD 'set-a-real-one' IN ROLE read_only;

‍

GRANT ... ON ALL TABLES IN SCHEMA is a one-time operation over the tables that exist when it runs. Without the ALTER DEFAULT PRIVILEGES lines the role appears to work and then silently stops seeing anything created afterwards — which is the failure mode that gets diagnosed as “the report is missing rows” three months later.

Two details on that last statement. NOLOGIN is already the CREATE ROLE default, so the keyword above is documentation rather than a change — but note the documented exception: it is not the default “when CREATE ROLE is invoked through its alternative spelling CREATE USER,” so CREATE USER read_only would silently produce a login-capable role. And in PostgreSQL 16 inheritance became a property of each membership rather than a role-level attribute — on 14 and 15, NOINHERIT on the role governs every membership it has.

There is a predefined alternative worth knowing: pg_read_all_data allows reading all data (tables, views, sequences) “as if having SELECT rights on those objects and USAGE rights on all schemas” — with no maintenance. Keep the “as if having”: it grants no ACL entry, so an auditor inspecting relacl on the tables sees nothing, which is precisely why the role exists. But read its documented caveat before using it: “This role does not bypass row-level security (RLS) policies.” If RLS is in play, pg_read_all_data is not a way around it, and that is deliberate.

Update as of September, 2026: In version 15, CREATE on the public schema is no longer granted to PUBLIC, and the schema is owned by pg_database_owner. But this applies only to new clusters and newly-created databases — “Upgrading a cluster or restoring a database dump will preserve public’s existing permissions.” So a PostgreSQL 18 cluster that started life on 13 or earlier still has the old permissive grant. Check, do not assume: \dn+ public in psql, or SELECT nspacl FROM pg_namespace WHERE nspname = 'public'; — a NULL nspacl means defaults apply, not “nothing granted” — and REVOKE CREATE ON SCHEMA public FROM PUBLIC; if it is still there.

Admin role: an admin role can be granted more extensive privileges for tasks like DDL execution or user management, but avoid giving superuser access unless it’s necessary. Note that CREATEROLE is not a mild privilege — it determines “whether a role will be permitted to create, alter, drop, comment on, and change the security label for other roles,” so it is a path to broader access rather than a bounded administrative capability.

3. Assign privileges using the principle of least privilege

By following the principle of least privilege, always assign the minimum privileges necessary for users to perform their roles effectively. For example, avoid granting unnecessary UPDATE, DELETE, or ALTER permissions unless the user requires them to perform their job.

In practice that is a three-step decision, and it is worth taking in order rather than reaching for superuser because it is quicker:

A TASK NEEDS PRIVILEGE predefined role pg_monitor · pg_maintain · pg_read_all_data TRY FIRST grant on the object GRANT SELECT, MAINTAIN ON … TO role THEN superuser always bypasses row-level security LAST RESORT The article's own example — superuser for a vacuum cron job — belongs on tier 1: pg_maintain, added in PostgreSQL 17.
Work down the ladder, not up: a predefined role first, an object grant next, superuser only when nothing else will do.
The task Wrong answer Right answer
A cron job that vacuums SUPERUSER pg_maintain (PostgreSQL 17), or GRANT MAINTAIN ON the specific tables
A monitoring agent SUPERUSER pg_monitor
A reporting user, no RLS Ad-hoc grants that go stale pg_read_all_data, or a group role with ALTER DEFAULT PRIVILEGES
A reporting user, RLS in play pg_read_all_data Explicit GRANT SELECT plus a policy — pg_read_all_data does not bypass RLS, and BYPASSRLS should be a deliberate decision
Terminating a stuck session SUPERUSER pg_signal_backend — note it “does not permit signaling backends owned by a superuser”
Role Management and Privilage Assignment in PostgreSQL

Running this model across a whole fleet rather than one cluster is its own problem — least-privilege access and CVE hardening across a multi-tenant SaaS fleet is the shape it takes at scale.

Row-level security

Roles decide which tables a user can touch. Row-level security decides which rows. If you have a multi-tenant schema, or a table where one team may see only its own records, this is the control that enforces it in the database rather than in every query in the application.

Policies are created with CREATE POLICY, altered with ALTER POLICY and dropped with DROP POLICY; enabling and disabling row security is done with ALTER TABLE. By default “tables do not have any policies, so that if a user has access privileges to a table according to the SQL privilege system, all rows within it are equally available.”

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- The owner bypasses policies unless you say otherwise. This is the line
-- most RLS deployments are missing.
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

Two things about who RLS does not apply to, and the distinction matters:

  • Table owners normally bypass row security. FORCE ROW LEVEL SECURITY is what subjects them to it: “If enabled, row-level security policies will be applied when the user is the table owner. If disabled (the default) then row-level security will not be applied when the user is the table owner.”
  • FORCE does not reach superusers. “Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table.” Always — there is no setting that changes it. If your threat model includes the superuser, RLS is not the control for it; the control is not having a superuser connection in that path.

Also: enabling row security and adding policies “is always the privilege of the table owner only”, and USING clauses run as part of the query, so a policy that calls an expensive function runs it per row.

Patch level is part of thisCVE-2026-14666 — “Row security caching disregards role modifications” (CVSS 4.2) — was fixed on 13 August 2026. If you are relying on RLS for tenant isolation, be on a minor release at or after that set.

RLS is a different mechanism from view-owner privileges. If your question is whether a view runs with the permissions of its owner or its caller, that is SECURITY DEFINER vs security_invoker views in PostgreSQL 15, which is a separate control and covered separately.

Encryption at rest

PostgreSQL core does not offer transparent data encryption. That is worth stating plainly, because it is the most common false assumption in a PostgreSQL security review.

The documentation’s own Encryption Options chapter enumerates what PostgreSQL considers available, and there are six entries: password encryption, encryption for specific columns, data partition encryption, encrypting data across a network, SSL host authentication, and client-side encryption. There is no TDE entry. Full-volume encryption appears in that list as an operating-system facility, not a PostgreSQL feature: “Linux file system encryption options include eCryptfs and EncFS, while FreeBSD uses PEFS. Block level or full disk encryption options include dm-crypt + LUKS on Linux and GEOM modules geli and gbde on FreeBSD.”

So your options are three, and they protect against different things:

Option Protects against Does not protect against
Volume / filesystem encryption
LUKS, dm-crypt, eCryptfs, geli
A stolen physical disk or a decommissioned storage host Anything while the filesystem is mounted — “when mounted, the operating system provides an unencrypted view of the data”
pgcrypto column encryption A database dump, a rogue SELECT on the ciphertext column The key, if it travels in the SQL statement string
Client-side encryption Both of the above, plus the database administrator (DBA) Anything the application then does with the plaintext in application memory

pgcrypto provides hashing (digest(), hmac()), password hashing (crypt(), gen_salt()), PGP symmetric and public-key encryption, raw encryption functions and gen_random_bytes(). Read its documented Security Limitations before you build on it:

pgcrypto — Security Limitations, verbatim“All pgcrypto functions run inside the database server. That means that all the data and passwords move between pgcrypto and client applications in clear text. Thus you must: 1. Connect locally or use SSL connections. 2. Trust both system and database administrator. If you cannot, then better do crypto inside client application.”
“The implementation does not resist side-channel attacks.”

The practical consequence, which the documentation states in the Encryption Options chapter rather than the pgcrypto page: “The decrypted data and the decryption key are present on the server for a brief time while it is being decrypted and communicated between the client and server. This presents a brief moment where the data and keys can be intercepted by someone with complete access to the database server, such as the system administrator.” If your key is passed as a literal in the statement, then log_statement, pg_stat_activity and your application’s own query logs are all places it can surface. Pass it as a bound parameter and keep log_statement off all on any cluster where that happens.

Patch level, againCVE-2026-14663 — “pgcrypto, for OpenSSL-disabled ciphers, silently encrypts to and decrypts from cleartext” (CVSS 6.5) — was fixed on 13 August 2026. Silently writing cleartext where you expected ciphertext is the worst possible failure mode for an encryption function, so this one is not optional.

Where a schema cannot be re-architected around encryption, masking the sensitive columns at the proxy layer is the other route — open-source data masking and redaction across 20 PostgreSQL and MySQL nodes at an Indian bank is what that looks like in a regulated environment. And for how the same problem is solved with engine-native TDE elsewhere, see how the same problem is solved on MySQL — MySQL’s InnoDB keyring is a genuinely different model, and the contrast is the useful part.

Protecting PostgreSQL from SQL injection and common threats

SQL Injection occurs when an attacker injects malicious SQL code into an application’s input fields, which is then executed by the database. To protect your PostgreSQL database from SQL injection attacks, it’s important to implement various best practices and defensive techniques.

Pass values as parameters, not as statement text

The primary defence is to pass values as parameters rather than concatenating them into statement text, so the value is never parsed as SQL. In PostgreSQL that means the extended query protocol — PQexecParams in libpq, or your driver’s parameter binding — with positional placeholders $1, $2. The documentation is explicit: “it is neither necessary nor correct to do escaping when a data value is passed as a separate parameter in PQexecParams or its sibling routines.”

SQL-level PREPARE/EXECUTE also parameterises, but do not treat it as the control. The documentation presents it as a planning optimisation — generic versus custom plans, plan_cache_mode — and prepared statements “only last for the duration of the current database session.” Many drivers and connection poolers bind parameters without ever issuing PREPARE.

And parameters substitute values only. Identifiers — table names, column names, ORDER BY targets — and structural SQL cannot be parameterised, which is where injection survives in otherwise-parameterised codebases. Those need format() with %I or quote_ident():

-- WRONG: the sort column is concatenated, so it is parsed as SQL.
--   'SELECT * FROM orders WHERE tenant_id = $1 ORDER BY ' || sort_col

-- RIGHT: %I "treats the argument value as an SQL identifier, double-quoting
-- it if necessary", and the value stays a parameter.
CREATE FUNCTION list_orders(p_tenant bigint, p_sort text)
RETURNS SETOF orders LANGUAGE plpgsql AS $$
BEGIN
  IF p_sort NOT IN ('created_at', 'total', 'status') THEN
    RAISE EXCEPTION 'unsupported sort column: %', p_sort;   -- allowlist first
  END IF;
  RETURN QUERY EXECUTE
    format('SELECT * FROM orders WHERE tenant_id = $1 ORDER BY %I', p_sort)
    USING p_tenant;
END $$;

The allowlist is not belt-and-braces — it is the actual control. %I makes an arbitrary string a valid identifier; it does not make it an authorised one, and it does not check that the column exists — a bad value raises a runtime error rather than falling back safely. A sort direction cannot go through %I at all, so ASC/DESC needs the same allowlist treatment.

The server is part of the injection story tooCVE-2026-15741 — “Expression deparse allows SQL injection via EXTRACT argument” (CVSS 8.8) — was fixed on 13 August 2026. Parameter binding in the application does not help against a server-side deparse bug.

Input validation and sanitization

Ensure that user inputs are strictly validated before they are used in SQL queries. This helps to prevent malicious input from affecting the database.

  • Type checking: let the database do it. Pass a parameter with its declared type and PostgreSQL rejects a malformed value before it reaches the plan — which is stronger than an application-side check, because it cannot be forgotten on one code path.
  • Sanitize inputs: for inputs that must allow special characters (like search queries), validate them against what the feature actually needs rather than trying to strip SQL metacharacters. With parameter binding in place, escaping is not the mechanism keeping you safe, and hand-rolled escaping is a common source of the bug it is meant to prevent.

Limit database permissions

Even if SQL injection is successful, limiting the privileges of the database user can reduce the potential damage. Make sure that each application or user has only the necessary privileges to perform its required tasks. Concretely:

-- The application role owns nothing and can create nothing.
CREATE ROLE app_user LOGIN PASSWORD 'set-a-real-one'
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;

-- Tables are owned by a separate migration role, so the application cannot
-- ALTER or DROP what it reads and writes.
ALTER TABLE orders OWNER TO migrator;
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;

-- Audit the file and program paths. Do NOT "REVOKE ... FROM PUBLIC" here:
-- pg_read_file, pg_ls_dir and friends are "restricted to superusers by
-- default", so a REVOKE from PUBLIC removes nothing. The risk runs the other
-- way -- someone GRANTing them, or handing out pg_read_server_files.
SELECT p.proname, pg_get_function_arguments(p.oid) AS args, p.proacl
FROM   pg_proc p
WHERE  p.proname IN ('pg_read_file', 'pg_read_binary_file',
                     'pg_ls_dir', 'pg_stat_file')
  AND  p.proacl IS NOT NULL;          -- NULL proacl = defaults, i.e. nothing granted

SELECT r.rolname AS member
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_read_server_files', 'pg_write_server_files',
                     'pg_execute_server_program');

Note the overload trap if you ever do need to grant or revoke one of these: the optional parameters in the documentation are separate physical functions, and “privilege must be granted separately on each such function.” pg_read_file is four of them.

Separating table ownership from table access is the highest-leverage item here, with three conditions that have to hold or the control is void:

  • Only the table owner, the schema owner and a superuser can drop a table. So the schema must not be owned by the application role either — ownership separation covers the schema, not just the table.
  • Role membership defeats it. If app_user is a member of migrator, directly or indirectly, it can act as the owner. Audit pg_auth_members, not just the grants.
  • It stops destructive DDL, not data tampering. With INSERT and UPDATE granted, an injected UPDATE orders SET ... still succeeds. DELETE and TRUNCATE fail here only because neither was granted — and TRUNCATE is separately grantable, so a later convenience grant silently reopens it.

Monitoring, logging, and regular updates for enhanced security

Enable comprehensive logging

PostgreSQL provides powerful logging features that allow you to track database activity. By enabling detailed logging, you can monitor queries, user activities, and any abnormal events that could indicate security issues.

Statement logging. log_statement takes none (the default), ddl, mod or all. For an audit trail ddl is the usual floor — it logs CREATE, ALTER and DROP — and all is rarely survivable on a busy cluster. log_duration and log_disconnections are both boolean and both default off. Prefer log_min_duration_statement (default -1, disabled) over log_duration if you want durations without a line per statement.

Update as of September, 2026: In version 18, log_connections is no longer boolean. It is a string — “The default is the empty string, '', which disables all connection logging” — taking any of receipt, authentication, authorization, setup_durations or all, alone or comma-separated. The boolean values still work for backwards compatibility: “The positive values are equivalent to specifying the receipt, authentication, and authorization options.” Worth knowing either way: failed authentication is always logged regardless of this setting.
# postgresql.conf -- a defensible audit baseline
log_statement       = 'ddl'          # default: none
log_connections     = 'all'          # PostgreSQL 18+. On 14-17 use: on
log_disconnections  = on             # default: off
log_line_prefix     = '%m [%p] %q%u@%d from %h '   # default: '%m [%p] '
log_min_duration_statement = 1000    # default: -1 (disabled)
log_lock_waits      = on             # default: off; fires after deadlock_timeout
PostgreSQL Security with Logging

For the surrounding log-management concerns — rotation, destinations, the collector, retention — see PostgreSQL log configuration: log_connections, log_disconnections and log_statement. This guide owns the audit-trail layer above it.

Use pgaudit for enhanced auditing

For a defined audit trail, use the PostgreSQL Audit Extension — pgaudit, not pg_audit. The underscore matters: CREATE EXTENSION pg_audit; fails, and so does shared_preload_libraries = 'pg_audit'.

It “provides detailed session and/or object audit logging via the standard PostgreSQL logging facility.” Two setup requirements, both easy to miss:

# 1. postgresql.conf -- requires a RESTART, not a reload.
shared_preload_libraries = 'pgaudit'
-- 2. Then, per database, BEFORE setting pgaudit.log:
--    "CREATE EXTENSION pgaudit must be called before pgaudit.log is set to
--     ensure proper pgaudit functionality."
CREATE EXTENSION pgaudit;

ALTER SYSTEM SET pgaudit.log = 'ROLE, DDL, WRITE';
SELECT pg_reload_conf();

It logs nothing by default. pgaudit.log is what selects classes, and the full documented set is:

Class What it captures
READ SELECT and COPY when the source is a relation or a query
WRITE INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation
FUNCTION Function calls and DO procedural execution blocks
ROLE GRANT, REVOKE, CREATE ROLE, ALTER ROLE, DROP ROLE
DDL All Data Definition Language statements that are not in the ROLE class
MISC DISCARD, FETCH, CHECKPOINT, VACUUM, SET
MISC_SET Miscellaneous SET commands (e.g. SET ROLE)
ALL All of the above classes combined

ROLE is the class a compliance reviewer asks for first, because it is where privilege changes are recorded — and it is the one most often left out of a pgaudit.log value. Class names are matched case-insensitively, and the settings “may be modified only by a superuser.” Note what 'ROLE, DDL, WRITE' deliberately leaves out: READ. Nothing in that configuration records a SELECT, so read-based exfiltration is unaudited — a choice worth making knowingly rather than by omission. And READ on a busy OLTP cluster will generate more log volume than the cluster generates data, so scope it by object audit logging rather than enabling it session-wide.

Match the branch to your server. pgaudit is versioned against PostgreSQL, and a mismatched branch will not load:

Class What it captures
READ SELECT and COPY when the source is a relation or a query
WRITE INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation
FUNCTION Function calls and DO procedural execution blocks
ROLE GRANT, REVOKE, CREATE ROLE, ALTER ROLE, DROP ROLE
DDL All Data Definition Language statements that are not in the ROLE class
MISC DISCARD, FETCH, CHECKPOINT, VACUUM, SET
MISC_SET Miscellaneous SET commands (e.g. SET ROLE)
ALL All of the above classes combined

Mapping the controls to a compliance regime

Everything above is a configuration change. What an auditor asks for is evidence that the configuration is what you say it is, and that it has been that way. The two are not the same job, and the second one is where most PostgreSQL estates fail an audit despite being configured correctly.

CONTROL WHERE AN AUDITOR LOOKS authentication method host-based access transport encryption roles and privileges audit trail patch level CIS Benchmark one per major, PostgreSQL 14–18 PCI-DSS access, encryption, logging SOC 2 evidence, not configuration DISA's current PostgreSQL STIGs are vendor-specific (Crunchy Data). There is none for community PostgreSQL 17 or 18.
Every control in this guide feeds the same three places an auditor looks for evidence.
Control in this guide CIS PostgreSQL Benchmark PCI-DSS SOC 2
password_encryption = scram-sha-256, no md5 in pg_hba.conf Authentication section Strong cryptography for authentication credentials Logical access controls
pg_hba.conf scoped to CIDR ranges, explicit reject Connection and login section Restrict access by business need to know Logical access controls
ssl = on, hostssl, client sslmode=verify-full TLS section Encrypt transmission across open networks Confidentiality
Predefined roles, no superuser sprawl, separate table ownership Access-control section Least privilege, unique IDs Logical access controls
Row-level security with FORCE — (application-level) Restrict access to cardholder data Confidentiality
pgaudit.log including ROLE, log_line_prefix with %u %d %h Logging and auditing section Track and monitor all access Monitoring, change management
Current minor release of a supported major Patch section Protect against known vulnerabilities Change management, risk

The benchmark landscape, stated precisely — because it is easy to get wrong:

  • CIS publishes a PostgreSQL Benchmark for every major currently in support: PostgreSQL 18 at v1.0.0, 17 at v1.1.0, 16 at v1.1.0, 15 at v1.2.0, 14 at v1.3.0. If you need a control list someone else will recognise, this is it.
  • DISA’s current PostgreSQL STIGs are vendor-specific, not community PostgreSQL. The current documents are the Crunchy Data Postgres 16 STIG (V1R3) and the Crunchy Data PostgreSQL STIG (V3R2). There is no DISA STIG for community PostgreSQL 17 or 18, and the PostgreSQL 9.x STIG is archived — do not cite it as current.
  • SOC 2 is not a configuration standard. It asks whether you have a control and whether you can evidence it operating over a period. pgaudit output with a log_line_prefix that identifies the user, database and host is the evidence; the configuration alone is not.

Regime-specific work has regime-specific shape: PCI-DSS compliant database configurations for fintech, GDPR-aligned data handling and CIS-benchmarked PostgreSQL audits for healthcare platforms, and the audit itself mapped to PCI-DSS, HIPAA, GDPR and ISO 27001. Where the driver is a migration rather than an audit, cloud network access, identity protocols and encryption configured during an MS-SQL to Aurora PostgreSQL migration is one worked example.

Frequently asked questions

Is PostgreSQL secure by default?

No, and the defaults depend on how the cluster was created. PostgreSQL’s own defaults leave ssl off, and a cluster initialised by a bare initdb with no -A option is configured for trust authentication, which the documentation warns allows any local user to connect as any PostgreSQL user including the superuser. Packaged installations differ: on Debian and Ubuntu, pg_createcluster rewrites pg_hba.conf to use peer for Unix-socket connections and md5 for TCP. Check pg_hba.conf and postgresql.conf on your own cluster rather than assuming either default.

Should I still use md5 password authentication in PostgreSQL?

No. As of PostgreSQL 18, md5 password authentication is deprecated: the documentation states that support for MD5-encrypted passwords is deprecated and will be removed in a future release. PostgreSQL 18 emits deprecation warnings from CREATE ROLE and ALTER ROLE when an md5 password is set, and PostgreSQL 19 warns on every successful md5 authentication. CVE-2026-6478 also discloses MD5-hashed passwords via a covert timing channel and was fixed on 14 May 2026 in 18.4, 17.10, 16.14, 15.18 and 14.23. Use scram-sha-256, which has been the password_encryption default since PostgreSQL 14.

Does sslmode=require protect against a man-in-the-middle attack?

No. PostgreSQL’s own SSL Mode Descriptions table rates require as giving eavesdropping protection but no man-in-the-middle protection, because it encrypts the connection without authenticating the server. verify-ca depends on CA policy, and only verify-full is rated as giving man-in-the-middle protection. Use sslmode=verify-full with sslrootcert pointing at your certificate authority. Note that libpq’s default is prefer, which tries TLS and then silently falls back to plaintext.

Does PostgreSQL have transparent data encryption?

PostgreSQL core does not offer transparent data encryption. Its Encryption Options chapter enumerates the encryption facilities PostgreSQL considers available, and full-volume encryption appears there as an operating-system facility such as dm-crypt with LUKS, eCryptfs or geli, not as a PostgreSQL feature. For column-level encryption, pgcrypto is available in contrib, with the documented limitation that all pgcrypto functions run inside the server, so data and passwords move between pgcrypto and client applications in clear text unless you connect locally or over SSL.

How do I give a monitoring tool access to PostgreSQL without granting superuser?

Grant the pg_monitor predefined role: GRANT pg_monitor TO monitor_user. pg_monitor is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables, which covers reading configuration variables and all pg_stat_ views. It does not include pg_read_all_data, so it does not grant access to table contents. For maintenance jobs such as a vacuum cron, use pg_maintain, added in PostgreSQL 17, rather than superuser.

Conclusion

Securing your PostgreSQL database requires a multi-layered approach, combining effective role management, strong authentication, SQL injection prevention, and proactive monitoring. Utilizing tools like pgaudit for auditing, enabling comprehensive logging, and applying regular updates ensures that your database remains protected from evolving threats. By following these best practices, you can safeguard sensitive data, meet compliance requirements, and maintain a secure and resilient PostgreSQL environment.

If you want the current state assessed rather than assumed, a PostgreSQL security and compliance audit engagement produces the role and privilege map, the pg_hba.conf findings and the CIS mapping as deliverables. For ongoing operation, role-based access, encryption in transit and at rest, and audit logging is what that looks like as a managed service. Contact us today.

Contact us today for a Personalised PostgreSQL Consultation!

Mydbops audits authentication, access controls, encryption and logging against the CIS Benchmark, mapped to PCI-DSS, HIPAA, GDPR and ISO 27001.

No items found.

About the Author

CTO of Mydbops, leading the engineering behind its managed database services and agentic platforms for databases. He has 16+ years of Linux and open-source experience, from kernel tuning to MongoDB performance and automation.

Topic covered

Subscribe Now!

Subscribe here to get exclusive updates on upcoming webinars, meetups, and to receive instant updates on new database technologies.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.