Postgresql – How to setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group


I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees".

The goal: To setup things in a way that allows all of the users to work with all of the objects in the database.

The problem: I can't expect the users to set the owner to "employees" when they create a new object, because they use various limited interfaces to work with the database. When they create a schema or a table, it gets created with the user as its owner, which means that the other users don't have rights on that schema/table.

I'm using PostgreSQL 11.2.

Best Answer

Assuming you already have

GRANT employees TO john;

Just do

ALTER USER john SET ROLE TO employees;

Now when john connects he will automatically have role employees and when he creates an object it will be owned by employees;