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

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