pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
REVOKE 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