PostgreSQL - .PGPass file

.pgpass file in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

This file should contain lines of the following format:

hostname:port:database:username:password

You can follow below steps to connect to PostgreSQL or PostgreSQL compatible tool or database systems.

Step 1: Created the .pgpass file. Below command will create the hidden .pgpass file in the home directory.

             vi ~/.pgpass

Step 2: Add the connection details with the instnace, port, database, user and password information in the below format. You can also use the wild card character like * as well.

 PostgreSQLInstance1:5432:mydatabase:myuser:mypassword

 *:*:mydatabase:myuser:mypassword

Step 3: On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. Changed file mode to 600 as below

chmod 600 ~/.pgpass

Step 4: Export the PGPASSFILE file

export PGPASSFILE=~/.pgpass

Step 5: Test the connection. PGSQL -w (lower case) option will not prompt for password and will connect using the password from the .pgpass file.

psql -U <user name> -h <PostgreSQL instance Name> <DB Name> -p 5432 -w -c "select * from tb0"

 id

----

 3

 1

 2

(3 rows)

PGSQL -W (upper case) will prompt for the password even specified in .pgpass file.

psql -U <user name> -h <PostgreSQL instance Name> <DB Name> -p 5432 -W -c "select * from tb1"

Password for user imsuperuser:

 id

----

 3

 1

 2