Setting the postgresql password for automated execution of scripts using psql

To execute batch files that execute psql, one needs to set the password. For security reasons, psql does not provide a command line switch to set the password. The recommended mechanism therefore in Linux is to set the password in the pgpass file.

I maintain a .pgpass file on my machine where I keep such passwords. Here is the way to edit and manage it.

First, open a terminal window and type the command to edit a file called .pgpass in your home folder.

$ gedit ~/.pgpass

Next, add lines to the file for the users you want to store passwords for. The format is

hostname:port:database:username:password

Here are the entries on my personal file.

Save the file, and you should be able to log in without the password, next time your pgpass file gets loaded.

That's it!