Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A5BAA9FB6F4 for ; Tue, 29 May 2007 16:51:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 77631-04 for ; Tue, 29 May 2007 16:51:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 22CB69FB28B for ; Tue, 29 May 2007 16:51:49 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from cerberus.lfix.co.uk (lfix.demon.co.uk [80.177.205.209]) by svr4.postgresql.org (Postfix) with ESMTP id 4B6895AF025 for ; Tue, 29 May 2007 19:51:48 +0000 (GMT) Received: from [192.168.8.1] by cerberus.lfix.co.uk with esmtp (Exim 4.63) (envelope-from ) id 1Ht7jL-0003ot-6T for pgsql-admin@postgresql.org; Tue, 29 May 2007 20:51:43 +0100 Subject: Re: Grant SELECT on all tables of a database From: Oliver Elphick To: pgsql-admin@postgresql.org In-Reply-To: <336463.96075.qm@web31809.mail.mud.yahoo.com> References: <336463.96075.qm@web31809.mail.mud.yahoo.com> Content-Type: text/plain Organization: LFIX Date: Tue, 29 May 2007 20:51:42 +0100 Message-Id: <1180468302.15764.31.camel@linda.lfix.co.uk> Mime-Version: 1.0 X-Mailer: Evolution 2.10.1 Content-Transfer-Encoding: 7bit X-LFIX-MailScanner: Found to be clean X-LFIX-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=-1.44, required 5, ALL_TRUSTED -1.44) X-LFIX-MailScanner-From: olly@lfix.co.uk X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.045 tagged_above=0 required=5 tests=AWL=-0.091, BAYES_50=0.001, FORGED_RCVD_HELO=0.135 X-Spam-Level: X-Archive-Number: 200705/305 X-Sequence-Number: 25625 On Tue, 2007-05-29 at 10:21 -0700, Marsha Ramsey wrote: > Hello all, > > How do I GRANT SELECT to all my tables at once? I have over 1026 > tables in this database... I don't think there is any direct SQL command to do it. But try this: psql -d your_database \t \a \o /tmp/sqlscript SELECT 'GRANT SELECT ON ' || schemaname || '.' || tablename || ' TO PUBLIC ;' FROM pg_tables WHERE tableowner = CURRENT_USER; \o \i /tmp/sqlscript -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html