Re: security_definer_search_path GUC

st 2. 6. 2021 v 8:45 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> On Tue, Jun 1, 2021, at 18:05, Pavel Stehule wrote:
>
> I learned programming on Orafce, and I didn't expect any success, so I
> designed it quickly, and the placing of old Orafce's functions to schemas
> is messy.
>
> I am sure, if I started again, I would never use pg_catalog or public
> schema. I think if somebody uses schema, then it is good to use schema for
> all without exceptions - but it expects usage of search_path. I am not sure
> if using public schema or using search_path are two sides of one thing.
>
>
> I think you're right they both try to provide solutions to the same
> problem, i.e. when wanting to avoid having to fully-qualify.
>
> However, they are very different, and while I think the 'public' schema is
> a great idea, I think 'search_path' has some serious problems. I'll explain
> why:
>
> 'search_path' is a bit like a global variable in C, that can change the
> behaviour of the SQL commands executed.
> It makes unqualified SQL code context-sensitive; you don't know by looking
> at a piece of code what objects are referred to, you also need to figure
> out what the active search_path is at this place in the code.
>

sometimes this is wanted feature - some sharding is based on this

set search_path = 'custormerx'
...

> 'public' schema if used (without ever changing the default 'search_path'),
> allows creating unqualified database objects, which I think can be useful
> in at least three situations:
>
> 1) when the application is a monolith inside a company, when there is only
> one version of the database, i.e. not having to worry about name collision
> with other objects in some other version, since the application is hidden
> in the company and the schema design is not exposed to the public
>
> 2) when installing a extension that uses schemas, when wanting the
> convenience of unqualified access to some functions frequently used,
> instead of adding its schema to the search_path for convenience, one can
> instead add wrapper-functions in the 'public' schema. This way, all
> internal functions in the extension, that are not meant to be executed by
> users, are still hidden in its schema and won't bother anyone (i.e. can't
> cause unexpected conflicts). Of course, access can also be controlled via
> REVOKE EXECUTE ... FROM PUBLIC for such internal functions, which is
> probably a good idea as well.
> In a similar way, specific tables in the extension's schema can be made
> unqualified as well by adding simple views, installed in the public schema,
> if insisting on unqualified convenience.
>
> In conclusion:
> The main difference is 'public' makes it possible to make *specific*
> objects unqualified,
> while 'search_path' makes *all* objects in such schema(s) unqualified.
>

These arguments are valid, but I think so it is not all. If you remove
search_path, then the "public" schema will be overused. I think we should
ask - who can change the search path and how. Now, there are not any
limits. I can imagine the situation when search_path can be changed by only
some dedicated role - it can be implemented in a security definer function.
Or another solution, we can fix the search path to one value, or only a few
possibilities.

Maybe for your purpose is just enough to introduce syntax for defining all
possibilities of search path:

search_path = "public" # now, just default
search_path = ["public"] # future - define vector of possible values of
search path - in this case, only "public" is allowed - and if you want to
change it, you should be database owner

or there can be hook for changing search_path, and it can be implemented
dynamically in extension

Pavel

>
> /Joel
>