Spring Boot Fails to Connect to PostgreSQL When Username and Database Name Are the Same

Question

I'm working with Spring Boot and trying to connect to a PostgreSQL database. I've encountered an unusual issue where the connection fails when the username and database name are the same. This issue doesn't seem to occur when they are different.

Here's the relevant part of my application.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/mydatabase
spring.datasource.username=mydatabase
spring.datasource.password=mypassword

I've checked the following:

• The database URL, username, and password are correct.
• PostgreSQL is running and accessible.
• There are no apparent network issues affecting connectivity.
• The PostgreSQL JDBC driver is included in the project dependencies.

I've also confirmed that the pg_hba.conf file allows connections from the host where the application is running.

Despite this, the connection fails specifically when the username and database name are the same. If I change either the username or the database name (assuming the corresponding user/db exists), the connection works.

Is there a known issue or limitation in Spring Boot or PostgreSQL that could cause this behavior? Any suggestions on how to troubleshoot or fix this would be greatly appreciated.

Answer 1

This issue might be related to a conflict between the default PostgreSQL behavior and how Spring Boot configures the data source. To resolve this, you can try specifying the connection properties explicitly. Update your application.properties as follows:

spring.datasource.url=jdbc:postgresql://localhost:5432/mydatabase
spring.datasource.username=mydatabase
spring.datasource.password=mypassword
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false
spring.datasource.hikari.connectionInitSql=SET SEARCH_PATH TO mydatabase

This configuration includes setting the driver-class-name, disabling Hibernate's use of JDBC metadata defaults, and explicitly setting the SEARCH_PATH to the database name. The SEARCH_PATH is a PostgreSQL schema search path, and specifying it explicitly may help in cases where the username and database name are the same.

Try these configurations and see if the issue persists.