Privilege required for IF EXISTS event if the object already exists

Shay Rojansky <roji@roji.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

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.

David J.

On 12/15/21 11:10, David G. Johnston wrote:

IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'foo') THEN

Orthogonally to any other comments,

IF pg_catalog.to_regnamespace('foo') IS NULL THEN

might be tidier, if you don't need to support PG < 9.5.

Regards,
-Chap

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).

On Wednesday, December 15, 2021, Shay Rojansky <roji@roji.org> wrote:

. 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.

If the user hasn’t specified they want the schema created it’s arguable
that executing create schema anyway is reasonable. The orm user should
know whether they are expected/able to create the schema as part of their
responsibilities and instruct the orm accordingly and expect it to only
create what it has been explicitly directed to create.

David J.

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.

If the user hasn’t specified they want the schema created it’s arguable

that executing create schema anyway is reasonable. The orm user should
know whether they are expected/able to create the schema as part of their
responsibilities and instruct the orm accordingly and expect it to only
create what it has been explicitly directed to create.

I think the point being missed here, is that the user isn't interacting
directly with PostgreSQL - they're doing so via an ORM which isn't
necessarily aware of everything. Yes, a switch could be added to the ORM
where the user instructs it to not ensure that the schema exists, but
that's placing unnecessary burden on the user - having the "ensure"
operation silently no-op (instead of throwing) if the schema already exists
just makes everything smoother.

Put another way, let's say I introduce a user-facing flag in my ORM to opt
out of CREATE SCHEMA IF NOT EXISTS. If the user forget to pre-create the
schema, they would still get an error when trying to create the tables
(since the schema doesn't exist). So failure to properly set up would
generate an error in any case, either when trying to create the schema (if
no flag is added), or when trying to create the table (if the flag is
added). This makes the flag pretty useless and an unnecesary extra burden
on the user, when the database could simply be ignoring CREATE SCHEMA IF
NOT EXISTS for the case where the schema already exists.

Is there any specific reason you think this shouldn't be done?

On Thu, Dec 16, 2021 at 3:38 AM Shay Rojansky <roji@roji.org> wrote:

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.

If the user hasn’t specified they want the schema created it’s arguable

that executing create schema anyway is reasonable. The orm user should
know whether they are expected/able to create the schema as part of their
responsibilities and instruct the orm accordingly and expect it to only
create what it has been explicitly directed to create.

I think the point being missed here, is that the user isn't interacting
directly with PostgreSQL - they're doing so via an ORM which isn't
necessarily aware of everything. Yes, a switch could be added to the ORM
where the user instructs it to not ensure that the schema exists, but
that's placing unnecessary burden on the user - having the "ensure"
operation silently no-op (instead of throwing) if the schema already exists
just makes everything smoother.

I get that point, and even have sympathy for it. But I'm also fond of the
position that "ensuring a schema exists" is not something the ORM should be
doing. But, if you want to do it anyway you can, with a minimal amount of
pl/pgsql code.

Is there any specific reason you think this shouldn't be done?

As I said before, your position seems reasonable. I've also got a couple
of reasonable complaints about IF EXISTS out there. But there is little
interest in changing the status quo with regards to the promises that IF
EXISTS makes. And even with my less constrained views I find that doing
anything but returning an error to a user that issues CREATE SCHEMA on a
database for which they lack CREATE privileges is of limited benefit.
Would I support a well-written patch that made this the new rule?
Probably. Would I write one or spend time trying to convince others to
write one? No.

David J.

As I said before, your position seems reasonable. I've also got a couple

of reasonable complaints about IF EXISTS out there. But there is little
interest in changing the status quo with regards to the promises that IF
EXISTS makes. And even with my less constrained views I find that doing
anything but returning an error to a user that issues CREATE SCHEMA on a
database for which they lack CREATE privileges is of limited benefit.
Would I support a well-written patch that made this the new rule?
Probably. Would I write one or spend time trying to convince others to
write one? No.

Fair enough, thanks.