Re: search_path improvements

On Sun, May 31, 2009 at 9:12 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> This assumes that all users should have access to the same public APIs as
> all other users.  Real applications are more complex.

Well the goal is to make them simpler. I don't know any language that
has implemented what you describe. Either you have access to the
internal methods of a class or you don't and you only have access to
the public api. That seems to work for much more sophisticated
languages than ours just fine.

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

Well when you ls a directory or perform some operation on a file you
only work with what's in that directory, not everything in the
hierarchy.

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

I don't think that gets any easier with better tools. This is the same
reason unix systems don't put every tool in a different directory and
then insist you put every directory in your path based on which tools
each user should have access to.

What you're describing is a fundamentally painful thing to do. You
have to decide for every user what objects they should have access to
and which they shouldn't. It doesn't get any ideasier if you have
every function hard coding inside it assumptions about what schemas it
will need.

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

How is this different from any other analogous system? The filesystem
uses directories for all three of the above, for example?

Having three different namespaces, one for organizing your code, one
to control visibility, and one to control security would be 9 times
more complex, i think.

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

3rd-party vendor code is precisely what I'm thinking of when I point
out that having global state to override what the code requests is a
recipe for problems. 3rd-party vendors would be left with no way to
write their code such that they could guarantee it would work -- the
DBA would always be able to break it by setting this variable. And
some other code might require this variable to be set leaving the
hapless DBA with no right option.

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

Well I don't mind push but I still think pop is an error. What you
really want to do is restore it to the value you started with. You
don't want to remove the last element since that may not be the
element you added. Some function you called may have added an extra
element on the head.

--
greg