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
