Skip to main content

postgres_session resource

[edit on GitHub]

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



This resource is distributed along with Chef InSpec itself. You can use it automatically.


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')

# 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 Chef 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/) }