Re: RFD: schemas and different kinds of Postgres objects

Tom Lane wrote:
>
> > 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.
>

But them it is not SQL-Schemas. Call it something else, "packages"
for instance. The standard has lots of rules and other considerations
all around the document that depend on schemas have the meaning they
assigned to it.

If someone wants to really make use of SQL-Schemas, he/she will need to
reorg the database anyway, which will probably mean dumping the data,
massaging the DLL and recreating it. I guess most users of SQL-Schemas
will be people creating new databases.

For the current users, (based on your idea below) a default behavior of
searching the current-AuthID schema, them the "default" schema them
"any"
schema will probably make things work.

Fernando

P.S.: Note that the standard has no GRANTs for SCHEMAs themselves; all
GRANTS go to the specific objects as before.

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser(at)redhat(dot)com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9