How to create a Postgres schema, let one role create tables in it, and let another role read from those tables

Question

I'm trying to set up a Postgres database using a process along the following lines:

1. A script is run, authenticated to role postgres that:

   • Creates a new, empty schema schema0.
   • Creates a role role0 with the ability to create and populate tables in schema0.
   • Creates a role role1 with the ability to retrieve data from any tables in schema0.

2. A migration task in AWS DMS, authenticated to role0, creates and populates an arbitrary number of tables in schema0.

3. role1 can be used to retrieve data from any tables in schema0.

The tricky part is giving role1, in advance, the ability to read from not-yet-created tables in schema0. I think I should be able to do this using ALTER DEFAULT PRIVILEGES. According to its documentation:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

Therefore, I've attempted to accomplish my goal by making postgres a member of role0 and then using postgres to give user1 default privileges to tables in schema0, but it doesn't work.

The script for (1) includes:

 CREATE ROLE role0 WITH
 LOGIN
 NOSUPERUSER
 NOINHERIT
 NOCREATEDB
 NOCREATEROLE
 NOREPLICATION;
 
 CREATE ROLE role1 WITH
 LOGIN
 NOSUPERUSER
 NOINHERIT
 NOCREATEDB
 NOCREATEROLE
 NOREPLICATION;
 
 CREATE SCHEMA schema0;
 ALTER SCHEMA schema0 OWNER TO role0;
 GRANT USAGE on SCHEMA schema0 to role1;
 GRANT role0 TO postgres;
 ALTER DEFAULT PRIVILEGES IN SCHEMA schema0 GRANT SELECT ON TABLES TO role1;

After I execute (2), creating tables including table0, when I log in as user1 and attempt to SELECT from table0, I get:

ERROR: permission denied for table table0

How can I accomplish what I'm trying to accomplish?

Answer 1

The question is which user you use to create the tables in schema0. Since you say that will be role0, you have to add FOR ROLE role0 to your ALTER DEFAULT PRIVILEGES statement.