I'm trying to create two users in a Postgres database—one with read-write access to all tables in two schemas, and one with read-write-create (i.e., able to make DDL changes) to the same schemas.
I currently have these statements.
CREATE SCHEMA
schema_a;
CREATE SCHEMA
schema_b;
CREATE ROLE read;
CREATE ROLE read_write;
CREATE ROLE read_write_create;
GRANT USAGE ON SCHEMA schema_a, schema_b TO read;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_a, schema_b TO read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_a, schema_b TO read_write;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA schema_a, schema_b TO read_write;
GRANT ALL ON SCHEMA schema_a, schema_b TO read_write_create;
GRANT read to read_write;
GRANT read_write to read_write_create;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT SELECT ON TABLES TO read;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT SELECT ON SEQUENCES TO read;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT INSERT, UPDATE, DELETE ON TABLES TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT SELECT, UPDATE ON SEQUENCES TO read_write;
CREATE USER
read_write_user
WITH PASSWORD
'a_password';
GRANT read_write TO read_write_user;
CREATE USER
read_write_create_user
WITH PASSWORD
'another_password';
GRANT read_write_create TO read_write_create_user;
After running these, read_write_create_user
can create tables, read from, and write to all tables. But read_write_user
can't read or write to any tables created by read_write_create_user
.
What am I doing wrong?