What should a PostgreSQL security checklist cover?

A PostgreSQL security checklist covers six areas: authentication rules in pg_hba.conf, transport encryption, authorisation (superusers, schema and default privileges, row-level security), network exposure, secret handling, and auditing plus patching. Each item should have a query you can run and a fix you can apply. Both are below.

Key facts

| Fact | Value | |---|---| | pg_hba.conf matching | First matching line wins; no fall-through to later lines (docs) | | password_encryption default | scram-sha-256 since PostgreSQL 14 (docs) | | ssl default | off — TLS is opt-in on a self-compiled/self-managed server (docs) | | PUBLIC CREATE on schema public | Revoked by default since PostgreSQL 15 (docs) | | listen_addresses default | localhost (docs) | | .pgpass permissions | Ignored unless mode is 0600 or stricter (docs) | | Minor releases | Cumulative, quarterly, no dump/restore — just a restart (policy) | | Fleet: password_encryption | Across 33 monitored instances, 15.2% are not on scram-sha-256 | | Fleet: superuser login roles | Across 50 monitored instances, 8.0% have more than two |

Why this happens

Most PostgreSQL security findings are not exploits. They are defaults that were correct for a laptop and never revisited on a server, or a setting that was loosened during an incident and never tightened again.

Three mechanics explain the majority of them.

pg_hba.conf is first-match, not best-match. The server reads the file top to bottom and stops at the first line whose connection type, database, user and address all match. If no line matches, the connection is refused outright — there is no fall-through to a later, stricter rule (docs). A broad host all all 0.0.0.0/0 md5 near the top therefore silences every careful rule below it, and a trust line placed above everything else disables authentication entirely for anything it matches. This is also why a missing rule produces the no pg_hba.conf entry for host error rather than a password prompt — see the fix for that error.

Password hashing is set at the moment the password is written, not at login. password_encryption controls the algorithm used when CREATE ROLE ... PASSWORD or ALTER ROLE ... PASSWORD stores a verifier (docs). Flipping the setting to scram-sha-256 changes nothing for roles whose passwords were already stored as md5 — those stay md5 until each password is set again. The default has been scram-sha-256 since PostgreSQL 14, so md5 verifiers on a modern server are almost always inherited from an older one through pg_upgrade or a restore.

Privileges are granted to PUBLIC more often than people expect. Before PostgreSQL 15, every database had CREATE on schema public granted to PUBLIC, meaning any role that could connect could create objects there. PostgreSQL 15 revoked that by default (docs), but databases carried forward from an older major version keep the old grant. Separately, ALTER DEFAULT PRIVILEGES writes rows into pg_default_acl that silently apply to objects created later by a given role in a given schema (docs) — a single forgotten GRANT ... TO PUBLIC default can keep re-granting access to every new table for years.

How to detect it

Run these as a superuser or a member of pg_monitor. Each one is a checklist item.

1. Authentication rules, parsed. pg_hba_file_rules (PostgreSQL 10+) shows the rules as the server actually parsed them, in match order, with an error column for lines that failed to load (docs):

SELECT rule_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
ORDER BY rule_number;

Read it top down. Anything with auth_method = 'trust', any md5, any address of 0.0.0.0/0 or ::/0, and any non-null error is a finding. Note that type = 'hostssl' only requires TLS on the connection; it does not verify the client's certificate unless you also use clientcert (docs).

2. Password hashing.

SHOW password_encryption;
SELECT rolname,
       CASE WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram'
            WHEN rolpassword LIKE 'md5%'           THEN 'md5'
            WHEN rolpassword IS NULL               THEN 'none'
            ELSE 'other' END AS verifier
FROM pg_authid WHERE rolcanlogin ORDER BY 2, 1;

3. Transport. ssl defaults to off (docs); this also shows which live sessions are actually encrypted:

SHOW ssl;
SELECT s.ssl, s.version, count(*)
FROM pg_stat_ssl s GROUP BY 1, 2 ORDER BY 3 DESC;

4. Superusers and elevated roles.

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolreplication, rolbypassrls
FROM pg_roles
WHERE rolcanlogin AND (rolsuper OR rolcreaterole OR rolcreatedb OR rolreplication OR rolbypassrls)
ORDER BY rolsuper DESC, rolname;

More on reading these columns in listing users and roles.

5. PUBLIC grants and default privileges.

SELECT nspname, nspacl FROM pg_namespace WHERE nspname NOT LIKE 'pg\_%';
SELECT pg_get_userbyid(defaclrole) AS grantor, defaclnamespace::regnamespace AS schema,
       defaclobjtype, defaclacl
FROM pg_default_acl;

=UC/ in an nspacl means PUBLIC still holds USAGE and CREATE. Any pg_default_acl row containing = with no role name before it is a standing grant to PUBLIC on every future object of that type (docs).

6. Exposure.

SHOW listen_addresses;
SHOW port;

localhost is the default (docs); * plus a permissive pg_hba.conf plus a public IP is the combination that turns up in brute-force logs.

7. Auditing.

SELECT name, setting FROM pg_settings
WHERE name IN ('log_connections','log_disconnections','log_statement','log_line_prefix','shared_preload_libraries');

8. Patch level.

SELECT version(), current_setting('server_version_num');

Compare against the current minor release for your major version on postgresql.org; more on that in end-of-life versions.

How MyDBA shows this

MyDBA scores a security domain per instance on its own schedule — superuser login roles (warning above two), observed SSL usage, password_encryption, and elevated-privilege roles — and each failing check becomes an Action Item at /recommendations with the query and the fix attached. Across the monitored fleet, 15.2% of 33 instances evaluated for password_encryption are not on scram-sha-256, and 8.0% of 50 instances carry more than two superuser login roles; SSL usage came back clean on all 50. Domain scores roll up to the worst member, so one instance with an md5 verifier pulls a whole cluster's grade down rather than hiding in an average.

