Liquibase & Spring how to use separate user for schema changes

Question

I'm looking into Liquibase as a potential solution to deploy my web application using pre-existing database servers (of different types).

This application should access the database with a user that can only manipulate data, I would like to use a different user as schema owner. Since my application uses Spring I thought I could use the integration class, though it would mean I have to create a second datasource which will remain opened for as long as my application runs which defeats the purpose of separating accounts.

Does anyone ever faced the same problem ? Any idea for a solution ? I sure can execute liquibase manually and pass relevant information but I wondered if someone figured a cleaner approach.

Thanks in advance for your help.

Answer 1

Using Liquibase with Spring Boot (at least 2.0+), there is now the possibility to configure a separate Liquibase db user in your application.yml:

spring:
  datasource:
    driver-class-name: org.mariadb.jdbc.Driver
    url: jdbc:mariadb://...
    username: <app_user>
    password: ${DB_PASSWORD}
  liquibase:
    default-schema: my_schema
    user: <admin_user>
    password: ${DB_ADMIN_PASSWORD}

Answer 2

Thanks for your input Nathan, I looked into the Spring and CommandLine classes and ended up writing my own small static method which opens a DB connection with "owner" credentials, apply the changelog through this connection and close it.

I then call this method from a WebApplicationInitializer, allowing liquibase to run before Spring beans are initialized. This way I'm sure the changes are applied before any piece of code related with database runs plus I get to only have "user" connections through my data source.

And as a nice side-effect, that static method can also be used when starting unit tests, which allows me to run tests on a cleanly created database instead of relying on hibernate's DDL.

Here are the code examples, first is a utility class that encapsulate the logic to retrieve schema owner connection details from my application's properties file (http://pastebin.com/HP4HzFmp), it also has couple methods to ease integration with unit tests setup.

Then in each web application I'm using a WebApplicationInitializer from Spring which allows me to trigger liquibase changes application right when the application start (i.e. before Hibernate makes any request to the DB): http://pastebin.com/anXicM15. Doing it this way allows me to keep a complete separation between schema manipulations and data-related connection.

With regards to database connection details, I store all of them in a properties files which is read directly by this piece of code and used as placholder replacement source by Spring for the Hibernate configuration. If you want to achieve even further isolation you can replace the part that grabs connection details by a more secured location.

Answer 3

You are right that the standard SpringLiquibase integration only supports being passed a dataSource and that dataSource will live for the lifetime of your application.

If you use a connection pool, once Liquibase finishes the connection it uses will be closed so there should be no active connections, but you will still have the pool referenced to it in your application.

If you want to bypass an open dataSource, you will probably need to create a subclass of SpringLiquibase that overrides the afterPropertiesSet() method and the getDataSouce() method.

afterPropertiesSet is the main method that runs which creates the Liquibase instance then runs the update method. If your subclass creates a new connection however it wants then calls super.afterPropertiesSet(), then closes the connection you should be able to ensure everything is cleaned up. Within afterPropertiesSet the getDataSource() method is called, so you will need to override that to return the connection created in afterPropertiesSet().

You can see how SpringLiquibase is implemented at github.com/..../SpringLiquibase.java