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

On Tue, Mar 2, 2010 at 8:26 PM, Thom Brown <thombrown(at)gmail(dot)com> wrote:

> On 2 March 2010 14:49, Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com>
> wrote:
> > 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.
> >
>
> 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
>

Eagerly waiting for 9.0....

--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com