Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> Databases have two grantable rights: CREATE allows creating new regular
>> (permanent) schemas within the database, while TEMP allows creation of
>> a temp schema (and thus temp tables).
> Couldn't the temp schema be permanent (and unremovable), and thus the
> privilege to create temp tables can be handled by GRANT CREATE ON SCHEMA
> temp. It seems to me that creating an extra type of privilege to be able
> to create one specific schema that exists by default anyway(?) is
> overkill.
Well, it's not a single schema but a schema-per-backend. I suppose we
could do it as you suggest if we invent a "prototype" pg_temp schema
on which the rights can be stuck. But it doesn't seem obviously cleaner
to do it that way than to attach the rights to the database. In
particular, the idea of cloning a temp schema bothers me: if someone
sticks some tables into the prototype schema, should we clone those
too upon backend startup? If not, why not?
>> A new database will initially allow both these rights to world.
> Should it? Shouldn't the database owner have to give out schemas
> explicitly? This would be consistent with not being able to create
> subobjects in other people's schemas by default.
Well, I've been dithering about that. Zero public rights on creation
would clearly be more compatible with the way we handle other kinds
of rights. It would also clearly *not* be backwards-compatible with
our historical behavior for new databases.
It seems relevant here that existing pg_dumpall scripts will fail
miserably if CREATE DATABASE does not allow connect/create rights
to world by default.
Unless you see a way around that, my inclination is to allow rights as
I suggested. We could perhaps tighten this up in a release or three,
after we've fixed pg_dumpall to do something appropriate.
regards, tom lane