PostgreSQL grant access to table

Question

I have user1 and user2 users in PostgreSQL. I have created a database and a scheme called data in PostgreSQL.

In the database, I have executed the following statements:

ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT ALL ON TABLES TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT ALL ON TABLES TO user2;

I logged in as user1 and created tables in data schema, then I logged in as user2, and couldn't select * from table1 using user2 credentials.

Is there any way I can create table (future) and give access to user1 & user2, not just the owner of the table?

I am currently using PostgreSQL 13.

Which user did you use to log in when you ran those ALTER statements? — user330315
– user330315, Commented Jul 13, 2021 at 10:56
@a_horse_with_no_name I login as postgres user to execute the ALTER statements. — xxestter
– xxestter, Commented Jul 15, 2021 at 2:34

Laurenz Albe · Accepted Answer · 2021-07-13 11:03:07Z

2

The way you wrote the ALTER DEFAULT PRIVILEGES statement, it applies to tables created by the user that ran the statement.

To change the default privileges for tables created by user1, run

ALTER DEFAULT PRIVILEGES FOR ROLE user1 IN SCHEMA data GRANT ALL ON TABLES TO user2;

answered Jul 13, 2021 at 11:03

Laurenz Albe

256k22 gold badges310 silver badges386 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thanks Laurenz, for the info. But if I have a list of users/roles, can I ask on how can I do it?

If all those users create tables, you need a lot of ALTER DEFAULT PRIVILEGES statements. There is no way around that. Try to automatize.

Trantkat Troy · Accepted Answer · 2021-07-13 10:40:29Z

-1

you may need to specify schema before select * from **data.**table1 using user2 credentials.

answered Jul 13, 2021 at 10:40

Trantkat Troy

1092 silver badges5 bronze badges