Re: Privilege required for IF EXISTS event if the object already exists

On Wed, Dec 15, 2021 at 5:35 AM Shay Rojansky <roji(at)roji(dot)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.

David J.