Connecting using Passfile

In real-world scenarios, the input of passwords should be automated when making a connection to the database. The password can also be given as part of a password file called passfile which will contain the password. This file specifies the name of the file used to store passwords and it defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on Microsoft Windows.

This file should contain lines of the following format:

hostname:port:database:username:password

• You can add a reminder comment to the file by copying the line above and preceding it with #.
• Each of the first four fields can be a literal value, or *, which matches anything.
• The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.)
• If an entry needs to contain : or \, escape this character with \.

Example entries:

host1:5432:appdb:appuser:secretpassword

host1:5433:appdb:appuser:secretpassword

host2:5432:hrdb:hruser:mypassword

host3:*:appdb:appuser:secretpassword

*:*:*:*:secretpassword

Important points to consider regarding the passfile.

• The passfile should be present on the client side, not the server side.
• The passfile should have protected permissions.
• The passfile should be readable by the operating system user trying to the connect to the database.

psql postgresql://hr_user@127.0.0.1:5433/appdb?'connect_timeout=10&keepalives=1&passfile=/home/mkm/.pwd'

Another example with connection details being given in form of key-value pairs:

psql -d "host=127.0.0.1 port=5433 dbname=appdb user=hr_user connect_timeout=10 passfile=/home/mkm/.pwd"