Change owner of Postgres table automatically?

Question

I have a database shared by many users, all the users are in a group "example" and the vast majority of objects in the database are owned by "example". Very occasionally a user will create a new table - that table gets assigned to the user who created it and so the other users are unable to alter the new table.

Is there a way to have the ownership of a table automatically set to the group "example" and not the user who created the table or a way to set up a trigger that happens after a CREATE TABALE or a way to set up group/permissions such that all users will be considered owners of objects regardless of who actually created them?

Try [Event Triggers](https://www.postgresql.org/docs/current/static/event-triggers.html) — Abelisto, Nov 02 '16 at 17:44

score 0 · Answer 1 · answered Nov 02 '16 at 14:39

0

You could change the default privileges this way:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;

or to give write access:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO PUBLIC;

https://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html

answered Nov 02 '16 at 14:39

Bruno

623
4
6

1

What I really need is `ALTER` privileges - they're only available to the owner of the table an when one user creates a new one they are assigned as the owner rather than a common group. – J. Doe Nov 02 '16 at 14:49

score 0 · Answer 2 · answered Dec 14 '16 at 09:32

You probably want to use an EVENT TRIGGER

This is doable in all versions of Pg from 9.3 forward but depending on your version might require different approaches since the structures for event triggers have improved significantly.

In earlier versions you could look through the table catalogs for items owned by the current user. In newer versions you can use pg_event_trigger_ddl_commands to get the information you need. you want to run the command at ddl end.

Change owner of Postgres table automatically?

2 Answers2