How to Audit a PostgreSQL Database with InSpec on Ubuntu (Including Ubuntu 18.04)

Use Chef InSpec to test PostgreSQL at two levels: the Ubuntu host (packages, services, processes, files and sockets) and the database itself (roles, privileges and SQL-visible settings). This guide builds a reusable profile, keeps version-specific paths in attributes, checks Ubuntu authentication rules, and runs locally or over SSH. Examples show PostgreSQL 10 naming from Ubuntu 18.04; substitute the major version and unit names installed on your target.

Antihost offers Linux VPS plans with full root access, unlimited traffic on a 1 Gbps port and a 14-day free trial.

What an InSpec audit can prove

An InSpec profile turns your PostgreSQL policy into repeatable tests. Host resources can establish that the expected package is installed, the right systemd unit is enabled and running, the server process belongs to the postgres account, and the listener exposes only approved addresses, ports and protocols. File and PostgreSQL-specific resources can test settings in postgresql.conf and inspect pg_hba.conf.

#	Preview	Product	Price
1		PostgreSQL 16 Administration Cookbook: Solve real-world Database Administration challenges with 180+...	$31.99	Buy on Amazon
2		PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database	$38.86	Buy on Amazon
3		PostgreSQL Mistakes and How to Avoid Them	$49.09	Buy on Amazon
4		Learn PostgreSQL: Use, manage, and build secure and scalable databases with PostgreSQL 16	$44.19	Buy on Amazon
5		Mastering PostgreSQL 17: Elevate your database skills with advanced deployment, optimization, and...	$41.99	Buy on Amazon

As an Amazon Associate I earn from qualifying purchases.

Those checks do not prove database catalog state. A host test cannot tell you whether an application role can log in, whether a role has excessive privileges, or whether a required extension or application setting exists. The postgres_session resource supplies the second layer: it opens a PostgreSQL connection and runs SQL through InSpec assertions. Keeping the layers separate makes failures easier to assign to an operating-system, configuration, authentication or database owner.

Prerequisites and version boundaries

• An Ubuntu host on which you can install PostgreSQL and Chef InSpec. InSpec supports Ubuntu both as a runtime and as a remote target.
• sudo access for package installation and controls that read protected files or query service state.
• A policy stating the approved PostgreSQL major version, service state, listener addresses and ports, logging settings, authentication methods and database roles.
• For SQL checks, a test account and password supplied securely at execution time rather than committed to the profile.

The detailed walkthrough that popularized this pattern uses Ubuntu 18.04 and PostgreSQL 10. PostgreSQL package and unit names, configuration directories and authentication defaults can differ on newer Ubuntu releases or another major version. Treat every version-specific value below as an example to adapt after inspecting the target, not as a universal identifier.

#1 Best Overall

Sale

PostgreSQL 16 Administration Cookbook: Solve real-world Database Administration challenges with 180+ practical recipes and best practices

$31.99

Buy on Amazon

Install PostgreSQL and confirm its defaults

Install from Ubuntu packages

Ubuntu’s documented installation command installs the distribution-supported PostgreSQL packages:

sudo apt install postgresql

The meta-package may install a different major version than the PostgreSQL 10 example. Record the resulting package and cluster names before writing strict controls. Ubuntu documents viable initial defaults and recommends regular backups; install the postgresql-doc package and consult the upstream manuals when you need configuration detail beyond this audit.

Check the service and cluster

Before creating controls, inspect what was installed:

systemctl list-units --type=service 'postgresql*'
systemctl status postgresql
psql --version

On a versioned cluster, the active unit may resemble postgresql@10-main. A generic postgresql unit can be an umbrella service. Use the exact unit that represents the cluster you intend to audit; otherwise a passing service check could describe a wrapper while the database cluster is stopped.

Understand the initial authentication policy

Ubuntu’s current documentation identifies peer as the default for local Unix-socket connections and scram-sha-256 as the default for host connections. The effective policy is always the ordered rules in pg_hba.conf, so inspect that file rather than assuming a default survived later hardening or an upgrade. Ubuntu also documents PostgreSQL SSL as built in and configured by default. A production policy can still require explicit hostssl rules, restricted network ranges and a certificate signed by a trusted certificate authority; a certificate suitable only for testing should be replaced before production use.

