From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
Cc: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Seems to be impossible to set a NULL search_path |
Date: | 2022-07-06 20:08:44 |
Message-ID: | CAKFQuwZF-Jj1vpT-0xdWXi4y2oXqu7G0MK26im9DKAnS=finxQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jul 6, 2022 at 11:50 AM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:
>
> It succeeded. And the \d metacommand showed me that I now have a table
> pg_temp_3.x. Using a different database, it ends up in "pg_temp_1. What's
> going on here? Is "pg_temp" a kind of generic nickname for ANY "pg_temp_N"?
>
> I hate to realize that I'm failing to understand a fundamental principle.
>
> It rather looks like the name-res to pg_catalog and (some) pg_temp is
> hard-coded and doesn't rely on the reigning search_path. Or, to put it
> another way, these two schemas are inevitably at the end of the search_path
> no matter what you set explicitly, and never mind that "show search_path"
> doesn't show them unless you also put them on the path (again) explicitly.
>
You either didn't read or failed or retain knowledge of the words in the
documentation that are the canonical reference for search_path and explain
exactly this. I suggest you (re-)read them.
https://www.postgresql.org/docs/current/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT
(under search_path)
And elsewhere I'm sure it is written that since temporary objects are
session-local it was decided that a useful implementation detail for that
would be for each session to have its own temporary schema, hence the
appended integer to distinguish them (referencing pg_temp works, the system
resolves the session specific schema name for you).
>
> I can't make sense of this wording from "Writing SECURITY
> DEFINER Functions Safely":
>
> « A secure arrangement can be obtained by forcing the temporary schema to
> be searched last. To do this, write pg_temp as the last entry
> in search_path. »
>
> If I do this:
>
> *set search_path = 'pg_catalog, pg_temp';*
> *show search_path;*
>
> Then I see what I set—in that order. But if I set the search_path to empty
> (and don't see pg_catalog or pg_temp with "show") PG behaves as if they're
> still there.
>
Those same docs also explain why search_path shows what it does (it is the
literally saved value) and to find out how the system actually resolved it
at runtime to come up with a final search_path you need to use a different
thing (via a function).
> Not only as my f() and p() above show. But even, say, "select count(*)
> from pg_class". Moreover, this is allowed too:
>
>
>
> *set search_path = 'pg_temp, pg_catalog, pg_temp';show search_path;*
> Now I see exactly what I set.
>
If you meant that to be a literal thing you've only identified one very
oddly named schema...otherwise yes I get your point.
> It seems strange that this is allowed. How does the implementation handle
> this when a to-be-resolved name exists nowhere? Does it just crank on,
> repeatedly searching where it already failed, right up to the bitter end?
>
Probably...or maybe it ignores the second reference and it is treated the
same as 'pg_temp, pg_catalog'
>
> Here's another test whose outcome surprises me and seems to be at odds
> with what you're saying and what the "Writing SECURITY DEFINER Functions
> Safely" section says:
>
>
>
>
>
>
> *select count(*) from pg_class; -- 399create temporary table pg_class(k
> int);select count(*) from pg_class; -- 0set search_path = 'pg_catalog,
> pg_temp';select count(*) from pg_class; -- STILL 0*
> Why does the final "select" show that the temp table's name has still
> captured the one in pg_catalog even though it's ahead in the path.
>
Remember that session scoped relation cache we went on about a little while
back...I think that by creating the object you got a cache invalidation but
simply changing the search_path does not cause a cache invalidation.
> The "Writing SECURITY DEFINER Functions Safely" section explicitly
> recommends that a subprogram includes a "set search_path" specification.
> But, as I read it, you're saying that this advice is wrong (at least when a
> function will be invoked in more than a bare "select" because it prevents
> inlining.
>
> How should I resolve these two conflicting pieces of advice?
>
There is no "conflict" - you basically get to choose safety or
performance. Though since performance isn't guaranteed nor always a need I
would say choose safety unless you've confirmed that you need performance.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Bryn Llewellyn | 2022-07-06 20:13:25 | Re: Seems to be impossible to set a NULL search_path |
Previous Message | Christophe Pettus | 2022-07-06 19:56:55 | Re: lifetime of the old CTID |