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

Shay Rojansky <roji(at)roji(dot)org> writes:
> 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?

Ummm ... why? What's the point of issuing such a command from a role
that lacks the privileges to actually do the creation? It seems to
me that you're asking us to design around very-badly-written apps.

> The same could apply to other CREATE ... IF NOT EXISTS variations.

Yeah, it would only make sense if we did it across the board.
For all of them, though, this seems like it'd just move the needle
even further in terms of not having certainty about the properties
of the object. I'll spare you my customary rant about that, and
just note that not knowing who owns a schema you're using is a
large security hazard.

regards, tom lane