X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3D5EE52E8E for ; Wed, 10 Aug 2005 22:44:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50596-07 for ; Thu, 11 Aug 2005 01:44:02 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id E8DAA52E8F for ; Wed, 10 Aug 2005 22:44:01 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j7B1i03Q092299 for ; Thu, 11 Aug 2005 01:44:00 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j7B1aNUb071885 for pgsql-admin@postgresql.org; Thu, 11 Aug 2005 01:36:23 GMT (envelope-from news) From: ljb X-Newsgroups: pgsql.admin Subject: Re: REVOKE from all tables ... Date: Thu, 11 Aug 2005 01:36:23 +0000 (UTC) Organization: Hub.Org Networking Services Lines: 34 Message-ID: References: <20050809004545.S958@hub.org> <42F834F6.2090908@commandprompt.com> <20050809021939.J1002@ganymede.hub.org> X-Complaints-To: usenet@news.hub.org User-Agent: slrn/0.9.8.1 (Linux) To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200508/137 X-Sequence-Number: 18456 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.