postgres_session resource

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

Availability

Install

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, see our Universal Matchers page.