train-pgsql - Train Plugin for connecting via pgsql

This plugin allows applications that rely on Train to communicate via postgres SQL.

Installation

This is published to rubygems.org at https://rubygems.org/gems/train-pgsql.

You can install the gem directly:

gem install train-pgsql

Or you can install it as an inspec plugin:

inspec plugin install train-pgsql

Transport parameters

Option	Explanation	Default	ENV VAR
host	Hostname	(required)	PGHOST
user	Username to connect	(required)	PGUSER
password	Password to connect	(required)	PGPASSWORD
database	Database to connect	postgres	PGDATABASE
port	Remote port	5432	PGPORT

Transport CommandResult

connection.run_command('SQL HERE') returns a CommandResult object. The stdout value is set to a hash with fields and values.

For Example:

#<struct Train::Extras::CommandResult stdout={"fields"=>["name", "owner", "acl"], "values"=>[["public", "postgres", "postgres=UC/postgres"], ["public", "postgres", "=UC/postgres"]]}, stderr="", exit_status=0>

Example use in inspec

Connect to the postgresql target as such:

inspec shell -t pgsql://db.host.name --user 'username' --password 'supersecret' --insecure boolean

or

inspec exec -t pgsql://db.host.name --user 'username' --password 'supersecret' --insecure boolean

Alternatively you can set all these as environment variables using the following variables and authenticate without the parameters in in the inspec command or the target

export PGHOST='db.host.name'
export PGUSER='username'
export PGPASSWORD='supersecret'
export PGDATABASE='somedatabase'
inspec exec -t pgsql://

Example use from Ruby

This will work if you have postgresql running on localhost:

require "train"
train  = Train.create("pgsql", {
            host:     "localhost",
            user:     "username",
            password: "password",
         })
conn   = train.connection
result = conn.run_command("show version\n")
conn.close

Local development

If you are building this on a Mac you may run into an issue trying to install this locally due to the PG gem not installing due to code signing issues. You can build it and run it in a docker container.

Requirements

• Postgres running locally
• docker

Steps

1) Build the train-pgsql gem

   rake build

2) Build the docker image. Be sure to set the appropriate GEM_VERSION arg based upon the value of the version.rb

   docker build --build-arg GEM_VERSION=1.0.0 . -t train-pgsql-test 

3) Run the test.rb file

   docker run -it --rm -v $(pwd)/test:/share -e PG_HOST="host.docker.internal" --entrypoint ruby train-pgsql-test test.rb

Note: This test assumes you are running postgres locally on a mac. If you are running postgres in some other location, update the PG_HOST environment variable appropriately. You may have to set the username/password in the pgsql train instantiation.

You should see output resembling the following:

   #<struct Train::Extras::CommandResult stdout="1", stderr="", exit_status=0>

Deploy

To publish a new version to RubyGems: 1) Ensure the version.rb has been bumped with the appropriate semver update. 2) Run rake release

   rake release

Note: you may have to authenticate with rubygems to publish. The deploy@stridehealth.com group has a rubygems account that is an owner of this gem.

Acknowledgements

• train-telnet
• train