select any table

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

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

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

I'm just curious how would having the ability to grant privileges to a
schema be a foot gun?

On Tue, Mar 25, 2008 at 2:54 PM, Joshua D. Drake <jd@commandprompt.com>
wrote:

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