Op 02-03-10 13:00, Thom Brown schreef:
On 2 March 2010 11:46, Nilesh Govindarajan<li...@itech7.com>  wrote:
On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown<thombr...@gmail.com>  wrote:

On 2 March 2010 11:12, Antonio Goméz Soto<antonio.gomez.s...@gmail.com>
wrote:
Hi,

I tried this:

names=# grant select on database names to spice;
ERROR:  invalid privilege type SELECT for database

The documentation seems to imply I need to grant SELECT
to each table separately. That's a lot of work, and what if
new tables are created?

Thanks,
Antonio

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


The privileges you can grant on a database are only related to the
creation of tables and connecting to that database.

You could create a role which has SELECT-only access, apply that role
to all your tables, and assign users (other roles) as members of that
role.

Regards

Thom

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

How to create that ? I'm also interested in this as I need this for backing
up my databases.

--

Okay, here's an example:

CREATE ROLE readonly; -- This user won't be able to do anything by
default, not even log in

GRANT SELECT on table_a TO readonly;
GRANT SELECT on table_b TO readonly;
GRANT SELECT on table_c TO readonly;

CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned
this user to any group

SET ROLE testuser;
SELECT * FROM table_a;

We get:
ERROR:  permission denied for relation table_a

SET ROLE postgres;

DROP ROLE testuser;
CREATE ROLE testuser WITH LOGIN IN ROLE readonly;

SET ROLE testuser;
SELECT * FROM table_a;

This would then return the results from table_a

Regards

Thom

But I still need to define access to each table separately?

Thanks,
Antonio.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to