Why am I getting SQL State: 28000 FATAL: Ident authentication failed for user error message when running a simple Java program?



I have a Postgres 11 database installed on a VirtualBox virtual machine running Centos7 (guest machine), and my host machine is a Mac OS Catalina. Both the guest (Centos) and host (Catalina) have JDK 8 installed.

I have the following simple Java code that uses a single dependency postgresql-42.2.5:

public class JavaApplication5 {

    public static void main(String[] args) {

        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5332/mydb", "mydbuser", "mydbpassword")) {

            if (conn != null) {
                System.out.println("Connected to the database!");
            } else {
                System.out.println("Failed to make connection!");
            }

        } catch (SQLException e) {
            System.err.format("SQL State: %sn%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
}

I compile and run this on my host (Catalina) :

java -jar JavaApplication5.jar 

and the output I get is:

Connected to the database!

So clearly this means I can connect to the Postgres database from my host to my guest. Next, I test whether I can connect directly from my guest. So I copy “JavaApplication5.jar” into guest Virtual Machine running Centos 7 and re-run the same command. However, this time I am getting the following message from the same executable jar file:

SQL State: 28000
FATAL: Ident authentication failed for user "mydbuser"

Does anybody know why I am getting this error on the guest virtual machine but not on the host?

Here is the relevant portion of my my pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host all all 0.0.0.0/0 md5

Answer

You must have port forwarding set up in your VirtualBox to capture connection attempts on the host to that port and route them to the VB. So while you specify 127.0.0.1, they are coming from someplace else as far as PostgreSQL is concerned. That matches the ‘md5’ line of your pg_hba.conf. When done from the guest, the connections really are coming from 127.0.0.1, and so match the earlier ‘ident’ line.

The ‘ident’ authentication is failing, for reasons surely mentioned in the log file. If you don’t want to use ‘ident’, remove those lines.



Source: stackoverflow