1. Install PSQL on the machine you plan on connecting to Redshfit with.
I used an Amazon Linux AMI
Once connected through SSH run the following:
> sudo yum update
> sudo yum install postgresql
Test that you can connect to your Redshift Server.
> psql -h my-connection-string -p my-port# -U user -d mydb
you'll be promted for the pasword. Once this works, we can setup the PGPASSFILE.
2. Create the Password File.
$ touch ~/.pgpass
$ chmod 0600 ~/.pgpass
Use your favorite text editor to add the following line to the file.
hostname:port:database:username:password
You can find more information regarding this file here : https://www.postgresql.org/docs/9.2/static/libpq-pgpass.html
3. Create a shell script to test your auto authentication. <yourscript>.sh
#!/bin/sh
set PGPASSFILE=~/.pgpass
psql -h <host url> -p <port> -U <user> -d <db_name> -c "Select 'Hello World' "
echo Done
Save this script on your ec2 instance. Run it.
$ sh <yourscript>.<ext>
