Thread: Privilege required for IF EXISTS event if the object already exists
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? 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 $$;
BEGIN
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'foo') THEN
CREATE SCHEMA "foo";
END IF;
END $$;
The same could apply to other CREATE ... IF NOT EXISTS variations.
Shay
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
Re: Privilege required for IF EXISTS event if the object already exists
From
"David G. Johnston"
Date:
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).
Re: Privilege required for IF EXISTS event if the object already exists
From
"David G. Johnston"
Date:
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?
Re: Privilege required for IF EXISTS event if the object already exists
From
"David G. Johnston"
Date:
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.