On Wed, Dec 15, 2021 at 5:35 AM Shay Rojansky <roji@roji.org> wrote:
Hi all,
I've received numerous complaints about CREATE SCHEMA IF NOT EXISTS failing when the user lacks CREATE privileges on the database - even if the schema already exists. A typical scenario would be a multi-tenant schema-per-tenant setup, where the schema and tenant user are created beforehand, but then some database layer or ORM wants to ensure that the schema is there so the above is issued.
Would it be reasonable to have the above no error if the schema already exists?
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.
That would make it similar to the following (which I'm switching to in the Entity Framework Core ORM):
DO $$ BEGIN IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'foo') THEN CREATE SCHEMA "foo"; END IF; END $$;
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.