SpringBoot Treating a null Flyway-specific password as an empty string prevents the use of PGPASS for authentication

In order to avoid having PostgreSQL password in source code or environment variables, we use PGPASS in development environments. See https://www.postgresql.org/docs/current/libpq-pgpass.html for details.

I think after gh-35046 related changes, Flyway is sending empty password (instead of null) to PostgreSQL during authentication. This prohibits driver from attempting PGPASS based authentication (which requires null password). This leads to following exception when password isn't set by the application -

Caused by: org.postgresql.util.PSQLException: The server requested SCRAM-based authentication, but the password is an empty string.
    at org.postgresql.core.v3.ConnectionFactoryImpl.lambda$doAuthentication$4(ConnectionFactoryImpl.java:844) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.core.v3.AuthenticationPluginManager.withPassword(AuthenticationPluginManager.java:81) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:835) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:203) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:258) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:263) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.Driver.makeConnection(Driver.java:443) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.Driver.connect(Driver.java:297) ~[postgresql-42.6.0.jar:42.6.0]
    at org.springframework.jdbc.datasource.SimpleDriverDataSource.getConnectionFromDriver(SimpleDriverDataSource.java:144) ~[spring-jdbc-6.0.8.jar:6.0.8]
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:205) ~[spring-jdbc-6.0.8.jar:6.0.8]
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:169) ~[spring-jdbc-6.0.8.jar:6.0.8]
    at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:48) ~[flyway-core-9.16.3.jar:?]
    at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:74) ~[flyway-core-9.16.3.jar:?]
    at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:140) ~[flyway-core-9.16.3.jar:?]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:140) ~[flyway-core-9.16.3.jar:?]
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-3.1.0-RC1.jar:3.1.0-RC1]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1816) ~[spring-beans-6.0.8.jar:6.0.8]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1766) ~[spring-beans-6.0.8.jar:6.0.8]

Sample pgpass-demo.zip

Comment From: wilkinsona

This has been fixed in 3.1.0 as a side-effect of the changes for #35109.

As far as I can tell, there's always been a problem in this area, 3.1.0 just broadened the scope of it. The same problem can occur with 2.7 and 3.0, but it requires a Flyway-specific URL or user:

spring.flyway.user = migrations

This configuration brings FlywayProperties.getPassword() into play and it coerces the null password into an empty string:

https://github.com/spring-projects/spring-boot/blob/be698cf6edfe68adf53421d4aa16e8b9a4965d73/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/flyway/FlywayProperties.java#L603-L605

We should just return null here as DataSourceProperties and LiquibaseProperties both do. I'll use this issue to make the necessary changes and merge forwards. That will also ensure that a test for this specific scenario is added to 3.1 (main).