MIME-Version: 1.0
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 14 Apr 2025 17:21:40 +0200
Message-ID: <CAFCRh-_z9V1hHgQ5kvZOKhQKA16YGR-8oPSyikTqbO=ZysCN1g@mail.gmail.com>
Subject: CREATE SCHEMA AUTHORIZATION and ALTER SCHEMA OWNER TO
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-_z9V1hHgQ5kvZOKhQKA16YGR-8oPSyikTqbO%3DZysCN1g%40mail.gmail.com>
Precedence: bulk

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