0

I need to be able to assign a role to users to allow them to do anything they want to tables in a schema. I need to do this without assigning superuser priv in Postgres.

I already figured out how to assign granular privileges. I also figured out that I can assign the user the same role that owns the table, and they can drop the said table.

However the user in question is not able to alter tables they don't own. I think is because when I create a new table with a different user, that new table is automatically owned by user that created, not by the admin role assigned to the user in question.

Is there a way around this? Tables could be created by anybody. Is there anyway to assign a priv / special role that is not superuser to allow the user in question to alter and drop tables they don't own?

1 Answer 1

1

No, there is no way around that: only a member of the owning role or a superuser can ALTER or DROP an object.

Perhaps you can make the user that is to drop the objects a member of all other users?

2
  • Thanks, we ended up, just assigning the schema and tables a specific admin user. Then the systems that needed said granular control of altering tables will log in as that admin user.
    – Dave
    Commented Jun 28 at 16:20
  • That sounds like a way forward. Note that an even cleaner solution would be to make the users a member of that admin user. Then they don't need to log in as that user, they can do SET ROLE admin and temporarily become that user. Commented Jun 29 at 6:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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