PostgreSQL grant privileges for future tables

privileges.md

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 object
• ALTER 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 the ALTER DEFAULT PRIVILEGES FOR USER appowner ... form