pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedREVOKE from all tables ...
7+ messages / 6 participants
[nested] [flat]
* REVOKE from all tables ...
@ 2005-08-09 03:46 Marc G. Fournier <scrappy@postgresql.org>
0 siblings, 2 replies; 7+ messages in thread
From: Marc G. Fournier @ 2005-08-09 03:46 UTC (permalink / raw)
To: pgsql-admin
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 ...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE from all tables ...
@ 2005-08-09 04:45 Joshua D. Drake <jd@commandprompt.com>
parent: Marc G. Fournier <scrappy@postgresql.org>
1 sibling, 1 reply; 7+ messages in thread
From: Joshua D. Drake @ 2005-08-09 04:45 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE from all tables ...
@ 2005-08-09 05:20 Marc G. Fournier <scrappy@postgresql.org>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 7+ messages in thread
From: Marc G. Fournier @ 2005-08-09 05:20 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE from all tables ...
@ 2005-08-09 13:20 Stephen Frost <sfrost@snowman.net>
parent: Marc G. Fournier <scrappy@postgresql.org>
1 sibling, 2 replies; 7+ messages in thread
From: Stephen Frost @ 2005-08-09 13:20 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-admin
* 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
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE from all tables ...
@ 2005-08-09 14:09 Guido Barosio <gbarosio@gmail.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 7+ messages in thread
From: Guido Barosio @ 2005-08-09 14:09 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE from all tables ...
@ 2005-08-09 14:55 John DeSoi <desoi@pgedit.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 7+ messages in thread
From: John DeSoi @ 2005-08-09 14:55 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE from all tables ...
@ 2005-08-11 01:36 ljb <ljb220@mindspring.com>
parent: Marc G. Fournier <scrappy@postgresql.org>
0 siblings, 0 replies; 7+ messages in thread
From: ljb @ 2005-08-11 01:36 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-08-11 01:36 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-09 03:46 REVOKE from all tables ... Marc G. Fournier <scrappy@postgresql.org>
2005-08-09 04:45 ` Joshua D. Drake <jd@commandprompt.com>
2005-08-09 05:20 ` Marc G. Fournier <scrappy@postgresql.org>
2005-08-11 01:36 ` ljb <ljb220@mindspring.com>
2005-08-09 13:20 ` Stephen Frost <sfrost@snowman.net>
2005-08-09 14:09 ` Guido Barosio <gbarosio@gmail.com>
2005-08-09 14:55 ` John DeSoi <desoi@pgedit.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox