Postgres SQL - passwordless login within bash script

Most of the time, we need to write a script to update multiple fields in a database table, pull values from database to fill up other tables or even grab from database and pass it to programs to process. The most annoying thing is every time it tries to connect to the database server, it prompts for password. Worse, if the sql statement in the bash script is not optimized or written properly, the scripts could actually ask for the password, more then 1 time !!!

Problem :

Connecting to postgresql (psql) within a bash script with no password,  preferred.


Solution :

- create .pgpass (notice there is a "dot" in front of the filename) in home directory (~/) with permision not more then 0600 (or it will complain)


- the format of the file is

hostname:server-db-port:dbname:username:password

e.g.

192.168.1.10:*:mydatabase:joe:GIjoe or db.domain.com:5432:db01:mike:macha

- in bash script, specify the server details when using psql as specific as possible.
e.g.

DBCONTENTS=$(psql -U mike -h db.domain.com -d db01 -c "select * from mytablename")

echo $DBCONTENTS;

Adiós !!!