We publish our own security posture at /security rather than asking you to take it on trust. If you want the same domain scored against your instances, start with the free PostgreSQL health check.

How to fix it

Work down the list; each fix maps to a check above.

1. Remove trust and narrow the wildcards. Delete or scope any trust line outside local-socket use, replace 0.0.0.0/0 with the real client CIDRs, and move the specific rules above the general ones. Reload with SELECT pg_reload_conf(); and re-read pg_hba_file_rules to confirm the error column is empty. 2. Move to SCRAM. Set password_encryption = 'scram-sha-256', reload, then re-set each md5 role's password — ALTER ROLE app_user PASSWORD 'new-secret'; — because existing verifiers are not rewritten (docs). Change md5 to scram-sha-256 in pg_hba.conf only after every role has a SCRAM verifier. 3. Turn TLS on and require it. Set ssl = on with ssl_cert_file/ssl_key_file, restart, then change host to hostssl for remote rules (docs). On the client, sslmode=require encrypts but verifies nothing — use verify-ca or verify-full with a sslrootcert if you want to know you are talking to your own server (docs). 4. Cut the superuser count. Give each role only what it needs: pg_monitor, pg_read_all_data, pg_signal_backend and the other predefined roles replace most reasons to hand out SUPERUSER (docs). Then ALTER ROLE app_user NOSUPERUSER NOCREATEROLE NOCREATEDB;. 5. Close the PUBLIC grants. REVOKE CREATE ON SCHEMA public FROM PUBLIC; on databases upgraded from PostgreSQL 14 or earlier, and clear stale defaults with ALTER DEFAULT PRIVILEGES FOR ROLE deployer IN SCHEMA app REVOKE ALL ON TABLES FROM PUBLIC; (docs). Where per-row separation is needed, enable row-level security — see the row-level security guide. 6. Take the port off the internet. Put the server behind a private network or bastion; if it must listen publicly, restrict listen_addresses and back it with a firewall rule, not pg_hba.conf alone. 7. Fix secret handling. chmod 0600 ~/.pgpass — libpq ignores the file outright if it is group- or world-readable (docs). Prefer .pgpass or a secret manager over PGPASSWORD, which is visible in the process environment, and never pass a password as a SQL literal: ALTER ROLE ... PASSWORD 'x' lands in pg_stat_activity.query and in the log if log_statement catches it (docs). psql's \password computes the verifier client-side and avoids this. 8. Turn on the audit trail. Set log_connections = on, log_disconnections = on and log_statement = 'ddl' (docs). 'ddl' is the setting most estates can afford to leave on permanently; 'all' is a volume decision. For per-object or per-role detail, add pgaudit to shared_preload_libraries. 9. Apply the current minor release. Minor updates are cumulative and need only a restart — no dump and restore (policy).

How to prevent it

The checks above are cheap to run and slow to drift, which makes them a good fit for scheduling rather than for a quarterly spreadsheet.

Three habits keep the list from re-filling. First, treat pg_hba.conf as code: keep it in version control, and after every reload assert that pg_hba_file_rules contains no trust, no md5, no 0.0.0.0/0 and no non-null error. That single query is a usable CI gate.

Second, make privilege grants explicit and reviewed. New databases should get REVOKE CREATE ON SCHEMA public FROM PUBLIC as part of provisioning, and every ALTER DEFAULT PRIVILEGES should name a grantee role rather than relying on PUBLIC. Re-read pg_default_acl whenever a deployment role changes — the rows outlive the person who added them.

Third, alert on the counts, not on the events. Superuser count, non-SCRAM verifier count, ssl, listen_addresses and the server minor version are all small integers or strings that should change only when someone means them to. Watching them for change catches the loosened-during-an-incident case, which is the one a checklist run every six months always misses. The same logic applies to the rest of the health check checklist — and to the configuration audit, where several of these settings also appear.

FAQ

Does sslmode=require mean my connection is authenticated?

No. require means libpq insists on an encrypted connection, but it does not check the server's certificate against a CA or hostname, so it does not protect against a man-in-the-middle. verify-ca checks the certificate chain and verify-full also checks the hostname (docs).

Why do roles still use md5 after I set password_encryption = 'scram-sha-256'?

Because the setting only applies when a password is written. Existing verifiers are not converted. Re-run ALTER ROLE ... PASSWORD ... (or \password in psql) for each affected role, then check pg_authid.rolpassword for the SCRAM-SHA-256$ prefix before switching pg_hba.conf from md5 to scram-sha-256.

Is REVOKE CREATE ON SCHEMA public FROM PUBLIC still needed on PostgreSQL 18?

Only on databases that were created on PostgreSQL 14 or earlier and carried forward. PostgreSQL 15 changed the default so new databases no longer grant CREATE on public to PUBLIC (docs). Check pg_namespace.nspacl rather than assuming either way.

How many superusers is too many?

There is no PostgreSQL-defined limit. As a working threshold, more than two login roles with rolsuper is worth reviewing; most day-to-day needs are covered by predefined roles such as pg_monitor and pg_read_all_data (docs). Across 50 monitored instances, 8.0% exceed that threshold.

Does log_statement = 'all' log passwords?

It can. A CREATE ROLE/ALTER ROLE ... PASSWORD 'literal' statement is logged as written, and the same text is visible in pg_stat_activity.query while it runs. Use \password, which sends a pre-computed verifier, and keep log_statement = 'ddl' unless you have a specific reason to log everything.

Part of the The PostgreSQL health check checklist guide. Last verified against PostgreSQL 18, 2026-09-17.