How to automate the psql password

by: Travis Media
Jan 21, 2020
0 minute read

Password prompts really put a damper on automation. In this post, I want to show you how to automate the psql password so you can run Postgres commands in a scripting environment.

So let’s create a script that:

Authenticates as the postgres user
Creates two databases, two users for those databases, and two passwords
Gives a success message upon completion

Here’s the script.

#!/bin/bash

#PSQL variables
psqlUser='postgres'
psqlPassword='testpassword'

#DB & User Variables
dbOne=one_db
userOne=one
passwordOne=testpassword

dbTwo=two_db
userTwo=two
passwordTwo=testpassword

RUN_ON_MYDB="psql -X -U $psqlUser password=$psqlPassword --set ON_ERROR_STOP=on"

$RUN_ON_MYDB << PSQL
CREATE DATABASE $dbOne;
CREATE USER $userOne WITH PASSWORD '$passwordOne';
ALTER DATABASE $dbOne OWNER TO $userOne;
grant all privileges on database $dbOne to $userOne;

CREATE DATABASE $dbTwo;
CREATE USER $userTwo WITH PASSWORD '$passwordTwo';
ALTER DATABASE $dbTwo OWNER TO $userTwo;
grant all privileges on database $dbTwo to $userTwo;
PSQL

if [ $? -eq 0 ]; then
    echo "Success! DBs, Users, and PWs created."
else
    echo "Stopped due to an error. Try again."
fi