Обсуждение: Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION })

Hi there!

I followed the discussion about the schema resolution, and I really 
think there is need for an early bound (at function definition time) 
version of CURRENT_SCHEMA (the first member of search_path)

Avoiding hard-coding of schema names, (and avoiding polluting the actual 
users schema) is hard.

My current code generates some plpgsql functions, which need some helper 
functions to construct fcolumn lists, query fragments, etc.

These helpers should live in the same schema, IMHO


It is not impossible: I ended up with the following kludge to refer to 
functions in the same schema as the calling function.

It involves an extra layer of dynamic SQL, which self-destructs after 
use. It is not pretty, but it works.

Example of such a nested dynamic function is attached. (the helper 
functions are not included, but they live in the same "compilation unit")

Cheers,

Adriaan van Kessel

On 2023-09-26 14:44 +0200, dld wrote:
> I followed the discussion about the schema resolution, and I really think
> there is need for an early bound (at function definition time) version of
> CURRENT_SCHEMA (the first member of search_path)

You mean something like

    CREATE FUNCTION myfunc()
        RETURNS void
        SET search_path = CURRENT_SCHEMA
        ...

where CURRENT_SCHEMA would resolve to the current schema instead of
meaning literal "CURRENT_SCHEMA"?

> Avoiding hard-coding of schema names, (and avoiding polluting the actual
> users schema) is hard.
> 
> My current code generates some plpgsql functions, which need some helper
> functions to construct fcolumn lists, query fragments, etc.
> 
> These helpers should live in the same schema, IMHO

The helper functions can't be created in a common schema with a fixed
name?

> It is not impossible: I ended up with the following kludge to refer to
> functions in the same schema as the calling function.
> 
> It involves an extra layer of dynamic SQL, which self-destructs after use.
> It is not pretty, but it works.

You can do without the self-dropping disposable_factory() by using a DO
block instead.

> Example of such a nested dynamic function is attached. (the helper functions
> are not included, but they live in the same "compilation unit")

It's already possible to "inject" the current schema name by setting the
search path after creating the function.  Define the function as usual
but with unqualified names in the function body and then alter the
function to set the search path to the value of current_schema.

    BEGIN;

    CREATE FUNCTION create_asof(_fqn text, asof_date text)
        RETURNS text
        LANGUAGE plpgsql
        AS $$
    DECLARE
        basepair text[];
    BEGIN
        basepair := split_name(_fqn);
        -- etc.
    END $$;

    DO $$
    BEGIN
        EXECUTE format(
            'ALTER FUNCTION create_asof(text, text) SET search_path = %I',
            current_schema);
    END $$;

    COMMIT;

By the looks of it, your create_asof() creates functions with qualified
names provided in variable fnc_name.  So the executed CREATE FUNCTION
statement is not subject to the current search path.

-- 
Erik

ccing list

On 2023-09-27 00:12 +0200, dld write:
> On 26-09-2023 23:47, Erik Wienhold wrote:
> > On 2023-09-26 14:44 +0200, dld wrote:
> > > I followed the discussion about the schema resolution, and I really think
> > > there is need for an early bound (at function definition time) version of
> > > CURRENT_SCHEMA (the first member of search_path)
> > You mean something like
> > 
> >     CREATE FUNCTION myfunc()
> >         RETURNS void
> >         SET search_path = CURRENT_SCHEMA
> >         ...
> > 
> > where CURRENT_SCHEMA would resolve to the current schema instead of
> > meaning literal "CURRENT_SCHEMA"?
> > 
> > > Avoiding hard-coding of schema names, (and avoiding polluting the actual
> > > users schema) is hard.
> > > 
> > > My current code generates some plpgsql functions, which need some helper
> > > functions to construct fcolumn lists, query fragments, etc.
> > > 
> > > These helpers should live in the same schema, IMHO
> > The helper functions can't be created in a common schema with a fixed
> > name?
> > 
> > > It is not impossible: I ended up with the following kludge to refer to
> > > functions in the same schema as the calling function.
> > > 
> > > It involves an extra layer of dynamic SQL, which self-destructs after use.
> > > It is not pretty, but it works.
> > You can do without the self-dropping disposable_factory() by using a DO
> > block instead.
> > 
> > > Example of such a nested dynamic function is attached. (the helper functions
> > > are not included, but they live in the same "compilation unit")
> > It's already possible to "inject" the current schema name by setting the
> > search path after creating the function.  Define the function as usual
> > but with unqualified names in the function body and then alter the
> > function to set the search path to the value of current_schema.
> > 
> >     BEGIN;
> > 
> >     CREATE FUNCTION create_asof(_fqn text, asof_date text)
> >         RETURNS text
> >         LANGUAGE plpgsql
> >         AS $$
> >     DECLARE
> >         basepair text[];
> >     BEGIN
> >         basepair := split_name(_fqn);
> >         -- etc.
> >     END $$;
> > 
> >     DO $$
> >     BEGIN
> >         EXECUTE format(
> >             'ALTER FUNCTION create_asof(text, text) SET search_path = %I',
> >             current_schema);
> >     END $$;
> > 
> >     COMMIT;
> > 
> > By the looks of it, your create_asof() creates functions with qualified
> > names provided in variable fnc_name.  So the executed CREATE FUNCTION
> > statement is not subject to the current search path.
> > 
> No: you misunderstood, IMO
> 
> It is about the worker functions being called. (which need to be in the same
> schema) The worker funcvtioens just need to be found.
> 
> CURRENT_SCHEMA just changes the schema to whatever schema the caller appears
> to live in. (at execution time!!11!1)

