Does the Postgresql JDBC Driver support Pgpass authentication?

Question

I am trying to connect to a Postgresql database from Java using the JDBC Driver and would like to use pgpass to authenticate.

My Postgresql server is correctly setup for password authentication and I have a local .pgpass file; I can connect using psql without having to provide the user's password. However, when I try to open a connection in Java, I get the following error:

org.postgresql.util.PSQLException: The server requested password-based authentication, but no password was provided.

I am using the following code:

String url = "jdbc:postgresql://localhost/test";
Properties props = new Properties();
props.setProperty("user","my_user");
Connection conn = DriverManager.getConnection(url, props);

My question is then: does the Postgres JDBC Driver support pgpass?

The only clue I could find is in SO and seems to indicate that since the driver does not use libpq, then it cannot use pgpass. I can't seem to find an authoritave answer anywhere though.

Grabble

Answer 1

No, PgJDBC doesn't read .pgpass.

$ cd projects/pgjdbc
$ git grep pgpass
$ 

Your Java program would need to open and parse .pgpass then do appropriate matching for the connection.

If you write a class to read and parse .pgpass and to match it for a given set of connection parameters, please submit it for inclusion in the PgJDBC driver, where it'd go under the package org.postgresql.util.

You might find it easier to instead modify the JDBC driver directly, looking up the password if none is specified in the connection parameters once the JDBC driver has already parsed the JDBC URL into host, port, etc.

Forwardness

Answer 2

Here is a tool to read password from .pgpass file in Scala,

object DbUtil {
  def dbPassword(hostname:String, port:String, database:String, username:String ):String = {
    // Usage: val thatPassWord = dbPassword(hostname,port,database,username)
    // .pgpass file format, hostname:port:database:username:password
    val passwdFile = new java.io.File(scala.sys.env("HOME"), ".pgpass")
    var passwd = ""
    val fileSrc = scala.io.Source.fromFile(passwdFile)
    fileSrc.getLines.foreach{line =>
      val connCfg = line.split(":")
      if (hostname == connCfg(0)
        && port == connCfg(1)
        && database == connCfg(2)
        && username == connCfg(3)
      ) { 
        passwd = connCfg(4)
      }
    }
    fileSrc.close
    passwd
  }

  def passwordFromConn(connStr:String) = {
    // Usage: passwordFromConn("hostname:port:database:username")
    val connCfg = connStr.split(":")
    dbPassword(connCfg(0),connCfg(1),connCfg(2),connCfg(3))
  }
}

Discommode

pgpass may contain wildcard * – Superadd

Answer 3

Java Example:

package com.example.package;

import java.io.File;
import java.io.IOException;
import java.nio.file.Path;
import java.nio.file.Paths;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.Scanner;

public class PostgreSQLExample {
public static void main(String[] args) {
    getFromPGSQL();
}
public static void getFromPGSQL() {
    String jdbcUrl  = "jdbc:postgresql://localhost:5432/";
    String dbname   = "database_name";
    String username = "user_name";
    String password = getPassword(dbname, username);        
    Connection connection = null;

    try {
        connection = DriverManager.getConnection(jdbcUrl + dbname, username, password);
    } catch (SQLException sqle) {
        System.out.println("Cannot connect to PostgreSQL");
    }
    if (connection != null) {
        String select = "SELECT column FROM table";
        try(Statement st = connection.createStatement()) {
            ResultSet queryResult = st.executeQuery(select);
            String column = "";
            System.out.println(queryResult);
            while (queryResult.next()) {
                column = queryResult.getString("column");
                System.out.println(column);
            }
        } catch (SQLException ex) {
            System.out.println("Cannot execute query " + select);
        }
        
        String insert = "INSERT INTO table (column) VALUES (?)";
        try (PreparedStatement st = connection.prepareStatement(insert)) {
            st.setString(1, "Value");
            st.executeUpdate();
            st.close();
        } catch (SQLException ex) {
            System.out.println("Cannot execute query " + insert);
        }
        // Close the connection
        try {
            connection.close();
        } catch (SQLException sqle) {
            System.out.println("Cannot disconnect from PostgreSQL");                
        }
    }
}
private static String getPassword(String dbname, String username) {
    String pss;
    try {
        pss = readPgpass(dbname, username);
    } catch (IOException ioe) {
        Scanner keyboard = new Scanner(System.in);
        System.out.println("Cannot read pgpass file. Please provide password for db academy_top user tutor:");
        pss = keyboard.nextLine();
        keyboard.close();
    }
    return pss;
}
private static String readPgpass(String dbname, String username) throws IOException {
    // Join standard path for pgpass in our WINDOWS system
    Path filePath = Paths.get(
            System.getenv("APPDATA"),
            "postgresql",
            "pgpass.conf");
    // Open file
    String pss = "";
    String line = "";
    String[] parts;
    System.out.println(filePath.toString());
    File file = new File(filePath.toString());
    Scanner inputFile = new Scanner(file);
    // Read lines from the file until no more are left.
    while (inputFile.hasNext() && pss == "") {
        line = inputFile.nextLine();
        // hostname:port:dbname:username:password
        // 0        1    2      3        4
        System.out.println(line);
        parts = line.split(":");
        if (parts.length >= 5 && parts[2].equals(dbname) && parts[3].equals(username)) {
            pss = parts[4].trim();  // deleting end of line
            System.out.println(pss);    
        }   
    }
    // Close the file.
    inputFile.close();
    return pss;
}
}

Incised