Re: [17] Special search_path names "!pg_temp" and "!pg_catalog"

Hi

pá 18. 8. 2023 v 23:44 odesílatel Jeff Davis <pgsql(at)j-davis(dot)com> napsal:

> The attached patch adds some special names to prevent pg_temp and/or
> pg_catalog from being included implicitly.
>
> This is a useful safety feature for functions that don't have any need
> to search pg_temp.
>
> The current (v16) recommendation is to include pg_temp last, which does
> add to the safety, but it's confusing to *include* a namespace when
> your intention is actually to *exclude* it, and it's also not
> completely excluding pg_temp.
>
> Although the syntax in the attached patch is not much friendlier, at
> least it's clear that the intent is to exclude pg_temp. Furthermore, it
> will be friendlier if we adopt the SEARCH SYSTEM syntax proposed in
> another thread[1].
>
> Additionally, this patch adds a WARNING when creating a schema that
> uses one of these special names. Previously, there was no warning when
> creating a schema with the name "$user", which could cause confusion.
>
> [1]
>
> https://www.postgresql.org/message-id/flat/2710f56add351a1ed553efb677408e51b060e67c(dot)camel(at)j-davis(dot)com

cannot be better special syntax

CREATE OR REPLACE FUNCTION xxx()
RETURNS yyy AS $$ ... $$$
SET SEARCH_PATH DISABLE

with possible next modification

SET SEARCH_PATH CATALOG .. only for pg_catalog
SET SEARCH_PATH MINIMAL .. pg_catalog, pg_temp

I question if we should block search path settings when this setting is
used. Although I set search_path, the search_path can be overwritten in
function of inside some nesting calls

(2023-08-19 07:15:21) postgres=# create or replace function fx()
returns text as $$
begin
perform set_config('search_path', 'public', false);
return current_setting('search_path');
end;
$$ language plpgsql set search_path = 'pg_catalog';
CREATE FUNCTION
(2023-08-19 07:15:27) postgres=# select fx();
┌────────┐
│ fx │
╞════════╡
│ public │
└────────┘
(1 row)

>
>
>
> --
> Jeff Davis
> PostgreSQL Contributor Team - AWS
>
>
>