Can't create read-write Postgresql user

Question

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?

score 1 · Answer 1 · answered Mar 20 '19 at 15:04

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.

answered Mar 20 '19 at 15:04

András Váczi

31,278
13
101
147

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? – Kris Harper 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...? – Kris Harper 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. – Kris Harper 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
Although probably better would be to stick with your original defaults, and have read_write_create_user do a set role read_write_create; before creating things. – jjanes Mar 20 '19 at 23:52
@jjanes Okay, I think I got it. I didn't realize that the ALTER DEFAULT statements had to be executed by read_write_create_user. Do you have any links to some best practices on this? I am new to Postgres and to be honest I find it very confusing that permissions for tables depend on which user created the table (and which user created the permission, even). – Kris Harper Mar 21 '19 at 02:08

Can't create read-write Postgresql user

1 Answers1