Install InSpec and create a profile

Install and verify the executable

Chef InSpec is an open-source framework for expressing compliance, security and policy requirements in a human- and machine-readable language. Install the package using the method documented for your Ubuntu release, then confirm that the command is available:

inspec --version

Pin and review the InSpec version used by your automation. Resource behavior and licensing terms can change, and the current InSpec project documentation should be checked before standardizing a version across a fleet.

Rank #2

Sale

PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database

$38.86

Buy on Amazon

Generate the profile skeleton

Create a profile directory and move into it:

inspec init profile PostgreSQL
cd PostgreSQL

The generated layout includes inspec.yml and a controls/ directory. Put the controls in a file such as controls/postgresql.rb. Keep policy inputs—major version, service unit, configuration directory, listener values and SQL credentials—outside the control logic so the same profile can run against more than one Ubuntu image.

Audit package installation and service state

Check the package and minimum version

Use the package resource for both presence and a minimum version. The following is the PostgreSQL 10 pattern from the Ubuntu 18.04 walkthrough:

control 'postgresql-installation' do
  impact 1.0
  title 'Audit PostgreSQL installation'

  describe package('postgresql-10') do
    it { should be_installed }
    its('version') { should cmp >= '10' }
  end
end

A minimum major-version comparison is only meaningful when the package naming and version scheme are known. For PostgreSQL 12, 14, 15 or another release, change the package resource to the package actually installed and set the minimum version your policy permits. If a distribution uses an unversioned postgresql package, pair the package assertion with a command or SQL check that identifies the server major version.

Check enabled and running state

control 'postgresql-service' do
  impact 1.0
  title 'Audit PostgreSQL service state'

  describe service('postgresql@10-main') do
    it { should be_installed }
    it { should be_enabled }
    it { should be_running }
  end
end

Replace postgresql@10-main with the cluster unit found on the target. “Installed,” “enabled” and “running” answer different questions: the unit exists, starts during boot, and is currently active. A server can pass one and fail another, so retain all three assertions when availability is part of the policy.

Verify process ownership, the operating-system account and the listener

Confirm the server runs as the intended account

PostgreSQL should normally run under its dedicated operating-system account rather than root. InSpec’s processes resource can locate the server process and check its owner, while the user resource verifies that the account exists:

control 'postgresql-process-and-account' do
  impact 1.0
  title 'Audit PostgreSQL process ownership'

  describe processes('postgres') do
    its('users') { should include 'postgres' }
  end

  describe user('postgres') do
    it { should exist }
  end
end

Process matching can vary with executable names and packaging. If the resource returns no process, inspect the actual command line and adjust the pattern to the target’s PostgreSQL binary. A process owned by an unexpected account is a high-priority finding because it changes file-access and privilege boundaries.

Constrain the network surface

Check the expected port, address and protocol with listener resources appropriate to your InSpec version. A policy might require the default PostgreSQL port only on loopback, or a private interface and TLS-capable TCP listener. Assert each dimension explicitly so a server listening on the right port but every interface does not pass accidentally.

Rank #3

PostgreSQL Mistakes and How to Avoid Them

$49.09

Buy on Amazon

control 'postgresql-listener' do
  impact 1.0
  title 'Audit PostgreSQL network exposure'

  describe port(5432) do
    it { should be_listening }
    its('addresses') { should include '127.0.0.1' }
    its('protocols') { should include 'tcp' }
  end
end

The address in this example is illustrative. If remote clients are required, replace it with the approved private address and add a separate control ensuring that public or wildcard addresses are not present. A listener check does not prove that clients can authenticate; combine it with pg_hba.conf and SQL-session controls.

Test postgresql.conf with portable profile attributes

Put the configuration directory in attributes

Hard-coding /etc/postgresql/10/main makes a profile brittle across operating systems and major versions. Define the path as a profile attribute:

attributes:
  - name: postgres_conf_dir
    type: string
    default: '/etc/postgresql/10/main'

Override the attribute for another cluster layout at execution time or in an environment-specific profile configuration. Keep the default tied to the example platform, and document the override alongside the target inventory.

Assert logging and connection settings

The postgres_conf resource reads PostgreSQL settings from the configuration directory. The same resource can test any supported PostgreSQL option; these examples cover common connection-audit requirements:

