Re: search_path improvements WAS: search_path vs extensions

Greg,

> Do we really? The only reason people are having trouble managing their
> search_path is because they're not using it as intended and putting
> things in lots of different schemas that they intend to all be
> visible.

Apparently you've never adminned a database with hundreds (or thousands)
of stored procedures.

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

In fact, I'd argue that that is one of the problems with the whole
schema concept: it's three things at once.

> I'm actually not sure if we should allow extensions to be installed
> into separate schemas. If you do then it means we can't detect
> conflicts. A module might refer to an object intending to get its
> local object but end up getting some object from some other module
> depending on how the user set up his search_path.

I agree with this. Eliminating module naming conflicts is a good in itself.

From a DBA and database designer perspective, the missing functionality
from being able to do everything with schema that I want are listed
below. It's been my experience that the awkwardness of managing
search_path has caused a *lot* of our users to ignore schema as a
feature and not use schema when they otherwise should.

a) the ability to "push" a schema onto the current search path
b) the ability to "pull" a schema off the current search path
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.*
d) the ability as superuser to "lock" specific role so that they can't
change their search path**
e) having roles somehow inherit search_path on a SET ROLE***

* 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?

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

*** this is the same issue as it is with resource management (i.e.
work_mem). However, it's particularly apt for search_path; imagine a
database with an "accounting" schema and a user who belongs to both the
"accounting" and the "HR" roles.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com