> I would say it is reasonable in theory. But I cannot think of an actual scenario that would benefit from such a change. Your stated use case is rejected since you explicitly do not want tenants to be able to create schemas - so the simple act of issuing "CREATE SCHEMA" is disallowed.
> [...]
> Because tenants are not allowed to CREATE SCHEMA you should replace "CREATE SCHEMA" in the body of that DO block with "RAISE ERROR 'Schema foo required but not present!';" Or, just tell them to create objects in the presumed present schema and let them see the "schema not found" error that would occur in rare case the schema didn't exist.
The point here is when layers/ORMs are used, and are not necessarily aware of the multi-tenant scenario. In my concrete real-world complaints here, users instruct the ORM to generate the database schema for them. Now, before creating tables, the ORM generates CREATE SCHEMA IF NOT EXISTS, to ensure that the schema exists before CREATE TABLE; that's reasonable general-purpose behavior (again, it does not know about multi-tenancy). It's the user's responsibility to have already created the schema and assigned rights to the right PG user, at which point everything could work transparently (schema creation is skipped because it already exists, CREATE TABLE succeeds).