david.g.johnston@xxxxxxxxx wrote: unless you are going to write: operator(pg_catalog.=) in your function the advice to always use schema qualifications is not going to be taken seriously... the correct search_path to set isn't "empty" but "pg_catalog", "pg_temp". While this does violate "DRY" principles it is the solution you are looking for. The advice always to use fully qualified names is orthogonal to the advice to set an explicit search_path in a subprogram's definition. Am I right that your point is about what search path to set and has nothing to do with the possible use of qualified names? I do see that I can achieve my goal by setting the search_path to pg_catalog, pg_temp in my functions rather than to empty. So I can adopt that practice. But it's at odds with an example in this section: Writing SECURITY DEFINER Functions Safely https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2 The example sets a search path the does *not* mention pg_catalog. I hadn't come across this locution: operator(pg_catalog.=) But I found an account here: 4.2.5. Operator Invocations https://www.postgresql.org/docs/current/sql-expressions.html#SQL-EXPRESSIONS-OPERATOR-CALLS To be sure, I tried this: select 17 operator(pg_catalog.=) 17; It ran without error and produced TRUE. I believe that you're saying that because (as I presume) the "normal" definition of the = operator is done by code in the pg_catalog schema, pg_catalog must be on the path for ordinary equality tests to work. (I also found random folks saying this on StackExchange.) So I tried this: create function f() returns boolean set search_path = '' security definer language plpgsql as $body$ begin return (2 + 5) = 7; end; $body$; select f()::text; It succeeded and said true. I don't know how to explain this outcome. For good measure, I tried this too: create procedure p() set search_path = '' security definer language plpgsql as $body$ begin create temporary table x(k int); end; $body$; call p(); 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. 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. 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. 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? 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; -- 399 create temporary table pg_class(k int); select count(*) from pg_class; -- 0 set 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. Might I trouble you to explain more carefully—or to give me a doc reference that will allow me to see why the equality function in my example f() is found when the path for the function is empty? And why my example procedure p() manages to create a temporary table while pg_temp is not on the search_path? The main problem is that by doing search_path manipulation on the function you prevent inlining it into the main query. 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? |
