2

I have a set of apps that produce data. Each app has its own credentials to access the DB, and they all inherit from a common role (producer).

I'd like to set default privileges to grant access to objects created by a producer, but it does not work because objects are owned by each particular app's login, not by producer.

So: is there a way to have all tables created by these apps to be owned by producer not by their particular login ? Or, is there a way to write these default privileges differently ?

EDIT: and I'd like to avoid changing all apps to make them do a SET ROLE...

EDIT2: and... I'm not the only one: Automatically invoke `SET ROLE` when connecting to PostgreSQL, Inherit default privileges: every table created by *any member of* role A is readable by role B

1

No postgresql does not inherit permissions in this fashion, Pretty much anything using the Create command sets the owner to the users that created it.

Use the GRANT command

Or after creating the tables set the Owner with Alter command

Create table Mytable (list of columns);
Alter table Mytable  owner to producer; 
| improve this answer | |
  • Found this, to set ownership automatically blog.hagander.net/setting-owner-at-create-table-237 – mathieu Jan 2 at 16:49
  • 1
    @mathieu I would not use DDL trigger, as it significantly alters PostgreSQL behavior, and means all the create tables get assigned this owner. Also I do not think this has taken into account Schema changes with a user having access to schema yet the role does not have permissions. – zsheep Jan 2 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.