Re: Schema (namespace) privilege details

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Schema (namespace) privilege details
Date: 2002-04-19 00:17:46
Message-ID: 8651.1019175466@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Loftis 2002-04-19 00:18:31 Re: Index Scans become Seq Scans after VACUUM ANALYSE
Previous Message Tom Lane 2002-04-19 00:10:41 Re: Schema (namespace) privilege details