control 'postgresql-logging' do
  impact 1.0
  title 'Audit PostgreSQL logging settings'

  describe postgres_conf(attribute('postgres_conf_dir')) do
    its('log_connections') { should eq 'on' }
    its('log_disconnections') { should eq 'on' }
    its('log_duration') { should eq 'on' }
  end
end

Set the expected values to your organization’s policy. Some settings accept equivalent spellings or values that vary by PostgreSQL release, so confirm the resource’s returned representation before writing a strict equality assertion. Logging controls also need operational review: enabling connection and duration logging can increase volume and may expose identifiers in log-management systems.

Distinguish configured from effective values

A file assertion tells you what is written in the selected configuration file. Include an SQL query through postgres_session when you must prove the server’s effective value after includes, reloads or overrides. This distinction prevents a profile from passing on a stale file while the running cluster uses another setting.

Audit pg_hba.conf and client authentication

Inspect rule order and scope

PostgreSQL evaluates pg_hba.conf from top to bottom, so the first matching rule controls a connection. Audit the complete rule set, not just whether a preferred method appears somewhere in the file. Use InSpec file or command controls to read the file, then compare each local, host and host-SSL rule with the approved policy.

Rank #4

Learn PostgreSQL: Use, manage, and build secure and scalable databases with PostgreSQL 16

$44.19

Buy on Amazon

control 'postgresql-hba-policy' do
  impact 1.0
  title 'Audit PostgreSQL client authentication rules'

  describe file("#{attribute('postgres_conf_dir')}/pg_hba.conf") do
    it { should exist }
    it { should_not be_readable.by_user('other') }
  end

  describe command("grep -Ev '^\s*(#|$)' #{attribute('postgres_conf_dir')}/pg_hba.conf") do
    its('stdout') { should_not match(/trust/) }
  end
end

The command assertion above is only a starting guard against the unauthenticated trust method. A real policy should test approved databases, roles, source CIDRs and methods, and should account for comments and included files. Avoid simplistic regular expressions that can mistake a comment for an active rule or miss a rule loaded through an include directive.

Require TLS where the policy demands it

For networks carrying credentials or sensitive data, require hostssl rules for the relevant database and role ranges and reject non-TLS TCP paths. Ubuntu’s documentation shows this pattern and cautions that a sample certificate is for testing; production clients should validate a certificate issued by a trusted CA. Test both the positive path (an approved TLS connection succeeds) and the negative path (an unencrypted path is rejected) when your test environment permits it.

Use postgres_session for database-level assertions

Create a session with runtime credentials

Host resources cannot inspect role attributes or privileges. The postgres_session resource creates a PostgreSQL session from a username, password, host, port and optional socket path, then exposes SQL results through query. Store connection values as profile attributes, environment variables or your secret-management integration; never commit a real password to inspec.yml or a control file.

sql = postgres_session(
  attribute('db_user'),
  attribute('db_password'),
  attribute('db_host'),
  attribute('db_port')
)

describe sql.query("SELECT rolcanlogin FROM pg_roles WHERE rolname = 'app_user';", ['postgres']) do
  its('rows.first.rolcanlogin') { should eq true }
end

The second argument to query identifies the expected column names returned by the statement. Use catalog views such as pg_roles and information_schema for deterministic checks, and write queries that return one clearly named row when possible. A missing row should fail explicitly rather than causing a misleading nil comparison.

Useful SQL checks

• Check that an application role exists and has the expected rolcanlogin value.
• Verify that a role is not a superuser, replication role or bypass-RLS role unless policy grants that capability.
• Query membership tables to ensure an application role belongs only to approved groups.
• Confirm database ownership, connection limits or application-specific settings exposed through PostgreSQL catalogs.

Do not retrieve password hashes or other secrets merely to prove that a password exists. Prefer metadata such as role flags, and grant the audit account only the catalog permissions required by your queries.

Use a Unix socket when TCP is not the policy path

If the target permits local socket authentication but not TCP, provide the optional socket path supported by postgres_session. This lets the SQL control test the same connection mechanism represented by a local rule in pg_hba.conf. For a network audit, supply the approved host and port instead so the test exercises the host or host-SSL rule.

Run the profile locally or over SSH

Execute on the Ubuntu machine

