Postgresql: create role, schema and change ownership but not as superuser

I need to create various schema in a database and give ownership of these to specific users. I want to do this in a script, and I rather not be the postgres user or a superuser.
My user pat has createdb and createrole privileges.

pat=> create role toto with login password 'pass';  
pat=> create schema toto;
pat=> alter schema toto owner to toto;
ERROR:  must be member of role "toto"

Is there a way to do this without being postgres or superuser ? or should I give pat superuser privileges while running the scripts and drop it after? What is the best procedure?

Per documentation,


You must own the schema to use ALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must also be a direct or indirect member of the new owning role, [...]

So in your case, you either need to be superuser or be a member of the role you are transferring the ownership to.

Ultimately, while I applaud your efforts, you're going to have a hard time managing this without some superuser use. So I'd focus on locking down the superuser use, maybe wrap the steps in a few security-definer functions.

StackExchange Database Administrators Q#115741, answer score: 2