Validate database output using chef inspec

Question

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)

Answer 1

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