Dedicated DDL user for liquibase migrations to avoid DDL permissions for app datasource user

[famod]

Description

Similar to what was done for flyway in #24639.

It would be nice if it was possible to set a dedicated user for liquibase migrations so that only that user needs DDL permissions and the app datasource user only needs DML permissions.

Implementation ideas

No response

[quarkus-bot]

/cc @andrejpetras (liquibase), @geoand (liquibase), @gsmet (liquibase)

[geoand]

This seems a little more complicated than the Flyway but hopefully we can get it in for 3.0

[tonbiklas]

Any news on this? This is something I would also like to have

[famod]

Btw, this would also help with having metrics (OTEL) only for the app datasource, not for the Liquibase ds.

[appiepollo14]

I'm working on this one. I've based my solution on the Flyway implementation. However, I can't figure out how to configure the dedicated username / password on the datasource. In this line: appiepollo14@a00548f#diff-1db6d04046bd4069a9e4ad8ad917d424386a6c63f88edd403853b0a4e1025b99R39 an error is thrown: username and password combination invalid on a pooled data source!

Is this thrown because there is already a username and password configured in the datasource and the ones I set are not the same? Any pointers?

@geoand you can assign this one to me

[famod]

@appiepollo14 cool, thanks for working on it! I don't have an answer to your question but I have assigned you as requested.

[geoand]

Is this thrown because there is already a username and password configured in the datasource and the ones I set are not the same? Any pointers?

What is the full stacktrace?

[appiepollo14]

@geoand

jakarta.ws.rs.WebApplicationException: java.sql.SQLException: username and password combination invalid on a pooled data source!
        at io.quarkus.it.liquibase.LiquibaseFunctionalityResource.updateWithDedicatedUser(LiquibaseFunctionalityResource.java:69)
        at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
        at java.base/java.lang.reflect.Method.invoke(Method.java:580)
        at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:154)
        at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:118)
        at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:560)
        at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:452)
        at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget$2(ResourceMethodInvoker.java:413)
        at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:321)
        at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:415)
        at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:378)
        at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:356)
        at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:70)
        at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:429)
        at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke$4(SynchronousDispatcher.java:240)
        at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(SynchronousDispatcher.java:154)
        at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:321)
        at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:157)
        at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:229)
        at io.quarkus.resteasy.runtime.standalone.RequestDispatcher.service(RequestDispatcher.java:82)
        at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler.dispatch(VertxRequestHandler.java:147)
        at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler$1.run(VertxRequestHandler.java:93)
        at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:599)
        at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
        at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
        at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
        at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.lang.IllegalStateException: java.sql.SQLException: username and password combination invalid on a pooled data source!
        at io.quarkus.liquibase.LiquibaseFactory.createLiquibase(LiquibaseFactory.java:68)
        at io.quarkus.liquibase.LiquibaseFactory_0QBYYClLHv6Q9wP1-j1NmFBNFQ8_Synthetic_ClientProxy.createLiquibase(Unknown Source)
        at io.quarkus.it.liquibase.LiquibaseFunctionalityResource.updateWithDedicatedUser(LiquibaseFunctionalityResource.java:57)
        ... 28 more
Caused by: java.sql.SQLException: username and password combination invalid on a pooled data source!
        at io.agroal.pool.DataSource.getConnection(DataSource.java:91)
        at io.quarkus.liquibase.LiquibaseFactory.createLiquibase(LiquibaseFactory.java:39)
        ... 30 more

[geoand]

The problem is that you can't use dataSource.getConnection(username, password) because the of connection pooling.
You'll need to build a java.sql.Connection manually.