On 2 March 2010 11:46, Nilesh Govindarajan <lists@xxxxxxxxxx> wrote: > On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown <thombrown@xxxxxxxxx> wrote: >> >> On 2 March 2010 11:12, Antonio Goméz Soto <antonio.gomez.soto@xxxxxxxxx> >> 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@xxxxxxxxxxxxxx) >> > 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@xxxxxxxxxxxxxx) >> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
