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 ...

---------------------------(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

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

* 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFC+K2OrzgMPqB3kigRAqayAJ9o/fSLEbDWTPD7LXaAkuhkUpaF1ACeKIAf
jLhZ8F+CScCkRQtBDg3mucc=
=WoOm
-----END PGP SIGNATURE-----

It would be great to look at that
g

On 8/9/05, Stephen Frost <sfrost@snowman.net> 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.
>
> Stephen
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFC+K2OrzgMPqB3kigRAqayAJ9o/fSLEbDWTPD7LXaAkuhkUpaF1ACeKIAf
> jLhZ8F+CScCkRQtBDg3mucc=
> =WoOm
> -----END PGP SIGNATURE-----
>
>
>


--
"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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.