Re: search_path vs extensions

On Wed, May 27, 2009 at 10:02 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Robert,
>
>> However, since we're on that tangent, I'm not completely convinced
>> that additional lists of search paths that get prepended or appended
>> to the main search path are the right way to go.  It seems like that's
>> just chopping up the problem into smaller bits without really fixing
>> anything.  I wonder if the right solution might be to associate with
>> each schema a list of other schemas to be searched if the object isn't
>> found in that schema.  This means that the contents of search_path
>> would really become the roots of the trees of schemas to be searched.
>
> See, that strikes me a completely unmanageable and likely to give rise to
> application security holes.  But you're a smart guy ... so, *why* would that
> be a better idea than some superuser settings?  What am I not thinking of?

Hey, you're a smart guy too, plus you've been around here longer than
I have and have more experience. If my idea sounds like it sucks,
there's a real possibility that it does.

With that caveat, my thought process was approximately as follows.
The contents of a particular schema are more or less analagous to an
application. In most programming languages, an application informs
the system of the libraries that it needs and the system goes off and
loads the symbols in those libraries into the application's namespace.
Using search path basically requires the user to tell the application
where to find those symbols, which ISTM is exactly backwards.

In other words, suppose we have an application in schema S that is
designed to use make use of extensions installed in scheams E1, E4,
and E7. With the pre- and post- search path approach, it's not
sufficient for the user to set his or her search_path to S and then
use the application. Instead, the user has to know that the
search_path must be set to S, E1, E4, E7, or else E1, E4, and E7 have
to be present in the system default post-search-path. And what
happens if there is another application in schema S2 that uses E1, E2,
and E4, where E2 is an older version of E7 with an overlapping set of
names? There's no possible way of configuring your search paths that
will make this work, short of explicitly setting the full search path
to exactly the right thing for each application when using that
application.

Also, it seems to me that we could create a system schema called
something like pg_extension and make it empty. Every extension could
install in its own schema and then tell pg_extension to inherit it
that schema. Then if you want to just get all the extensions, you can
just set your search path to include pg_extension, and as new
extensions are added or old ones are removed, you'll still have all
the extensions without changing anything. I don't see how this could
be made to work with the pre- and post- search_path idea; you'll be
manually fiddling those settings in postgresql.conf, or on a per-user
basis, or wherever you set them up, every time you add or remove an
extension.

I Just Work Here, You Want To Talk To The Boss.

...Robert