What is the purpose of the .pgpass file, and how do you use it to securely store PostgreSQL passwords? — PostgreSQL Interview Question #40

The .pgpass file is a plain text file that allows PostgreSQL users to securely store their passwords, and avoid the need to enter them manually every time they connect to a PostgreSQL instance. This file is particularly useful for scripting and automation tasks, as well as for interactive use.

The .pgpass file contains one entry per line, with fields separated by colons. The fields represent, in order: the hostname, the port number, the database name, the username, and the password. Each field can be replaced with an asterisk (*) to match any value. Comments can be added by prefixing a line with the hash symbol (#).

To use the .pgpass file, the file permissions must be set to 0600 (read/write only for the owner), as the file contains sensitive information. When connecting to PostgreSQL, the client library (such as libpq in C, or the JDBC driver in Java) first checks if a .pgpass file exists in the user’s home directory. If it does, it reads the file and attempts to match the connection parameters with each entry in the file, in the order they appear. If a match is found, the corresponding password is used to authenticate the user.

Here is an example of a .pgpass file:

# This is a comment
localhost:5432:mydb:myuser:mypassword
*:5432:*:myotheruser:myotherpassword

In this example, the first line matches connections to localhost on port 5432, to the database "mydb", with the username "myuser". The corresponding password is "mypassword". The second line matches connections to any host on port 5432, to any database, with the username "myotheruser". The corresponding password is "myotherpassword".

In Java, to use the .pgpass file with the JDBC driver, you can set the "user" property to the desired username, and leave the "password" property unset or null. The driver will automatically look for a .pgpass file in the user’s home directory and use it to authenticate the user. Here is an example:

import java.sql.*;

public class Example {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://localhost/mydb";
        String user = "myuser";
        String password = null; // Use .pgpass file
        Connection conn = DriverManager.getConnection(url, user, password);
        // Use the connection
    }
}