Re: security_definer_search_path GUC

On Thu, Jun 3, 2021, at 20:42, Isaac Morland wrote:
> I also want to mention that I consider any suggestion to eliminate the search_path concept as a complete non-starter.
>
> It would be no different from proposing that the next version of a programming language eliminate (or stop using) the module system.

I think the suggestion of making it possible (but not a default) to eliminate search_path,
is very similar to C compiler flags that turn specific language features into hard errors, such as "-Werror=vla".

If you know your C code base doesn't contain vla, you can compile with that compiler flag.

If you know your SQL code base doesn't makes use of search_path, nor any installed EXTENSIONs,
I'm suggesting it would be nice to have a way to effectively ensure that stays the case.

I realise "eliminate" is not really necessary, it would suffice to just allow setting a a sane default per database, and make that value immutable, then all data structures and code using wouldn't need to change, one would then only need to change the code that can mutate search_path, to prevent that from happening.

> If I could make it happen easily, I would go in the other direction and allow schemas to be hierarchical (note: totally ignoring all sorts of very important choices which are more than just details about how this should work). I would like to be able to have an extension or subsystem exist in a single schema, with its objects broken up into schemas within the schema. Same reason as most languages have hierarchical module systems.

I note we already have a hierarchical extension system; EXTENSIONs can specify their dependencies (parents) via "requires" in the .control file. The entire hierarchical tree can then can be created/dropped using CASCADE.

I can possibly see some value in hierarchical schemas too, that is completely unrelated to my distaste for search_path.

I never felt I needed more than one namespace level, but I've only worked in companies with <1000 employees, so I can imagine it would be useful if the data needs for >100k employees needs to be organised in one and the same database. Is this how large companies organise their data? Or do they instead break up things into multiple databases?
Do we have some example of an extension that is complex enough where it would be good to organise it into multiple schema levels?

If reducing complexity by not using search_path, the complexity budget might afford hierarchical schemas, so I think the two ideas seem very compatible.

/Joel