PostgreSQL: How to GRANT permission to GRANT permission in specific SCHEMA

Question

I have a sample database containing two schemas and I have two roles in the database.

I need to grant rights in a way that

• 1. role can grant all permission within schema #1 (nowhere else)
• 2. role can grant all permission within schema #2 (nowhere else)

(essentially "schema-specific admins")

Would someone know a possible approach to this?

Answer 1

I can think of two ways:

• the “administrator role” for each schema owns the objects in that schema – then the requirement is automatically fulfilled

• all objects are owned by the same role, and that role uses GRANT ... WITH GRANT OPTION on all tables in each schema to the respective administrative role

I'd prefer the first option, because it is simpler.