Hi. I'm on v16+. The DB owner ROLE has CREATEROLE, and obviously CREATE on the DB. So it can both CREATE SCHEMA, and CREATE ROLE. Yet it cannot CREATE SCHEMA AUTHORIZATION, and gets an
ERROR: must be able to SET ROLE "..." Yet because this is v16+, thus the DB owner has ADMIN OPTION on the ROLEs is created, so it can grant itself those ROLEs, to be able to CREATE SCHEMA AUTHORIZATION. acme=> create schema "PRJ1" authorization "OWNER1"; ERROR: must be able to SET ROLE "OWNER1" acme=> grant "OWNER1" to current_role; GRANT ROLE acme=> create schema "PRJ1" authorization "OWNER1"; CREATE SCHEMA So basically, admin_option trumps set_option in this case. So shouldn't admin_option be enough to create schemas on behalf of roles one created? Is this one of those things to got overlooked when v16 "tightened" CREATEROLE? It's a PITA to have to be a MEMBER of the role one wants to create schemas on behalf of. Could the rules of CREATE SCHEMA AUTHORIZATION be relaxed a little? On a related subject, ALTER SCHEMA OWNER TO mentions the new owner must have CREATE on the database. Why? Seems like the owner set via CREATE SCHEMA AUTHORIZATION does not have that requirement, so why would it be any different from ALTER SCHEMA OWNER TO? Isn't it the whole point of allowing some roles who lack CREATE on the DB to own schemas, but delegating the creation (via SECURITY DEFINER procs for example) to ROLEs you can create those schemas? Thanks for insights on this. --DD
