how to revoke multiple users permission from multiple tables at the same time?

On Wed, Apr 22, 2009 at 4:19 PM, DM <dm.aeqa@gmail.com> 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)

On Wed, Apr 22, 2009 at 5:01 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Wed, Apr 22, 2009 at 4:19 PM, DM <dm.aeqa@gmail.com> 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.

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@gmail.com>wrote: