Re: RFD: schemas and different kinds of Postgres objects

Tom Lane wrote:

>
> This would be a *whole* lot simpler if we forgot the notion of "any"
> and made the search order look like
>
> (temp, private, public, system)
>
> where the public namespace is world-writable but the private per-user
> ones are (typically at least) not.
>
> It occurs to me that we can get both backward-compatible and SQL92
> semantics with this same search path; the only thing that needs to
> be different in the two cases is whether the default place to create
> objects is your private schema or the public one. If you don't ever
> use your private schema then it doesn't matter if it's on the search
> path or not. I would still prefer that the search path be a settable
> option, since a paranoid person might well wish to not have public in
> his path at all ... but the default could be as-above.
>

I think it would be desirable to be able to restrict users from
"publishing" objects into the public schema. As an admin, I'd like some
control over the objects in this namespace. Hand-in-hand with this would
be the ability for the superuser to move (or "promote") an object from a
private schema to the public one. This would allow a user to develop
their own objects without interfering with others, but then make it
public with the superuser's assistance.

The search path you suggest above would then lead to the behavior that
unqualified references to objects will see my own objects before the
public ones, and other people's private objects must be explicitly
qualified.

Joe