A union of curiosity and data science

Knowledgebase and brain dump of a database engineer

EC2 and Redshift - Set Password File - use Bash / PSQL / Select without interactive password

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.


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

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>