Re: Schema (namespace) privilege details

Joe Conway <mail(at)joeconway(dot)com> writes:
> If user1, the owner of the schema1, creates a new table tab1, will user2
> who has "Read" privilege to schema1, be automatically granted SELECT
> privilege on tab1? Or will he be able to see that tab1 exists, but not
> select from it (continuing the analogy with directories)?

No, and yes.

> This looks good to me. I only wonder if public should default to world
> read and no create?

That would be non-backwards-compatible. Since the main reason for
having the public namespace at all is backwards compatibility of the
out-of-the-box behavior, I think we have to let it default to world
write. DBAs can revoke world write, or even remove the public namespace
altogether, if they want to run a tighter ship.

> I like this. That way I can lock out a particular user if I need to with
> a single command. Would A automatically get ALL privileges on objects
> created in his schema by others? I think he should.

Hmm, I'd argue not; see nearby messages. The analogy with Unix
directory permissions seems to hold good here. If you are owner of
a directory you can delete files therein, but not necessarily do
anything else with 'em.

> I think only the database owner should be able to create schemas in
> their own database.

That seems overly restrictive to me; it'd be the equivalent of getting
rid of users that have createdb rights but aren't superusers.

Also, if a database owner is not superuser, I do not think he should be
able to create objects that are marked as belonging to other users.
At least not in general. Do we need to make an exception for schemas?

> Agreed. How would it work though if say I wanted to create a view in the
> public schema, which pointed at a table in a schema which has had SELECT
> revoked? Same question for a public function/private table. It would be
> ideal if you could do this.

AFAICS this would not be checked at creation time, but when someone
tries to use the view; just the same as now.

regards, tom lane