Liquibase & Spring how to use separate user for schema changes

liquibase tutorial
liquibase java
liquibase github
liquibase spring
liquibase alternatives
liquibase gui
liquibase-oracle
liquibase python

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.

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}

Liquibase, is an open-source solution for managing revisions of your database schema scripts. It works across various types of databases and supports various file formats for defining the DB structure. All the flexibility of Liquibase with added features and support to help growing businesses scale. By buying Liquibase, you are agreeing to the terms of our EULA.

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.

Introduction to Liquibase and Managing Your Database Source , It's easy to get up and running with Liquibase. Select a topic below to get started. Liquibase is so great we built our company on it. Datical is a Commercial version of Liquibase. Datical was created to satisfy the database schema management requirements of Enterprises with Agile, DevOps, and Continuous Delivery initiatives that require accelerating Application Releases. .

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

Docs, Learn about tracking, deploying & versioning your databases with Liquibase! Liquibase Pro adds capabilities that enhance and extend the base open source features, such as Targeted Rollbacks to update a specific change or set of changes and working with Stored Logic objects. Show me pricing

Liquibase, Liquibase is an open source solution for managing revisions of your databse schema scripts. It Duration: 11:47 Posted: Oct 16, 2017 Name Description Required for Supports Multiple allowed; columns: Column constraint and foreign key information. Setting the "defaultValue" attribute will specify a default value for the column.

Docs, When you issue a Liquibase update command, Liquibase looks at the current state of your DB and identifies which changes have already  Everything in Liquibase plus: Targeted rollbacks to undo one change or an entire set of changes Expanded Stored Logic functionality, including snapshots and rollbacks Advanced, personalized support from Liquibase experts Easier-to-use changelog All the flexibility of Liquibase Community

Liquibase, Liquibase is a tool for database versioning, it does migrations and has built-in refactorings. Commbined with feature flags, it helps ease  I believe Liquibase is great when your philosophy is that the database is an afterthought. This philosophy has caused the majority of bad databases in production - and most of them are bad. A database should be designed with a full view of the entire business system, not in pieced by application developers each working in their own silos.

Comments
  • I'm in the exact situation as your problem. Can you please post your solution code or a link to github gist ? That will be of great help.
  • did you not require to use SpringLiquibase in your solution?
  • @TechCrunch, I edited the answer with code examples, hope it'll help you.
  • Thank you very much. I went ahead with SpringLiquibase and second datasource for now but still considering doing it your way. Did you face any issues so far for upgrading DB directly in the production application ? Some folks in team want it to be running manually outside the application.
  • No issues so far, I'm running the exact same updates from multiple web apps, though they all start in sequence with Tomcat. Therefore only the first one actually applies anything. I haven't tested that with many nodes starting at once, though running changes in transactions should keep you safe with all DBMS properly handling transactions.