User A owns all of our tables and has access to all of our schemas

User B needs full access including Drop and Alter permissions to some of those tables and schemas but not all of them.

As is well documented, Drops and Alters are not possible because User B does not own the tables.

UserB => DROP TABLE userATable;
ERROR:  must be owner of relation test

The recommendation in this case seems to be to make Role B a member of Role A.

grant UserB to UserA;

But we cannot do this because then UserB will have access to schemas for which it should not. What are recommended strategies to handle this?


make the tables be owned by role A and grant role A to role B (or make them owned by role C and grant C to both A and B)

  • yes this is our current backup plan, but we were hoping there was some other less invasive way to handle this. it does not seem that there is though, so I will accept this answer – Howard_Roark Feb 14 '19 at 15:23
  • You could play around with functions (or procedures) having security definer and owned by A but that seems easy to mess up. – Jasen Feb 16 '19 at 6:25

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.