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

Op 02-03-10 13:00, Thom Brown schreef:
> On 2 March 2010 11:46, Nilesh Govindarajan<lists(at)itech7(dot)com> wrote:
>> On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown<thombrown(at)gmail(dot)com> wrote:
>>>
>>> On 2 March 2010 11:12, Antonio Goméz Soto<antonio(dot)gomez(dot)soto(at)gmail(dot)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(at)postgresql(dot)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(at)postgresql(dot)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.