title: Postgres: granting access to a role/user for future tables created by a different role/user
body: <p>I'm building a spring boot application. Flyway database migrations are executed at the application startup. </p>
  
  <p>I decided to use two different roles: <strong>role__app</strong> (<strong>read/write</strong> rights on tables, sequences in <strong>app</strong> schema) and <strong>role__migration</strong> (<strong>advanced</strong> rights in <strong>app</strong>/<strong>migration</strong> schemas). </p>
  
  <p>Flyway migrations are executed under <strong>role__migration</strong> so it becomes the owner of the created objects. I thought that the following statements would help:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>But when the new tables are added to the <strong>app</strong> schema the <strong>user__app</strong> (belongs to the <strong>role__app</strong>) doesn't have access to the tables.</p>
  
  <p>Is it possible to maintain such a flow (with app, migrattion users/roles) by Postgres or by any other means?</p>
  
  <hr>
  
  <p>As a side note I should mention that I run the following statements on the target database:</p>
  
  <pre><code>REVOKE CREATE ON SCHEMA public FROM PUBLIC;
  REVOKE ALL ON DATABASE myDb FROM PUBLIC;
  </code></pre>
  
  <hr>
  
  <p>Update 1</p>
  
  <p>I added the <code>FOR ROLE</code> clause, yet I'm still getting the permission denied message for a created table (<strong>app.property</strong>) in app schema for user <strong>user__app</strong>. The owner of the table is <strong>user__mig</strong>.</p>
  
  <p>Update 2</p>
  
  <p>After logging in as postgres user in dbeaver we can see that <strong>user__mig</strong> has all necessary permissions ticked whereas the <strong>user__app</strong> has no permissions on the <strong>app.property</strong> table at all:</p>
  
  <p><img src="https://i.stack.imgur.com/DZWVp.png" alt="enter image description here"></p>
  
  <hr>
  
  <p>Here is a gist to reproduce the problem: <a href="https://gist.github.com/happygrizzly/849a6a791f028ba5b191f73180ae35d1" rel="nofollow noreferrer">https://gist.github.com/happygrizzly/849a6a791f028ba5b191f73180ae35d1</a></p>