How to use .pgpass in PostgreSQL?

.pgpass is a common way to store connection information in PostgreSQL instead of typing password every time your login to your database using psql.

On Windows, the file is named %APPDATA%\postgresql\pgpass.conf.

You can create a .pgpass file in your home folder and input your login credentials with the following structure:

host:port:db_name:user_name:password

Example:

localhost:5432:postgres:myadmin:Str0ngP@ssw0rd

Except for the password, each field can be replaced with an asterisk (*) value to match anything.

Example:

*:*:*:postgres:myadmin:Str0ngP@ssw0rd

It means you can use the username myadmin and password Str0ngP@ssw0rd to login to the database postgres on every host and port.

After you finish, remember to set the proper permission so it would be used by psql:

sudo chmod 600 .pgpass

Export the PGPASSFILE file to set environment variable:

export PGPASSFILE='/home/user/.pgpass'

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS