Re: How to grant a user read-only access to a database?

On 2 March 2010 11:46, Nilesh Govindarajan <lists@itech7.com> wrote:
> On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown <thombrown@gmail.com> wrote:
>>
>> On 2 March 2010 11:12, Antonio Goméz Soto <antonio.gomez.soto@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