Flyway vs Principle of Least Privilege in Spring Boot

It’s nice to use Flyway or Liquibase or something similar to manage the evolution of DB schemas in your service. It’s even nicer when with a bit of Spring magic the migrations are applied automagically on service launch. This is possible with Flyway as described in the Spring Boot docs.

However, when using this in production the important question is how do you still apply the Principle of Least Privilege while benefiting from the convenience of Flyway managing your schemas.

I suggest creating a separate user for flyway that is NOT the same user used by the rest of your Spring service to access the database. This lowers the risk of your DB being compromised if your service is compromised through an attack like SQL injection.

There was surprisingly little online on what permissions this user should have, so here’s what I did starting with this StackOverflow answer and reviewing the Postgres docs.

application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432:/postgres
    username: service_user
    password: ${SERVICE_DB_PASSWORD}
  flyway:
    user: flyway_user
    password: ${FLYWAY_DB_PASSWORD}

SQL to create role and apply it to the user

-- Create a Migrator Role
CREATE ROLE schema_migrator;
GRANT CONNECT ON DATABASE postgres TO schema_migrator;
GRANT USAGE ON SCHEMA public TO schema_migrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO schema_migrator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO schema_migrator;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO schema_migrator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO schema_migrator;

-- Assign Role to flyway_user
CREATE USER flyway_user WITH password 'abc123';
GRANT schema_migrator TO flyway_user;

If you read this far, how have you handled this situation?