Thread: Revoke privilege from multiple tables

Revoke privilege from multiple tables

From
"Aaron Dummer"
Date:
Hello list,
 
I'd like to give a user only SELECT privileges, database-wide.  Is there an easy way to do this?  I could execute a REVOKE ALL PRIVILEGES command for each table, then execute a GRANT SELECT command for each table, but that is tedious.
 
It would be great to do something like this:
 
REVOKE ALL PRIVILEGES ON * FROM user_name
GRANT SELECT ON * TO user_name
 
Please help me.
 
Aaron Dummer

Re: Revoke privilege from multiple tables

From
Bruce Momjian
Date:
We don't have per-db grant/revoke capabilities.  You could create a
plpgsql function that does the grant/revokes for an arbitrary table
name, then call the function on all the pg_class object, skipping the
system tables, of course.  I think there are some examples on the
plpgsql cookbook web page referenced on the techdocs web site.

This is becoming an FAQ, folks.

---------------------------------------------------------------------------

Aaron Dummer wrote:
> Hello list,
>
> I'd like to give a user only SELECT privileges, database-wide.  Is there an easy way to do this?  I could execute a
REVOKEALL PRIVILEGES command for each table, then execute a GRANT SELECT command for each table, but that is tedious. 
>
> It would be great to do something like this:
>
> REVOKE ALL PRIVILEGES ON * FROM user_name
> GRANT SELECT ON * TO user_name
>
> Please help me.
>
> Aaron Dummer

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073