databasepostgresqlchef-infrainspec

Validate database output using chef inspec


While executing the below postgresql command, how to validate the output under name column and row column using chef inspec.

postgres=# select name, setting from pg_settings where (name ~ '_directory$'
postgres(# or name ~ '_tablespace');
         name         |        setting
----------------------+------------------------
 data_directory       | /var/lib/pgsql/10/data
 default_tablespace   |
 log_directory        | log
 stats_temp_directory | pg_stat_tmp
 temp_tablespaces     |
(5 rows)

Solution

  • you can use 2 postgres resources:

    1. postgres_session to test SQL commands run against a PostgreSQL database

      sql = postgres_session('username', 'password', 'host', 'port')
      describe sql.query('SELECT * FROM pg_shadow WHERE passwd IS NULL;') do
        its('output') { should eq '' }
      end
      
    2. postgres_conf to test the contents of the configuration file for PostgreSQL

      describe postgres_conf do
        its('max_connections') { should eq '5' }
      end