Re: [ADMIN] how to revoke multiple users permission from multiple tables at the same time?

[DM <dm.aeqa@xxxxxxxxx>]

Thanks Scott.

Good answer, I was consolidating the schemas here, there were too many users were granted permission to tables.I wanted to consolidate/optimize to bring it to one role and granting this role to the user (same way as you mentioned).

Thanks for the solution.

Thanks

Deepak

On Wed, Apr 22, 2009 at 4:02 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:

On Wed, Apr 22, 2009 at 5:01 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
> On Wed, Apr 22, 2009 at 4:19 PM, DM <dm.aeqa@xxxxxxxxx> wrote:
>> how to revoke multiple users permission from multiple tables at the same
>> time?
>> Or in simple is there a way to revoke multiple users grant access from
>> multiple tables under a schema.?
>
> Best way is to NOT grant multiple users permissions, but to grant a
> role the permissions and grant that role to users.  That way you only
> have to revoke persmissions from the role to revoke it from all the
> users.
>
>> I use Revoke below command to execute on each table one by one.
>> revoke SELECT/ALL on testtable from user1;
>
> Note that you can build a set of revoke commands by using selects and
> concatenations if you need them.  Something like this (use psql -E to
> see the queries \ commands invoke in psql)
>
> SELECT 'revoke all from somename on '||n.nspname||'.'||  c.relname ||';'
> FROM pg_catalog.pg_class c
>     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
>     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','v','S','')
>  AND n.nspname <> 'pg_catalog'
>  AND n.nspname !~ '^pg_toast'
>  AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1;
>
>                   ?column?
> ----------------------------------------------
>  revoke all from somename on public.colony;
>  revoke all from somename on public.delegate;
>  revoke all from somename on public.jt1;
>  revoke all from somename on public.jt2;
>  revoke all from somename on public.mytable;
>  revoke all from somename on public.test1;
>  revoke all from somename on public.test2;
>  revoke all from somename on public.tmp;
> (8 rows)

In my hurry I got the order wrong, you want the revoke to look like this:

revoke all on tablename from somename;

I leave it to you to rebuild the query to get what ya need.