postgres_session resource

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

Availability

Installation

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

Version

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

Syntax

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 '' }
end

where

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.

Properties

output

The output property tests the results of the query:

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

Examples

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

Test the PostgreSQL shadow password

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

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

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' }
end

Matchers

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