Re: search_path improvements WAS: search_path vs extensions - Mailing list pgsql-hackers

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