PostgreSQL Password File

Summary: In this tutorial, you’ll learn how to create a PostgreSQL password file to store passwords securely and automatically connect to a PostgreSQL server.

PostgreSQL Password File Overview #

To connect to a PostgreSQL server, you need to provide a username (role) and password.

You can use a password file to save time typing a password every time you connect.

A PostgreSQL password file allows you to store connection parameters securely.

The password file can help automate a backup script that uses pg_dump and pg_dumpall.

Technically, a password file is a plain text file that includes one or more lines in the following format:

hostname:port:database:username:passwordCode language: plaintext (plaintext)

In this syntax:

• hostname: The PostgreSQL server hostname.
• port: The port on which the PostgreSQL server is listening.
• database: The database you want to connect to.
• username: The username (or role) you want to use to connect to the database.
• password: The password of the role.

Each line in the .pgpass file represents information about a connection to a PostgreSQL database.

You can use an asterisk (*) for a value to match any value. The psql reads the password file from top to bottom and uses the first matching line.

For this reason, you need to use the lines with more specific information followed by lines with more general information to achieve the desired behavior.

Creating a PostgreSQL Password File on Windows #

PostgreSQL stores the password as pgpass.conf in the directory %APPDATA%\postgresql\ on Windows. The %APPDATA% is the application data subdirectory in the path. For example, on Windows 11 the %APPDATA% is as follows:

C:\Users\<YourUsername>\AppData\Roaming\postgresqlCode language: plaintext (plaintext)

You can store the password file in another directory by setting the PGPASSFILE environment variable to the password file path.

Step 1. Open a plain text editor (Notepad).

Step 2. Enter the connection information for your PostgreSQL database in the above format. For example:

localhost:5432:*:postgres:securepasswordCode language: plaintext (plaintext)

Replace the information, including host, port, username, and password, with yours.

Step 3. Save the file with the name pgpass.conf in the following path:

C:\Users\<YourUsername>\AppData\Roaming\postgresql\pgpass.confCode language: plaintext (plaintext)

Replace <YourUsername> with your username on Windows.

Step 4. Open the Command Prompt and execute the psql command:

psqlCode language: Shell Session (shell)

You should be able to connect to the PostgreSQL database automatically.

Creating a PostgreSQL Password File on Linux #

Step 1. Open a terminal and use a text editor like Vi, or Nano to create a .pgpass file:

nano ~/.pgpassCode language: Shell Session (shell)

Step 2. Enter the connection information for your PostgreSQL database:

localhost:5432:*:postgres:securepasswordCode language: plaintext (plaintext)

Step 3. Save the file and exit. 4. Grant proper permission on the .pgpass file:

chmod 600 ~/.pgpassCode language: Shell Session (shell)

Step 4. Run the psql command to automatically connect to the PostgreSQL database stored in the password file:

psqlCode language: Shell Session (shell)

Summary #

• Use a PostgreSQL password file (pgpass.conf on Windows and .pgpass on Linux) to store connection information securely and connect to PostgreSQL databases automatically.