5

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?

1

This is because the tables in either schema are created by a different role (I assume read_write_create_user) than the one the default privileges were defined for (which is you, or the role that run the above set of statements).

You'll find a bit more explanation in my older answer.

7
  • Sorry, I'm still a little confused. I updated my statements to read ALTER DEFAULT PRIVILEGES FOR ROLE read_write_create IN SCHEMA schema_a, schema_b..., but I still have the same issue. Is there something else I need to change? Mar 20 '19 at 19:06
  • It has to be executed for "read_write_create_user" if that is who is creating the tables. It is not an inherited property. Which means it has to be executed after that role is created.
    – jjanes
    Mar 20 '19 at 19:18
  • @jjanes Are you saying I should have ALTER DEFAULT PRIVILEGES FOR USER read_write_create_user IN SCHEMA schema_a, schema_b...? Mar 20 '19 at 20:29
  • @jjanes I also tried updating to ALTER DEFAULT PRIVILEGES FOR USER read_write_create_user IN SCHEMA schema_a, schema_b...TO read_write_user, read_write_create_user but that didn't work either. Mar 20 '19 at 22:27
  • @KrisHarper works for me. Did you look at tables created after you did the new ALTER DEFAULT PRIVILEGES..?
    – jjanes
    Mar 20 '19 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.