C11 — Security Posture

Authentication and privilege configuration that's easy to get wrong quietly: superusers without a password, weak (md5) authentication entries, dangerous default privileges on the public schema, and what fraction of login-capable roles are superusers at all. None of these are exploitable on their own the way a code vulnerability is — they're the kind of drift that turns into a real incident only when combined with something else, which is exactly why they're easy to let slide without a check like this.

12 checks, PGHF11-001 through PGHF11-012. Every check here is PGHF-namespace and built-in — see Metadata Columns Explained for what each field below actually means, and Resetting to Defaults for why these definitions can't be hand-edited in place.

PGHF11-001 — Superusers without password

A superuser role with no password can be accessed without any authentication at all if pg_hba.conf allows it — the single highest-privilege account in the cluster, protected by nothing.

How to fix

Treat this as urgent:

ALTER ROLE the_superuser PASSWORD 'a-strong-randomly-generated-password';

Then confirm pg_hba.conf requires scram-sha-256 (PGHF11-010) on the path that role connects over — a password alone doesn't help if the pg_hba.conf rule is trust (PGHF01-006).

PGHF11-002 — MD5 authentication entries

MD5 is a weak hashing algorithm vulnerable to offline brute-force attacks against a captured hash — scram-sha-256 (PGHF11-010) is the modern replacement and any remaining MD5 entries are legacy exposure worth migrating off.

How to fix

1. Set password_encryption = scram-sha-256 (PGHF11-010) so new/changed passwords hash correctly going forward.
2. For every existing role, force a password reset (an old MD5-hashed password stays MD5 until it's explicitly changed — password_encryption doesn't rehash existing passwords):

ALTER ROLE the_role PASSWORD 'a-new-password';

1. Update pg_hba.conf entries from md5 to scram-sha-256 and reload once every affected role has a SCRAM password.

PGHF11-003 — Public schema CREATE privilege

If PUBLIC can still CREATE in the public schema, any authenticated user — including one with no other privileges — can create objects there, which is a well-known privilege-escalation path via object-name hijacking.

How to fix

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(This is the default since PostgreSQL 15; on an older version or a database created before then, it needs revoking explicitly.) Grant CREATE back only to the specific roles/schemas owners that legitimately need it.

PGHF11-004 — Privileged non-superuser roles

CREATEROLE/CREATEDB grant significant power short of full superuser — worth periodically confirming every role holding them still needs to, since these grants tend to accumulate and outlive their original justification.

How to fix

No universal fix — review each flagged role and confirm it still needs the privilege for a current purpose. Revoke what's no longer justified:

ALTER ROLE the_role NOCREATEROLE;
ALTER ROLE the_role NOCREATEDB;

Note PG16+'s CREATEROLE is itself more tightly scoped (a CREATEROLE role can no longer grant itself SUPERUSER/REPLICATION/BYPASSRLS on another role) — if this server is pre-16, weigh that gap specifically when deciding whether a given grant is safe to leave.

PGHF11-005 — Connection logging

Without connection/disconnection logging, unauthorized access attempts leave no trail at all — this is foundational visibility for any later incident investigation, not an optional nicety.

How to fix

ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
SELECT pg_reload_conf();

Overhead is negligible relative to the forensic value — there's little reason to leave this off outside a benchmark-sensitive test environment.

PGHF11-006 — pgaudit extension

Standard PostgreSQL logging doesn't provide the detailed, compliance-grade audit trail (who ran what DDL/DML, on which objects) that pgaudit does — its absence limits both compliance posture and forensic capability after an incident.

How to fix

ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';  -- append if other libraries already preload

pg_ctl restart

CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'ddl, write';  -- scope to what your compliance requirement actually needs
SELECT pg_reload_conf();

Scope pgaudit.log deliberately — logging everything (all) is usually far more volume than needed and can itself become an operational burden.

PGHF11-007 — Non-superuser login role count

PostgreSQL has no built-in last-login tracking, so this raw count is the starting point for a manual periodic access review — a growing count with no corresponding review process is how stale accounts accumulate unnoticed.

How to fix

No config fix — this is a process gap, not a setting. Establish a periodic access review (quarterly is common) cross-referencing this role list against current staff/service ownership. If genuine last-login visibility matters, log_connections (PGHF11-005) plus external log aggregation is the practical substitute, since the server itself doesn't track it.

PGHF11-008 — SSL certificate paths

Knowing exactly where the certificate and private key files live is what lets an operator actually verify their filesystem permissions are locked down — a private key readable by the wrong OS user defeats the point of TLS entirely.

How to fix

Using the paths this check reports, confirm on the filesystem:

ls -l /path/to/server.key

The private key should be owned by the PostgreSQL OS user and mode 0600 (or 0640 if a specific group needs read access) — never world-readable. Fix with chmod 600 server.key and chown to the correct owner if it's wrong.

PGHF11-009 — Superuser login roles as % of all login roles

Every superuser role bypasses all access controls, including row-level security policies — a high proportion of superusers relative to total login roles means privilege isn't being scoped down to what each role actually needs.

How to fix

For each superuser role that doesn't genuinely need full superuser, drop the attribute and grant the specific privilege it actually needs instead:

ALTER ROLE the_role NOSUPERUSER;
GRANT pg_monitor TO the_role;  -- or another predefined role matching the actual need

PostgreSQL's predefined roles (pg_monitor, pg_read_all_data, pg_signal_backend, and similar) cover most common "needs elevated access but not everything" cases without granting full superuser.

PGHF11-010 — password_encryption is scram-sha-256

MD5 password hashes are crackable offline once captured; scram-sha-256 uses a proper challenge-response protocol that never exposes an equivalent crackable hash over the wire — this setting controls what NEW passwords get hashed with going forward.

How to fix

ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

This only changes the default for new password assignments — see PGHF11-002 for migrating existing MD5-hashed passwords.

PGHF11-011 — Unencrypted client connections

Even with SSL available and PGHF01-001 reporting it enabled, individual client connections can still choose to connect unencrypted — this check catches actual traffic in the clear, credentials and query results included, not just server-side configuration.

How to fix

See PGHF01-001's remediation for enabling SSL if it isn't already — then close the gap that lets clients opt out: change host entries in pg_hba.conf to hostssl so an unencrypted connection is refused outright rather than merely discouraged:

SELECT pg_reload_conf();  -- after editing pg_hba.conf

PGHF11-012 — MAINTAIN privilege / pg_maintain role audit

Requires PostgreSQL 17+.

MAINTAIN grants VACUUM/ANALYZE/REINDEX/CLUSTER/REFRESH MATERIALIZED VIEW without full table ownership — a real privilege-escalation-adjacent surface, structurally identical to the existing CREATEROLE/CREATEDB audit (PGHF11-004), just for a privilege that didn't exist when PGHF11-004 was written.

How to fix

No universal fix — review each flagged role and confirm it still needs MAINTAIN for a current purpose. Revoke what's no longer justified:

REVOKE pg_maintain FROM the_role;

or, to scope it more narrowly than the blanket predefined role, grant MAINTAIN on specific tables instead of adding the role to pg_maintain wholesale:

GRANT MAINTAIN ON schema.the_table TO the_role;

Continue to C12 — pgEdge / Spock Cluster.