Thread: select any table

I have some users that need "select any table" but they should not be
superusers.  How can this be done?

I need a "grant select on <dbname> to <rolename>".


Jon

Roberts, Jon wrote:
> I need a "grant select on <dbname> to <rolename>".
>
>
This is a FAQ, though it doesn't actually seem to be in the PostgreSQL FAQ.

A Google search, either of the mailing list archives or of the web in
general, for:

    postgresql grant all tables

should prove informative.

http://www.google.com/search?q=postgresql+grant+all+tables
http://www.google.com/search?q=postgresql+grant+all+tables+site%3Aarchives.postgresql.org

--
Craig Ringer

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, 25 Mar 2008 09:54:20 -0500
"Roberts, Jon" <Jon.Roberts@asurion.com> wrote:

> I have some users that need "select any table" but they should not be
> superusers.  How can this be done?  
> 
> I need a "grant select on <dbname> to <rolename>".

You can't do it with a single command. It is easy enough to write a
query to grab the tables and grant select on them though.

Sincerely,

Joshua D. Drake


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH6Rc0ATb/zqfZUUQRAuXYAKCjVwoLHZLasWFGUM4JwOa82yohEgCfRyRo
nkLbi/vImp7jA+bqZD1o4Jc=
=vFAR
-----END PGP SIGNATURE-----

> On Tue, 25 Mar 2008 09:54:20 -0500
> "Roberts, Jon" <Jon.Roberts@asurion.com> wrote:
>
> > I have some users that need "select any table" but they should not
be
> > superusers.  How can this be done?
> >
> > I need a "grant select on <dbname> to <rolename>".
>
> You can't do it with a single command. It is easy enough to write a
> query to grab the tables and grant select on them though.
>

We are adding tables and schemas all of the time and we need to grant
auditors read-only access to the database.


Jon

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, 25 Mar 2008 10:16:19 -0500
"Roberts, Jon" <Jon.Roberts@asurion.com> wrote:

> > On Tue, 25 Mar 2008 09:54:20 -0500
> > "Roberts, Jon" <Jon.Roberts@asurion.com> wrote:
> > 
> > > I have some users that need "select any table" but they should not
> be
> > > superusers.  How can this be done?
> > >
> > > I need a "grant select on <dbname> to <rolename>".
> > 
> > You can't do it with a single command. It is easy enough to write a
> > query to grab the tables and grant select on them though.
> > 
> 
> We are adding tables and schemas all of the time and we need to grant
> auditors read-only access to the database.  
> 

O.k. :) but that doesn't change my response. You can't do it with a
single command. You can script it.


http://pgedit.com/public/sql/acl_admin/index.html
http://unf.be/postgresql/postgres_grant_all.perl
http://www.archonet.com/pgdocs/grant-all.html

There are some links that may help you.

Sincerely,

Joshua D. Drake

> 
> Jon
> 


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH6T4TATb/zqfZUUQRAseZAJ4zR3NhBoNbwrbjNaTIdLR0UFOuhwCbBT3D
NgEqYHc68KKhWnks5g8vrW0=
=7R0w
-----END PGP SIGNATURE-----

> > > On Tue, 25 Mar 2008 09:54:20 -0500
> > > "Roberts, Jon" <Jon.Roberts@asurion.com> wrote:
> > >
> > > > I have some users that need "select any table" but they should
not
> > be
> > > > superusers.  How can this be done?
> > > >
> > > > I need a "grant select on <dbname> to <rolename>".
> > >
> > > You can't do it with a single command. It is easy enough to write
a
> > > query to grab the tables and grant select on them though.
> > >
> >
> > We are adding tables and schemas all of the time and we need to
grant
> > auditors read-only access to the database.
> >
>
> O.k. :) but that doesn't change my response. You can't do it with a
> single command. You can script it.
>
>

It would be a nice enhancement to have a "select any table" privilege or
at least "grant insert/update/delete/select on <schema_name>".


Jon

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, 25 Mar 2008 13:37:37 -0500
"Roberts, Jon" <Jon.Roberts@asurion.com> wrote:

> > O.k. :) but that doesn't change my response. You can't do it with a
> > single command. You can script it.
> > 
> > 
> 
> 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.

Sincerely,

Joshua D. Drake



- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH6UptATb/zqfZUUQRAmhQAJ0bzU5LCF0GU4r3SgihoE22xuqedQCdEOcB
w3Im5SsA8Y1I4NZNcx1XNjc=
=pOZY
-----END PGP SIGNATURE-----

On 25/03/2008 14:54, Roberts, Jon wrote:
> I have some users that need "select any table" but they should not be
> superusers.  How can this be done?
>
> I need a "grant select on <dbname> to <rolename>".

PgAdmin (www.pgadmin.org) has a handy "Grant Wizard" which will do this
for you in one go.

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On Tuesday 25 March 2008, "Roberts, Jon" <Jon.Roberts@asurion.com> wrote:
> We are adding tables and schemas all of the time and we need to grant
> auditors read-only access to the database.

Make a "grant select on table to auditors;" a standard part of your table
creation process.

--
Alan

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'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.

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).


  Sam

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Sam Mason
> Sent: Wednesday, March 26, 2008 7:14 AM
> To: pgsql-general@postgresql.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

On Wed, Mar 26, 2008 at 07:27:49AM -0500, Roberts, Jon wrote:
> I think the bigger foot gun would be a lazy dba granting auditors
> "superuser" in place of a read-only account.

At least that would stop users revoking audit access to the tables!
:) Any scheme that purports to allow this (i.e. disallows revoking
of access) should be taken out and shot quickly.  Any language of
reasonable complexity will support some form of information hiding (aka
abstraction) and suggesting you can stop this by disallowing revoking of
access is just silly.

> Sam Mason wrote:
> > 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.

Sorry, I don't know Oracle.  That sounds like a rather awkward way
of doing things in general, though it probably works well enough in
practise.

> 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?

Sounds pretty intractable, how do you revoke access sanely?

> > 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".

Sorry, I was using "capability" as a technical term and not a
descriptive one.  Capability security is *very* different from the ACL
(or more technically, "identity" or "role") based security mechanisms in
Oracle and MS SQL.


  Sam