configure dedicated db user for database migrations: DML-only user for datasource, but DDL user for migration

[Felk]

Description

My usecase is simple in theory:

• Database migrations (using either flyway or liquibase) are run using a database user with DDL grants
• The datasource itself uses a database user without DDL rights afterwards, so preferably only DML.

This is a safety measurement against theoretical situations where the application goes haywire for any reason. This includes e.g. accidentally setting quarkus.hibernate-orm.database.generation to drop-and-create. If the application runs with a restricted database user, damage in such situations can be mitigated.

This implies the possibility to configure a separate username-password-pair that is used for the database migration only. Right now this does not seem to be possible out-of-the-box. Two workarounds right now are:

• Configuring an entirely new datasource with different credentials and only setting migrate-at-start for that one. This requires a bit of config duplication and fiddling to get right but can be made to work.
• Not using migrate-at-start and instead manually migrating in-code: For example when using flyway, if you manually create a Flyway object in code you are able to provide a different username and password for the flyway config. But this then doesn't let you use hibernate schema validation anymore, because the flyway migration happens after the validation.

Implementation ideas

I am assuming the use of flyway again, since that's what I am currently using. But some ideas may also apply to liquibase.

From a high-level view I could imagine there to be two additional configurations:

# Name of a custom database user for flyway to use. Must have DDL rights
quarkus.flyway.username=my_schema_owner
# Password of the custom database user for flyway
quarkus.flyway.password=my_secret_password

These configs could then be passed through to the FlywayCreator, which replaces configure.dataSource(dataSource); with configure.dataSource(jdbcUrl, username, password); if the custom username and password are set.
Alternatively, the username and password can be forwarded to the flyway config and flyway itself could be modified to use the DataSource#getConnection(String username, String password) overload instead of DataSource#getConnection() if it sees a username and password in addition to a datasource in its configuration.

What do you think? Is this a sensible usecase to consider?

[quarkus-bot]

/cc @DavideD, @Sanne, @andrejpetras, @barreiro, @cristhiank, @gastaldi, @gavinking, @geoand, @gsmet, @mswatosh

[mfpc]

Hi,

How can I check if the property is setted? Could anyone help me with this? Which class should I look?

[gastaldi]

Make the property an Optional<String> and check if it is present

[mfpc]

This wil be created at FlywayDataSourceRuntimeConfig right?

[gastaldi]

This wil be created at FlywayDataSourceRuntimeConfig right?

Correct.

[mfpc]

/FlywayDataSourceRuntimeConfig.java:[45,29] Unable to find javadoc for config item io.quarkus.flyway.runtime.FlywayDataSourceRuntimeConfig password

What should to do in this case?

[gastaldi]

Add a Javadoc?

[joaomfas]

Is it possible to also have this feature for liquibase?

[gastaldi]

Is it possible to also have this feature for liquibase?

Yes, that would be nice to have. Could you open a new issue requesting this? Or provide a Pull-Request?

[famod]

I've created a new issue for the liquibase part: #31214