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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
