Spring Boot makes database migration with Flyway or Liquibase almost effortless - but by default it will use the
spring.datasource.*
credentials when running the migrations, which kinda suggests that this user should have
ALL PRIVILEGES
on the schema. This is risky, because... well if you find this page, you probably familiar
with poor little Bobby Tables
.
Spring Boot also makes it possible to configure separate credentials for running database migrations with the
spring.flyway.*
or spring.liquibase.*
properties.
I prefer app credetials this way: an app owner
with ALL PRIVILEGES
to run the migrations and an app user
with least privileges, mostly CRUD
or some limited EXECUTE
for the app itself.
With mysql, configuring these users are simple: create them, grant, and lift off 🚀!
-- for mysql
create database appdb;
create user 'appowner'@'%' identified by password 'AppOwnerPass';
grant all on appdb.* to 'appowner'@'%';
create user 'appuser'@'%' identified by password 'AppUserPass';
grant insert, update, delete, select on appdb.* to 'appuser'@'%';
But for postgres, it's a bit different:
-- for postgresql
-- given that appdb was created and this session runs in that
create user appowner with password 'AppOwnerPass';
grant all privileges on database appdb to appowner;
create user appuser with password 'AppUserPass';
alter default privileges for user appowner in schema public grant insert, update, delete, select on tables to appuser;
For explanation, read the comments in this SO https://stackoverflow.com/questions/22684255/grant-privileges-on-future-tables-in-postgresq but TL;DR:
GRANT
only works for exinting objectALTER DEFAULT PRIVILEGES ...
will take effect when tables are created in te future by the current user (which is the superuser in our case)- when creating our
app owner
with the superuser, we should use theALTER DEFAULT PRIVILEGES FOR USER appowner ...
form