SpringBoot Databases that support embedded and non-embedded modes are always detected as embedded

I have an h2 database created without username/password. I have a basic spring boot web+jpa application, in my application.properties I have just

spring.datasource.url = jdbc:h2:~/test

I can access with the h2 driver from the commandline, for example (using absolute path to show the versions)

> "c:\Program Files\AdoptOpenJDK\jdk-11.0.7.10-hotspot\bin\java.exe" -cp c:\Users\vito.detullio\.m2\repository\com\h2database\h2\1.4.200\h2-1.4.200.jar org.h2.tools.Shell -url jdbc:h2:~/test

Welcome to H2 Shell 1.4.200 (2019-10-14)
Exit with Ctrl+C
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> create table t(id Number primary key, label varchar2);
(Update count: 0, 16 ms)
sql> select * from t;
ID | LABEL
(0 rows, 22 ms)
sql> quit
Connection closed

>

Unfortunately if I launch the application I have

org.h2.jdbc.JdbcSQLInvalidAuthorizationSpecException: Wrong user name or password [28000-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:461) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.200.jar:1.4.200]
...

from what I understood the problem is that by default the username set up is SA. My problem is that I can't find a way to set "no username" in the application.properties.

I tried with

spring.datasource.username = 
spring.datasource.username = "" 
spring.datasource.username = null

but all of them ended with the same error

It seems to me that it's not possible to set "no username"

Comment From: wilkinsona

It seems to me that it's not possible to set "no username"

That's correct. Your H2 database is being identified as an embedded database so the username defaults to sa if it has no text (if it's null or an empty string).

I think it could be argued that H2 shouldn't be identified as embedded in this case as it's running separately. While we consider that, you should be able to work around the problem by post-processing the DataSource:

@Bean
public static BeanPostProcessor clearDataSourceUserName() {
    return new BeanPostProcessor() {

        @Override
        public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
            if (bean instanceof HikariDataSource) {
                ((HikariDataSource) bean).setUsername("");
            }
            return bean;
        }

    };
}

Comment From: wilkinsona

We should look for the mem part of the JDBC URL and only identify the database as embedded when it is present.

Comment From: somayaj

Can this be worked on?

Comment From: snicoll

@somayaj thanks for asking to contribute. This one may be a little hard to fix on surface but if you have already looked at it and have an idea how to fix it, please let us know and we can review a PR.

Comment From: somayaj

@snicoll yes, I've checked in the code changes. Please review. Thanks.

Comment From: snicoll

Closing in favour of PR #23693