Skip to main content

postgres_session resource

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



This resource is distributed with Chef InSpec and is automatically available for use.


This resource first became available in v1.0.0 of InSpec.


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', 'port', 'socketpath')

# default values:
#   username: 'postgres'
#   host: 'localhost'
#   port: 5432
#   socketpath (optional): nil

# 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', 'port', 'socketpath')
describe sql.query('SELECT * FROM pg_shadow WHERE passwd IS NULL;') do
  its('output') { should eq '' }


  • its('output') { should eq '' } compares the results of the query against the expected result in the test
  • socketpath is an optional parameter. Use socketpath to establish a socket connection with Postgres by specifying one of the Postgres Unix domain socket paths. Only supported on Unix-based platforms.



The output property tests the results of the query:

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


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

Test the PostgreSQL shadow password

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

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, see our Universal Matchers page.
Edit this page on GitHub

Thank you for your feedback!


Search Results