Ah, I see.  But still wondering if this is necessary.

> I do not want to interfere, I do not want to pollute their schema with my
> nonsense-functions..

Again, why can't create_asof() and the helper/worker functions be in a
hard coded schema?  Are those functions defined once in the database or
does each user get their own version, perhaps in a multitenancy design?
And who is calling create_asof()?

I'm currently working on a database that I also designed in large parts
where trigger functions (SECURITY DEFINER) create views that give users
a restricted view of the data for ease of use.  Quite similar to that
create_asof() function but with hard coded schema names.  So I'm also
interested to learn what designs other people came up with.

>  I just want to keep them in my own secret schema. [remember POSTGIS?]

Secretive for having security through obscurity?  But you can't really
hide schema information when users still need access to system catalogs.
But you can decide to revoke EXECUTE privilege from those functions and
give users a few SECURITY DEFINER functions as entry points to the
"private" parts of the schema.

Anything special about PostGIS in this regard?  In my databases PostGIS
either lives in public or a dedicated schema.  But there's nothing
secretive about it.

-- 
Erik

On 27-09-2023 04:03, Erik Wienhold wrote:
> ccing list
>
> On 2023-09-27 00:12 +0200, dld write:
>> On 26-09-2023 23:47, Erik Wienhold wrote:
>>> On 2023-09-26 14:44 +0200, dld wrote:
>>>> I followed the discussion about the schema resolution, and I really think
>>>> there is need for an early bound (at function definition time) version of
>>>> CURRENT_SCHEMA (the first member of search_path)
>>> The helper functions can't be created in a common schema with a fixed
>>> name?


Yes, they could. But I try to avoid hard coding the name all over the place.

>
> Ah, I see.  But still wondering if this is necessary.
>
>> I do not want to interfere, I do not want to pollute their schema with my
>> nonsense-functions..
> Again, why can't create_asof() and the helper/worker functions be in a
> hard coded schema?  Are those functions defined once in the database or
> does each user get their own version, perhaps in a multitenancy design?
> And who is calling create_asof()?

Yes they could.

my_separate_schema.create_asof() is intended to be called by the "end 
user" of the "package"

 From whatever current_schema or search_path [s]he happens to be in.


>
> I'm currently working on a database that I also designed in large parts
> where trigger functions (SECURITY DEFINER) create views that give users
> a restricted view of the data for ease of use.  Quite similar to that
> create_asof() function but with hard coded schema names.  So I'm also
> interested to learn what designs other people came up with.
>

Me too.

And: I would really like another version of current_schema() that is 
resolved/bound at the moment the function is defined.

>>   I just want to keep them in my own secret schema. [remember POSTGIS?]
> Secretive for having security through obscurity?  But you can't really
> hide schema information when users still need access to system catalogs.
> But you can decide to revoke EXECUTE privilege from those functions and
> give users a few SECURITY DEFINER functions as entry points to the
> "private" parts of the schema.

It is not about security. security is orthogonal to this.

And the SECURITY DEFINER is already present.

The factory function will generate a function in the end-users schema

, but only if this end-user has sufficient rights.

> Anything special about PostGIS in this regard?  In my databases PostGIS
> either lives in public or a dedicated schema.  But there's nothing
> secretive about it.
IIRC postgis needs to be in the search_path, or it will suffer the same 
restrictions.


HTH,

AvK