How to SET ROLE for liquibase to use for DATABASECHANGELOGTABLE-creation

Question

My current setup requires that the whole liquibase-migration is executed with a specific DB-role.

I currently have the following changeSet on top of my script:

- changeSet:
    id: init-role
    author: me
    runAlways: true
    dbms: postgresql
    sql: 'SET ROLE TO my-role'

With this all changesets are executed with the role, BUT the databasechangelog-table is not and thus liquibase fails because it has no permission to write in that table.

Question: How can I tell liquibase to use my role also for the creation and the insertion in the databasechangelog-table?

Answer 1

So - I've just encountered this myself, given that PostgreSQL's user model is really, really counterintuitive_. I had this issue because I was giving a second role default permissions from my upgrader (DDL allowed) role and nothing was being inherited because postgres was creating everything with the USER as owner.

Assuming the following:

• There is a ROLE that has permission to run DDL in the database
• Your DDL is being run by a user which is already in the group to which you want to assign ownership of the objects
• There is no split-ownership in the database - everything can be owned by the upgrade_user group.

My solution is the following:

databaseChangeLog:

  # The below ChangeSets are a requirement and need to be run before
  # liquibase does anything else. Because of the unique and interesting way
  # PostgreSQL handles users and roles any created objects need to be owned
  # by the ROLE and not the USER in order to allow default permissions to
  # track through to the application user.
  - changeSet:
      id: 0
      author: [email protected]
      comment: | 
        Reassigns entities created by the current user to the role from which
        it gets its permissions, principally to enable ownership of liquibase
        changelog tables
      dbms: postgresql
      # Context is set so that this will only run against "real" database
      # instances, not local development databases, for which there is no
      # distinction between DDL and DML user identities
      context: "!local"
      changes:
        - sql:
            dbms: 'postgresql'
            sql: REASSIGN OWNED BY CURRENT_USER TO upgrade_user

  - changeSet:
      id: 1
      author: [email protected]
      runAlways: true
      # Context is set, for more information see ChangeSet (0)
      context: "!local"
      comment: |
        Forces liquibase to run commands as the GROUP role, not the user
        who is a member of that group. This is required to allow the default
        permissions to flow through to the application user.
      dbms: postgresql
      changes:
        - sql:
            dbms: 'postgresql'
            sql: SET ROLE TO upgrade_user

  # Add the per-release changelogs below here
  - include:
      file: db/changelog/db.changelog-v0.0.0.xml
  - include:
      file: db/changelog/db.changelog-v1.1.0.xml