Search code examples
postgresqlspring-bootmigrationflyway

Postgres: granting access to a role/user for future tables created by a different role/user

I'm building a spring boot application. Flyway database migrations are executed at the application startup.

I decided to use two different roles: role__app (read/write rights on tables, sequences in app schema) and role__migration (advanced rights in app/migration schemas).

Flyway migrations are executed under role__migration so it becomes the owner of the created objects. I thought that the following statements would help:

ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role__app;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT USAGE ON SEQUENCES TO role__app;

But when the new tables are added to the app schema the user__app (belongs to the role__app) doesn't have access to the tables.

Is it possible to maintain such a flow (with app, migrattion users/roles) by Postgres or by any other means?

As a side note I should mention that I run the following statements on the target database:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE myDb FROM PUBLIC;

Update 1

I added the FOR ROLE clause, yet I'm still getting the permission denied message for a created table (app.property) in app schema for user user__app. The owner of the table is user__mig.

Update 2

After logging in as postgres user in dbeaver we can see that user__mig has all necessary permissions ticked whereas the user__app has no permissions on the app.property table at all:

enter image description here

Here is a gist to reproduce the problem: https://gist.github.com/happygrizzly/849a6a791f028ba5b191f73180ae35d1

Solution

  • You should write

    ALTER DEFAULT PRIVILEGES FOR USER role__migration ...

    If you omit the FOR USER clause, the privileges are only granted on objects created by the user who ran ALTER DEFAULT PRIVILEGES.

    With the above statement, the privileges are granted when role__migration creates an object. That does not extend to members of the role role__migration.