Re: select any table

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Sam Mason
> Sent: Wednesday, March 26, 2008 7:14 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] select any table
>
> On Tue, Mar 25, 2008 at 05:37:00PM -0400, Malinka Rellikwodahs wrote:
> > On Tue, Mar 25, 2008 at 2:54 PM, Joshua D. Drake wrote:
> > > On Tue, 25 Mar 2008 13:37:37 -0500 Jon Roberts wrote:
> > > > It would be a nice enhancement to have a "select any table"
> privilege
> > > > or at least "grant insert/update/delete/select on
<schema_name>".
> > >
> > > Certainly, but it is also a foot gun.
> >

I think the bigger foot gun would be a lazy dba granting auditors
"superuser" in place of a read-only account.

> > I'm just curious how would having the ability to grant privileges to
a
> > schema be a foot gun?
>
> In ACL (Access Control List) systems this sort of "privilege" isn't
very
> natural. The closest thing I can imagine is by having a "default" set
> of permissions that the user has control over, rather than currently
> where the set of default permissions is fixed by PG to only include
> unrestricted access by the owner. Another solution, and probably the
> footgun that Joshua was referring to, would be to have some code that
> is automatically run when a new object is created that grants
read-only
> access. I don't think PG provides a way to do this at the moment
> though.
>

Hmm, that is probably why Oracle treats this as a "system privilege" as
apposed to being granted rights to a table or role.

The ANSI standard is database.schema.table right? So when you don't
specify the database name, it is supposed to default to the current one.
When executing a query, couldn't PG check the database first for "read"
like it probably already does for connect, create, and temporary?

> Other security models allow this case to be more directly expressed.
> My current favourite is capability based security, it allows you to
> directly say that "auditors" have transitively read-only access to
> specific things (i.e. the entire database).
>

I like that too. I know Oracle and MS SQL Server have this (select any
table and db_datareader respectively). I've not used MySQL but a quick
google shows they have a "grant all on db.* to user".

Jon