Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "admin"

Question

When I try to connect to my PostgreSQL server using my credentials using (pgadmin iii - postgres sqltool) it works fine. When I try to connect from my java application I get the below logs. It's weird

org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.postgresql.util.PSQLException: FATAL: password authentication failed for user "admin"
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:240)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy8.getCollegeDetails(Unknown Source)
    at com.cts.bo.HESBO.registerCourse(HESBO.java:42)
    at com.cts.facade.HESFacade.registerCourse(HESFacade.java:34)
    at com.cts.manager.HESManager.registerCourse(HESManager.java:34)
    at com.cts.presentation.Tester.registerCourse(Tester.java:66)
    at com.cts.presentation.Tester.main(Tester.java:159)
**Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "admin"**
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:415)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:188)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:143)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
    at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
    at org.postgresql.jdbc3g.Jdbc3gConnection.<init>(Jdbc3gConnection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:412)
    at org.postgresql.Driver.connect(Driver.java:280)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119)
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:202)
    ... 11 more

.properties file

jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/postgres

jdbc.password=admin
jdbc.username=admin

spring.xml

<bean
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location">
            <value>com\cts\resource\constant.properties</value>
        </property>
    </bean>

    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean> 

    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

Any help on this is much appreciated, I am just not able to progress because of this weird error.

Answer 1

We had a similar issue but it was the new line in the password which was stored in GCP secret manager. The same password worked on oracle. So oracle is smart enough to trim the password

Answer 2

You might need to allow an IP or a range of IP addresses to connect to PostgreSQL.

If you are using Docker, for example, the problem might be that you didn't allow the Docker network for PostgreSQL.

1. Get the host IP address on the Docker network interface (ip addr show docker0 | grep 'inet');
2. Allow your IP addresses to connect to Postgre in pg_hba.conf.

If you only want to allow an IP address, for instance, either use /32 at the end of the address or write the 255.255.255.255 subnet mask after as shown bellow:

host    all     all     172.17.0.1        255.255.255.255     md5

or

host    all     all     172.17.0.1/32     md5

Read the PostgreSQL documentation for more information on the structure of the pg_hba.conf file.

Answer 3

For me, its worked when i updated the version

<dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
        <version>42.5.0</version>
</dependency>

Answer 4

I had the same problem, although I was running the PostgreSQL server inside a Docker container. I fixed it by using a different port mapping (e.g. 0.0.0.0:5433->5432/tcp).

Symptoms and debugging:

• The error message was "org.postgresql.util.PSQLException: FATAL: password authentication failed for user xxx".

• Changing JDBC host, port or username resulted in a different error message.

• PgAdmin was able to connect to the database.

• PgAdmin connections were shown in the PostgreSQL log (after enabling connection logging), but nothing was shown when trying to connect with JDBC.

• tcpdump port 5432 showed nothing when trying to connect with JDBC, but showed something when connecting with PgAdmin, or when sending random data with netcat.

In the end the root cause remained unclear. But obviously it was not a password problem, so the JDBC driver shouldn't say it is.

Docker version 20.10.17 running on Windows 10. PostgreSQL driver version was 42.4.1.

Answer 5

I think the problem might be that the postgres database is configured to allow the only password that are hashed with md5.

You can check if this is true by going to the configuration file pg_hba.conf. Search with the following property host all all all if you find that the property is configured like below:

host all all all md5

than the issue is that you need to hash the password to md5. You can fix this by changing the configuration to:

• trust instead of md5 -> will allow user to connect without any password
• password instead of md5 -> will allow user to connect with plain text password

Answer 6

Create a new user under Login section and using that in application.properties, solves this problem.