1. Introduction
A common pitfall when configuring a Datasource for our Spring Boot project with a PostgreSQL database is providing the wrong password for the database connection or even forgetting the password for the provided user.
This is why we might encounter errors when starting our project and connecting to the database. In this tutorial, we’ll learn how to avoid the PSQLException.
2. Datasource Configuration
Let’s examine the two most common techniques for configuring the Datasource and a database connection in Spring Boot. We can use only one of these two approaches: the file application.properties or the application.yml.
2.1. application.properties
Now we create and configure the application.properties file with the minimum required fields for the connection to have place:
spring.datasource.url=jdbc:postgresql://localhost:5432/tutorials
spring.datasource.username=postgres
spring.datasource.password=
spring.jpa.generate-ddl=true
The application creates the tables on startup by adding the property spring.jpa.generate-ddl.
We don’t provide the password inside the file and try to start from Command Prompt (on Windows) our application and see what happens:
mvn spring-boot:run
We notice there’s an error because of not providing a password for authentication:
org.postgresql.util.PSQLException: The server requested password-based authentication, but no password was provided.
This kind of message might slightly defer in case we’re using a newer version of the PostgreSQL database, and we might see the SCRAM-based authentication error instead:
org.postgresql.util.PSQLException: The server requested SCRAM-based authentication, but no password was provided.
2.2. application.yml
Before we continue, we must comment on the content of the file application.properties or remove the file from the solution so it won’t conflict with the application.yml file.
Let’s create and configure the application.yml file with the minimum required fields as we’ve done previously:
We’ve added the property generate-ddl to create the tables on startup.
By not completing the password inside the file and trying to start our application from Command Prompt, we notice the same error as before:
Also, the error message might slightly defer in this case by showing a SCRAM-based authentication error instead if we’re using a newer PostgreSQL database.
2.3. Providing the Password
In either of the configurations we choose to use, the server will start successfully if we input the password correctly into the requested parameter.
Otherwise, a specific error message is shown:
org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"
Now let’s use the correct password to establish the connection to the database successfully and the application is starting correctly:
2024-07-19T00:03:33.429+03:00 INFO 18708 --- [ restartedMain] com.baeldung.boot.Application : Started Application in 0.484 seconds
2024-07-19T00:03:33.179+03:00 INFO 18708 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-9 - Starting...
2024-07-19T00:03:33.246+03:00 INFO 18708 --- [ restartedMain] com.zaxxer.hikari.pool.HikariPool : HikariPool-9 - Added connection org.postgresql.jdbc.PgConnection@76116e4a
2024-07-19T00:03:33.247+03:00 INFO 18708 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-9 - Start completed.
3. Database Password Reset
Alternatively, we have options for changing or resetting the password of a database user or the default user if we forget or choose to do so.
Now let’s dive into details on how to reset the PostgreSQL password for the default user postgres.
3.1. Reset the Password for the Default User
First, we identify the location of the data directory where PostgreSQL is installed, if on Windows, ideally inside “C:\Program Files\PostgreSQL\16\data“.
Then, let’s backup the pg_hba.conf file by copying it to a different location or renaming it to pg_hba.conf.backup. Open a Command Prompt inside the data directory and run the command:
copy "pg_hba.conf" "pg_hba.conf.backup"
Second, edit the pg_dba.conf file and change all local connections from scram-sha-256 to trust so we can log into the PostgreSQL database server without a password:
# 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 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
Third, restart the PostgreSQL server (on Windows) using the Services feature. Alternatively, we can run the following command as the Administrator in the Command Prompt instead:
pg_ctl -D "C:\Program Files\PostgreSQL\16\data" restart
Afterward, we use tools like psql or pgAdmin to connect to a PostgreSQL database server.
Open a Command Prompt inside the bin directory under the PostgreSQL installation folder, and type the following psql command:
psql -U postgres
We have logged into the database, as PostgreSQL requires no password. Let’s change the password for user postgres by executing the following command:
ALTER USER postgres WITH PASSWORD 'new_password';
Lastly, let’s restore the pg_dba.conf file and restart the PostgreSQL database server as before. Now, we can use the new password inside our configuration file to connect to the PostgreSQL database.
3.2. Reset the Password for Any Other User
By choosing to do it with psql (on Windows) we open a Command Prompt inside the PostgreSQL installation bin directory and run the command:
psql -U postgres
We then provide the password for the postgres user and login.
After login as superuser postgres, let’s change the password for the user we want to:
ALTER USER user_name WITH PASSWORD 'new_password';
4. Conclusion
In this article, we’ve seen a common connection issue when configuring the Datasource in a Spring Boot application and the various options we have to solve it.
As always, the example code can be found over on GitHub.