Re: permissions

On Wed, May 30, 2012 at 11:26 PM, Ross Boylan <ross@biostat.ucsf.edu> wrote:
> Is there an easy way to give role x blanket access to the objects in a
> database?

As of 9.0, you can use:
  GRANT SELECT ON ALL TABLES IN SCHEMA ... TO ... ;

If you have many schemas, it should be easy to create a PL/pgSQL
function to iterate over them and run the above GRANT for each. If
you're stuck on 8.4, you might have to resort to a PL/pgSQL function
to iterate over the tables in your database and run the necessary
GRANTs.

Another strategy worth mentioning is to figure out which tables should
be readable by all users in the database, and use
  GRANT SELECT ON ... TO PUBLIC;

on those tables.

> My theory is that I would need to grant privileges individually for each
> table, index, ....
>
> Is that theory right?  Is there a command to do it all at  once?  I
> though grant ... database... was that command, but it didn't work.

Granting, say, ALL PRIVILEGES on a database essentially allows the
specified user to create *new* objects in that database, but does not
affect the privileges on objects in the database created by other
users.

Josh