Greg,

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 is an interesting idea. However, what our real users are really doing in the field is more diverse. (see below)

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).

This assumes that all users should have access to the same public APIs as all other users. Real applications are more complex.

In my experience of PostgreSQL applications, people use schema for three different reasons:

Organization/Maintainability: when you have hundreds or thousands of database objects, you want "folders" to put them in just so that you can keep track of them and view them in easy-to-digest groups, just as you deal with files in a filesystem. DBAs no more want to put everything in one big flat namespace, even if the individual names are unique and the permissions are the same, than we want to have all of the PostgreSQL source code in one big directory. Further, these schema names generally indicate something about the purpose of the objects in them: "cms","matviews", "reports","calendar". When accurate, these schema names aid the DBA in maintaining and troubleshooting the application, and are more convenient than hungarian notation schemes.

Visibility: some applications use schema to hide objects from roles which shouldn't see them: "inner","cronjobs","acl", whether for data hiding or just to keep "private" functions and tables separate from what the application accesses directly. However, this approach is not very common *because of* the awkwardness and overhead of search_path; DBAs are constantly troubleshooting search_path omissions and errors and eventually give up on visibility rules, making all schema visible to all users. This gets even more difficult when you consider that in a large complex application with multiple ROLEs, not all ROLEs should see all schema, but what an individual user can access might be a list of schema which represent some-but-not-all schema. The lack of a convenient "search_path_add" or "SET ROLE ... WITH DEFAULTS" makes this an unmanageable mess; DBAs find themselves constantly ALTERing each user's search_path individually.

Security: schema provide convenient containers to lock up groups of objects by role. "admin", "permissions" etc. schemas combine visibility and USE restrictions to make sql injection much harder, and administrative tasks are supported by objects in schema not accessible to the "webuser".

As I said before, schema conbine 3 purposes: organization, visibility and security, into one structure. Which is why it's difficult to make them work perfectly for all 3 purposes. We could, however, make them work better.

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.

Easily said for someone who doesn't have to adapt a 3rd-party vendor application or support real users on the phone. Insisting that all of your application developers remember to type "information_schema." all of the time really makes them love the DBA. Mostly, this simply results in people not using information_schema, and instead using their own home-grown system view scripts, which are often wrong.

However, if we had push/pop/shift/unshift for search_path, the need for search_path_suffix would be considerably diminished, since application code (& DBAs) would use push/pop instead of replacing the entire search_path.

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).

Again, I'm not talking about Extensions. I think that Extensions are completely orthagonal to search_path, hence the change of subject line. I'm talking about making search_path (and schema) more useful to DBAs and application designers.

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.

As previously discussed, this would work via something like SET ROLE ... WITH DEFAULTS, rather than with just SET ROLE. We don't want to break backwards compatibility.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to