How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group?

Question

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.

• eckes: Maybe it is enough to work with default permissions on a schema?
• Laurenz Albe: You cannot do it if you limit yourself to tools that do not allow the users to perform a simple statement like SET ROLE employees.

Answer 1

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;