Thread: REVOKE from all tables ...

Is there a way of REVOKEing privileges on all tables in a database?  I've
checked the REVOKE man page, and there doesn't appear to be, but I figured
I'd double check to make sure ...

Marc G. Fournier wrote:

>
> Is there a way of REVOKEing privileges on all tables in a database?
> I've checked the REVOKE man page, and there doesn't appear to be, but
> I figured I'd double check to make sure ...

No. This has actually been a requested feature on the grant side as
well... Nothing a simple for loop can't do with perl though.

>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

On Mon, 8 Aug 2005, Joshua D. Drake wrote:

> Marc G. Fournier wrote:
>
>>
>> Is there a way of REVOKEing privileges on all tables in a database?  I've
>> checked the REVOKE man page, and there doesn't appear to be, but I figured
>> I'd double check to make sure ...
>
> No. This has actually been a requested feature on the grant side as well...
> Nothing a simple for loop can't do with perl though.

Ya, that is what I did, it just seemed that there should be a much
simplier way of doing it :(  Ah well, at least I wasn't re-creating the
wheel on this one :)

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

* Marc G. Fournier (scrappy@postgresql.org) wrote:
> Is there a way of REVOKEing privileges on all tables in a database?  I've
> checked the REVOKE man page, and there doesn't appear to be, but I figured
> I'd double check to make sure ...

I actually wrote a little perl script which allows you to pass in a
regexp to match names against, allows for limitation to a specific
schema, and handles tables, views, sequences and functions.  I'll see
about making it available (it's not very big).  Of course, I think it'd
be nice to have some functions in core that did the same thing; or maybe
some ability in psql to do it.

    Stephen

--
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
performance, not more" - Tom Lane.

On Aug 9, 2005, at 9:20 AM, Stephen Frost wrote:

> * Marc G. Fournier (scrappy@postgresql.org) wrote:
>
>> Is there a way of REVOKEing privileges on all tables in a
>> database?  I've
>> checked the REVOKE man page, and there doesn't appear to be, but I
>> figured
>> I'd double check to make sure ...
>>
>
> I actually wrote a little perl script which allows you to pass in a
> regexp to match names against, allows for limitation to a specific
> schema, and handles tables, views, sequences and functions.  I'll see
> about making it available (it's not very big).  Of course, I think
> it'd
> be nice to have some functions in core that did the same thing; or
> maybe
> some ability in psql to do it.

Also, there are some pl/pgsql functions available from here which
will GRANT/REVOKE on all tables:

http://pgedit.com/node/20


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

scrappy@postgresql.org wrote:
> On Mon, 8 Aug 2005, Joshua D. Drake wrote:
>
>> Marc G. Fournier wrote:
>>
>>>
>>> Is there a way of REVOKEing privileges on all tables in a database?  I've
>>> checked the REVOKE man page, and there doesn't appear to be, but I figured
>>> I'd double check to make sure ...
>>
>> No. This has actually been a requested feature on the grant side as well...
>> Nothing a simple for loop can't do with perl though.
>
> Ya, that is what I did, it just seemed that there should be a much
> simplier way of doing it :(  Ah well, at least I wasn't re-creating the
> wheel on this one :)


I like doing things like with just psql as shown below. And, I get to
look at the commands before running them.  For example: something
like this to revoke all for all tables in the public schema:

  -- Turn off headers:
  \t
  -- Use SQL to build SQL:
  SELECT 'REVOKE ALL ON public.' || table_name || ' FROM PUBLIC;'
  FROM information_schema.tables
    WHERE table_type = 'BASE TABLE' AND table_schema='public';
  -- If the output looks good, write it to a file and run it:
  \g out.tmp
  \i out.tmp

It works pretty well on similar tasks, at least until you run into string
quote/escape problems.