Secure PostgreSQL with cnspec

Scan your PostgreSQL servers to find security risks before they become incidents. cnspec evaluates roles and their attributes, host-based authentication rules, transport encryption, database and schema privileges, SECURITY DEFINER functions, row-level security, extensions, foreign servers, and runtime configuration.

cnspec connects with a PostgreSQL role and inventories the server through read-only queries against pg_catalog and information_schema. It never touches your data.

If you're new to cnspec, start with the Quickstart. For an overview of every database cnspec can scan, see the database scanning overview.

Prerequisites

To scan PostgreSQL with cnspec, you must have:

• cnspec installed on your workstation
• Network access to the server on its port (5432 by default)
• A role that can connect and read the catalog

Authenticate

cnspec authenticates with a PostgreSQL role and password, and negotiates TLS according to --sslmode:

cnspec shell postgresdb db.contoso.com --user postgres --ask-pass

For a verified-TLS connection, add the certificate flags:

cnspec shell postgresdb db.contoso.com --user auditor --ask-pass --sslmode verify-full --sslrootcert ca.pem

Prefer a least-privileged role for auditing. A role's passwordType and the hbaRules collection need superuser, or membership in pg_read_all_settings and pg_read_all_stats. Without those, they degrade to null or empty rather than failing the scan, so confirm they're populated before trusting a check that reads them.

Connection options

Verify with a quick PostgreSQL check

cnspec shell postgresdb db.contoso.com --user postgres --ask-pass

cnspec> postgresdb.instance { version ssl passwordEncryption }
postgresdb.instance: {
  version: "PostgreSQL 17.10 (Debian 17.10-1.pgdg13+1) on aarch64-unknown-linux-gnu, ..."
  ssl: false
  passwordEncryption: "scram-sha-256"
}

If cnspec connects and shows the version, you're ready to scan.

Scan PostgreSQL

PostgreSQL can't query across databases, so cnspec connects per database. By default it discovers each connectable database as its own postgres-database asset alongside the server asset:

cnspec scan postgresdb db.contoso.com --user auditor --ask-pass

To scan the server only:

cnspec scan postgresdb db.contoso.com --user auditor --ask-pass --discover none

When a scan completes, cnspec prints a summary of all the checks it ran, grouped by policy, along with a risk score from 0 (no risk) to 100 (highest risk). Failed checks include remediation guidance to help you fix issues. To learn more about reading scan results, read Understand cnspec Results.

Mondoo doesn't yet ship an out-of-the-box PostgreSQL policy. The resources are shaped around the CIS PostgreSQL Benchmark, so the checks below map closely to its recommendations. Use them as a starting point and create your own policies to meet your requirements.

Explore and test checks interactively

cnspec shell postgresdb db.contoso.com --user auditor --ask-pass

List roles and their attributes

cnspec> postgresdb.instance.roles { name canLogin isSuperuser createRole createDb bypassRLS }

List host-based authentication rules

cnspec> postgresdb.instance.hbaRules { type databases userNames address authMethod }

Read a configuration setting

cnspec> postgresdb.instance.settings.where(name == "log_connections") { name setting source }

List privileges on a schema

cnspec> postgresdb.instance.databases.where(name == "appdb").first.schemas.where(name == "public").first.privileges { grantee privilegeType }
postgresdb.instance.databases.where.first.schemas.where.first.privileges: [
  0: { privilegeType: "USAGE"  grantee: "pg_database_owner" }
  1: { privilegeType: "CREATE" grantee: "pg_database_owner" }
  2: { privilegeType: "USAGE"  grantee: "PUBLIC" }
]

Find databases with pgvector installed

An empty pgvectorVersion means pgvector is not installed:

cnspec> postgresdb.instance.databases.where(pgvectorVersion != "") { name pgvectorVersion }

Example security checks

Ensure TLS is enabled

cnspec> postgresdb.instance.ssl == true
[ok] value: true

Ensure passwords are stored with SCRAM

md5 password storage is a legacy scheme that PostgreSQL keeps only for compatibility:

cnspec> postgresdb.instance.passwordEncryption == "scram-sha-256"
[ok] value: true

Ensure no authentication rule trusts the client

An hbaRule with the trust method authenticates anyone who can reach the address, with no credential at all:

cnspec> postgresdb.instance.hbaRules.none(authMethod == "trust")
[ok] value: true

Ensure no rule accepts unencrypted TCP connections

A hostnossl rule matches only connections that are not encrypted:

cnspec> postgresdb.instance.hbaRules.none(type == "hostnossl")
[ok] value: true

A plain host rule matches both encrypted and unencrypted connections, so review those too and narrow them to hostssl where the client can support it:

cnspec> postgresdb.instance.hbaRules.where(type == "host") { lineNumber databases userNames address authMethod }

Ensure superuser is limited to the bootstrap role

cnspec> postgresdb.instance.roles.where(isSuperuser).all(name == "postgres")
[ok] value: true

Ensure no login role bypasses row-level security

bypassRLS makes every row-level security policy invisible to the role:

cnspec> postgresdb.instance.roles.where(canLogin).none(bypassRLS == true)
[ok] value: true

Ensure CREATE is not granted to PUBLIC on the public schema

Any role that can connect holds a PUBLIC grant, so CREATE there lets every user add objects:

cnspec> postgresdb.instance.databases.all(schemas.where(name == "public").all(privileges.none(grantee == "PUBLIC" && privilegeType == "CREATE")))
[ok] value: true

Review SECURITY DEFINER functions

A SECURITY DEFINER function runs with its owner's privileges, so one owned by a superuser is a privilege-escalation path:

cnspec> postgresdb.instance.databases { name functions.where(isSecurityDefiner) { schema name owner { name isSuperuser } } }

Ensure connection logging is on

cnspec> postgresdb.instance.settings.where(name == "log_connections").all(setting == "on")
[ok] value: true

Ensure no role's password validity has already lapsed unnoticed

A validUntil in the past means the role can no longer authenticate, which is usually a leftover rather than an intentional state:

cnspec> postgresdb.instance.roles.where(canLogin && validUntil != null).none(validUntil < time.now)
[ok] value: true

Review foreign servers and their user mappings

A foreign server reaches outside this database, and its user mappings carry the credentials that get there:

cnspec> postgresdb.instance.databases { name foreignServers { name fdwName userMappings { role server } } }

Review tables that hold row-level security policies

cnspec> postgresdb.instance.databases { name schemas { tables.where(rowSecurityEnabled) { schema name rowSecurityForced policies { name command roles } } } }

Learn more

• PostgreSQL Resource Pack Reference: every PostgreSQL resource and field cnspec can query
• Secure Neon with cnspec: for Neon's managed Postgres control plane
• Write Effective MQL: guide to authoring checks and queries