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

access-controlpostgresqlpostgresql-11users

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;

Related Solutions

Postgresql – Postgres Large Objects & Multiple Users

One option is to use SET ROLE command after you open a connection from your application to the databaserole:

SET ROLE databaserole;

In such way any object (including large objects) created within this session will be owned by databaserole instead of token-XXX.

Another option is to use LOCAL and make it work only within the transaction used to create the large object:

BEGIN;
SET LOCAL ROLE databaserole;
-- create and insert the large object
COMMIT;

Alternatively, you could just set the role directly as the user setting:

ALTER ROLE "{{name}}" SET role TO 'databaserole';

I kind of dislike that option as it can become a bit obscure to others how it is working, and if you manage other roles to your user it won't inherit them (although that doesn't seem like a problem to you, as with dynamically created user you should have a single role that it directs inherit from).

PostgreSQL – Grant privileges as superuser

ALTER DEFAULT PRIVILEGES without a FOR ROLE clause alters the default privileges for objects created by only the current role (the role executing the ALTER DEFAULT PRIVILEGES). So in the first case, you altered the superuser's default privileges, which has no effect on testuser's default privileges.

Best Answer

Related Solutions

Postgresql – Postgres Large Objects & Multiple Users

PostgreSQL – Grant privileges as superuser

Related Question