Re: RFD: schemas and different kinds of Postgres objects

Fernando Nasser <fnasser(at)redhat(dot)com> writes:
> Tom Lane wrote:
>> I don't buy that premise. It's true that SQL92 equates ownership of a
>> schema with ownership of the objects therein, but AFAICS we have no hope
>> of being forward-compatible with existing database setups (wherein there
>> can be multiple tables of different ownership all in a single namespace)
>> if we don't allow varying ownership within a schema.

> Quoting Date (pg. 221): "The schema authID for a given schema identifies
> the owner of that schema (and hence the owner of everything described by
> that schema also)."

Yes, I know what the spec says. I also think we'll have a revolt on our
hands if we don't make it possible for existing Postgres applications to
continue working as they have in the past --- and that means allowing
tables of different ownerships to be accessible in a single namespace.

Although I haven't thought through the details yet, it seems to me that
a solution exists along these lines:

1. The creator of an object owns it. (With some special cases, eg the
superuser should be able to create a schema owned by someone else.)

2. Whether you can create an object in a schema that is owned by someone
else depends on permissions attached to the schema. By default only
the owner of a schema can create anything in it.

3. SQL92-compatible behavior is achieved when everyone has their own
schema and they don't grant each other create-in-schema rights.
Backwards-compatible behavior is achieved when everyone uses a
shared "public" schema.

We'd probably need GUC variable(s) to make it possible to choose which
behavior is the default. I haven't thought much about exactly what
knobs should be provided. I do think we will want at least these two
knobs:

1. A "search path" that is an ordered list of schemas to look in
when trying to resolve an unqualified name.

2. A "default schema" variable that identifies the schema to create
objects in, if a fully qualified name is not given.

The default creation location shouldn't be hardwired to equal the
front of the search path, because the front item of the search path
is probably always going to be a backend-local temporary schema
(this is where we'll create temporary tables).

The most dumbed-down version of this that would work is to reduce the
search path to just a fixed list of three locations: temp schema, a
selectable default schema (which is also the default creation location),
and a system schema (where pg_class and friends live). But a
user-settable path wouldn't be any more effort to support, and might
offer some useful capability.

regards, tom lane