Re: Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION }) - Mailing list pgsql-general

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