st 2. 6. 2021 v 8:45 odesÃlatel Joel Jacobson <j...@compiler.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 >
