On Tue, Mar 2, 2010 at 8:26 PM, Thom Brown <thombr...@gmail.com> wrote:
> On 2 March 2010 14:49, Antonio Goméz Soto <antonio.gomez.s...@gmail.com> > wrote: > > 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. > > > > 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