Use the postgres_session InSpec audit resource to test SQL commands run against a PostgreSQL database.


A postgres_session resource block declares the username and password to use for the session, and then the command to be run:

# Create a PostgreSQL session:
sql = postgres_session('username', 'password', 'host')

# default values:
#   username: 'postgres'
#   host: 'localhost'

# Run an SQL query with an optional database to execute
sql.query('sql_query', ['database_name'])`

A full example is:

sql = postgres_session('username', 'password', 'host')
describe sql.query('SELECT * FROM pg_shadow WHERE passwd IS NULL;') do
  its('output') { should eq '' }

where its('output') { should eq '' } compares the results of the query against the expected result in the test


The following examples show how to use this InSpec audit resource.

Test the PostgreSQL shadow password

sql = postgres_session('my_user', 'password', '')

describe sql.query('SELECT * FROM pg_shadow WHERE passwd IS NULL;', ['testdb']) do
  its('output') { should eq('') }

Test for risky database entries

describe postgres_session('my_user', 'password').query('SELECT count (*)
              FROM pg_language
              WHERE lanpltrusted = \'f\'
              AND lanname!=\'internal\'
              AND lanname!=\'c\';', ['postgres']) do
  its('output') { should eq '0' }


For a full list of available matchers, please visit our matchers page.


The output matcher tests the results of the query:

its('output') { should eq(/^0/) }