Re: How to grant a user read-only access to a database? - Mailing list pgsql-general

On 2 March 2010 14:49, Antonio Goméz Soto <antonio.gomez.soto@gmail.com> wrote:
> Op 02-03-10 13:00, Thom Brown schreef:
>>
>> 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
>
> But I still need to define access to each table separately?
>
> Thanks,
> Antonio.
>

As far as I'm aware.  It's only in the upcoming version 9.0 that you
can do things like:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Other folk on here may have some alternative suggestions though.

Thom