Re: [ADMIN] REVOKE from all tables ...
[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] REVOKE from all tables ...
* Marc G. Fournier ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [ADMIN] REVOKE from all tables ...
It would be great to look at that :) g On 8/9/05, Stephen Frost [EMAIL PROTECTED] wrote: * Marc G. Fournier ([EMAIL PROTECTED]) 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 aregexp to match names against, allows for limitation to a specific schema, and handles tables, views, sequences and functions.I'll seeabout making it available (it's not very big).Of course, I think it'dbe 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/fSLEbDWTPD7LXaAkuhkUpaF1ACeKIAfjLhZ8F+CScCkRQtBDg3mucc= =WoOm-END PGP SIGNATURE--- Adopting the position that you are smarter than an automaticoptimization algorithm is generally a good way to achieve less performance, not more - Tom Lane.
Re: [ADMIN] REVOKE from all tables ...
On Aug 9, 2005, at 9:20 AM, Stephen Frost wrote: * Marc G. Fournier ([EMAIL PROTECTED]) 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] 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 ... ---(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
Re: [ADMIN] REVOKE from all tables ...
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
Re: [ADMIN] REVOKE from all tables ...
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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster