title: Is there a way to grant permissions on tables that don't exist yet (or that get recreated)?
body: <p>I have a database with a read-only role set up; let's call it 'app_user_ro'. I am using a third party data loading tool (running as 'app_user') that drops and recreates the tables. After recreating the tables, the previous permissions are gone and 'app_user_ro' can no longer select from the tables.</p>
  
  <p>I am using Postgres 11.3 running on Ubuntu 19.04. I've looked at <a href="https://stackoverflow.com/questions/22684255/grant-privileges-on-future-tables-in-postgresql">Grant privileges on future tables in PostgreSQL?</a> and tried altering the default privileges but still 'app_user_ro' can't read data from the tables after they are dropped and recreated.</p>
  
  <p>I've tried:  </p>
  
  <pre class="lang-sql prettyprint-override"><code>-- The accepted answer
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user_ro;
  
  -- An alternative answer from the comments:
  ALTER DEFAULT PRIVILEGES FOR USER app_user_ro IN SCHEMA public GRANT SELECT ON TABLES TO app_user_ro;
  </code></pre>
  
  <p>The only thing that works is manually granting the permissions after each import:  </p>
  
  <pre class="lang-sql prettyprint-override"><code>GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user_ro;
  </code></pre>
  
  <p>The <a href="https://www.postgresql.org/docs/11/sql-alterdefaultprivileges.html" rel="nofollow noreferrer">docs</a> state:  </p>
  
  <blockquote>
    <p>You can change default privileges only for objects that will be created by yourself or by roles that you are a member of</p>
  </blockquote>
  
  <p>The roles I had were:  </p>
  
  <pre class="lang-sql prettyprint-override"><code>app_data_loader=# \du
                                        List of roles
     Role name    |                         Attributes                         | Member of  
  ----------------+------------------------------------------------------------+------------
   app_user       |                                                            | {postgres}
   app_user_ro    |                                                            | {}
   pg             | Superuser                                                  | {}
   postgres       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  </code></pre>
  
  <p>Originally I was running the <code>ALTER DEFAULT...</code> commands as the 'postgres' role but more recently I added the 'app_user_ro' role to the 'app_user' role (the one loading the data) and tried running it as the 'app_user' role with no change in behaviour.</p>
  
  <p>For now I'll just schedule a job to grant the permissions after each import but it seems like there should be a better way!</p>