Re: search_path improvements WAS: search_path vs extensions

On Fri, May 29, 2009 at 10:52 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> Sometimes one needs to use schemas just for namespacing (they are called
> "namespaces" after all), and not for security or visibility.

What's the point of "namespaces" if not to implement visibility? The
interesting thing to do would be to hide all the internal foo
functions in a foo.* schema and only put the external api in public.
That way you can't accidentally call an internal foo function or have
a name conflict between two internal functions. The external api could
even just be a bunch of thin wrappers around the implementation
functions in foo.* (what Oracle calls public synonyms).

If you just put them all in search path you haven't bought anything,
all your functions are in the same namespace and one module can
override another's objects. Actually it's worse than just putting them
all in one schema since you won't even be warned when a conflict
happens. It will just silently start doing something different.

> c) the ability as superuser to have my own "special schema" which are always
> in the search path, as pg_catalog and $user_temp are.*

> * if you're not sure why someone would want this, consider
> information_schema. If your application depends on I_S to work, how do you
> make sure it's always in every user's search_path?

Uhm, wouldn't you just refer to information_schema.foo? What if some
other part of your application depends on information_schema *not*
being in your path? Using global state for this seems destined to
leave you with something broken that can't be fixed without breaking
something else.

> d) the ability as superuser to "lock" specific role so that they can't
> change their search path**

> ** think about the number of security exploits around search_path we could
> protect against if we had this.

Actually I'm thinking of how many security exploits this would
*create*. So if I call a security_definer function which has a
search_path set on it which search_path would it use?

This seems like it would make it impossible to code any extension to
work reliably. You would never know when some object in your extension
was being hidden by some public object which the locked search_path
overrode.

Hm, I'm beginning to think extensions need to have search_path set on
every function or have every object reference everywhere be explicitly
pg_extension.* (and/or _private_.* like my earlier suggestion).

> e) having roles somehow inherit search_path on a SET ROLE***

Grr. I'm still bitter about "su" doing that on some systems without
"su -". I think I've lost that battle though and I'm forever doomed to
never know what "su" will do on a new system.

--
greg