Fernando Nasser <fnasser@redhat.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
ownedby 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-schemarights. 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