From the profile directory, run:

inspec exec .

Add --sudo when controls need elevated access to service metadata, protected configuration files or process details:

Best Value

Sale

Mastering PostgreSQL 17: Elevate your database skills with advanced deployment, optimization, and security strategies

$41.99

Buy on Amazon

inspec exec . --sudo

Run without elevation first when practical, then add --sudo only for the controls that require it. This exposes accidental privilege dependencies and keeps the profile usable with a least-privilege execution account.

Target a remote Ubuntu host

InSpec can execute the same profile through SSH. Specify the target using the SSH target syntax supported by your installed InSpec version, for example:

inspec exec . -t ssh://[email protected] --sudo

Use key-based authentication or your organization’s approved SSH credential mechanism. Confirm that the remote account can run the required commands with sudo, that the PostgreSQL configuration path attribute matches the remote host, and that SQL credentials are for the remote database rather than the machine running InSpec. InSpec also documents other target types, including containers and cloud integrations; choose one only when its transport and privilege model are understood.

Read failures and remediate in the right order

Separate infrastructure failures from policy failures

• Package failure: install the approved PostgreSQL package or correct the profile’s package name and minimum version.
• Service failure: identify the actual cluster unit, then enable or start it only after checking why it stopped.
• Process/account failure: investigate unexpected ownership, duplicate clusters or a compromised launch path before changing permissions.
• Listener failure: reconcile listen_addresses, port assignments, firewall rules and the approved network design.
• Configuration failure: edit the authoritative file or included fragment, reload PostgreSQL as appropriate, and rerun the effective-value check.
• HBA failure: fix rule order, CIDR scope, role/database selectors and authentication method; test both allowed and denied paths.
• SQL-session failure: verify credentials, socket versus TCP choice, database name, role privileges and query assumptions without weakening the policy.

Keep evidence tied to the control

Review output by control ID and preserve the failing resource, observed value and target identity. Assign host findings to the Ubuntu or platform owner, configuration and HBA findings to the database administrator, and role or privilege findings to the database/application owner. The sample test counts shown in some tutorials are illustrative run results, not benchmarks; use your own execution output as the evidence for a host.

Make the profile reusable and safe

Parameterize what changes

At minimum, expose the PostgreSQL major version, package name, service unit, configuration directory, expected listener address and port, required configuration values, SQL host and port, and database account as attributes. This avoids copying controls for every Ubuntu image while preserving strict expectations per environment.

Keep secrets out of source control

Supply db_password at runtime through the mechanism your execution environment already protects. Ensure command output and CI logs do not echo connection strings or SQL containing secrets. Use a dedicated read-only audit role where catalog visibility permits it, and separate that role from any account used to administer PostgreSQL.

Quick Recap

SaleBestseller No. 1

PostgreSQL 16 Administration Cookbook: Solve real-world Database Administration challenges with 180+ practical recipes and best practices

$31.99

SaleBestseller No. 2

PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database

$38.86

Bestseller No. 3

PostgreSQL Mistakes and How to Avoid Them

$49.09

Bestseller No. 4

Learn PostgreSQL: Use, manage, and build secure and scalable databases with PostgreSQL 16

$44.19

SaleBestseller No. 5

Mastering PostgreSQL 17: Elevate your database skills with advanced deployment, optimization, and security strategies

$41.99

Compare profiles by coverage, portability and connectivity

Practical completion checklist

• PostgreSQL is installed from Ubuntu packages and the actual major version is recorded.
• The selected cluster service is installed, enabled and running.
• The server process exists and is owned by the expected postgres account.
• The operating-system postgres user exists with the organization’s approved account policy.
• The listener exposes only approved ports, addresses and protocols.
• postgresql.conf assertions use an attribute for the configuration directory.
• Required logging and connection settings match policy, including effective values where includes or overrides matter.
• pg_hba.conf rules are reviewed in order, with approved CIDRs, roles, databases and authentication methods.
• TLS-only requirements are expressed with hostssl rules and trusted production certificates.
• postgres_session checks cover role login flags, privileges or application-specific SQL state that host checks cannot see.
• Credentials are injected securely, and the profile has been run with the least privilege that still permits its controls.
• The profile passes locally and, where relevant, against the same kind of Ubuntu host over SSH.

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.