From msramsey22@yahoo.com Tue May 29 17:21:10 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 713189FBC36 for ; Tue, 29 May 2007 14:21:10 -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 05691-01 for ; Tue, 29 May 2007 14:21:00 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web31809.mail.mud.yahoo.com (web31809.mail.mud.yahoo.com [68.142.207.72]) by postgresql.org (Postfix) with SMTP id 98DE79FBB8C for ; Tue, 29 May 2007 14:21:03 -0300 (ADT) Received: (qmail 97411 invoked by uid 60001); 29 May 2007 17:21:02 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=Bi3Wc/JEkbDWDihZceoR9EsIkfrSpPb6RernVxliBowZuK2/kx8PEJstFkLjiFjb/zzanuAZxK4S7Oaz/axBBxnE0I6LOCw4eZexxnT3emnWd0Zhs6DZItb5zBsHkiEk+/+rY2dUe1+YVBmLC8Ds5tJMe/xOSvW0UGhqx5LuYUc=; X-YMail-OSG: iq7drOkVM1kY7YIuDU6uQHJB1Gw4YPB_bGhFOVEY..nzrmX0IAZrH4ZAgbhgr6ba.GSZJDNU.UO.j7r0xWLn_InDQtHiDD6OpGWTlWnmRguDd.gDvX9xN2GgR7.qmQ-- Received: from [170.153.62.251] by web31809.mail.mud.yahoo.com via HTTP; Tue, 29 May 2007 10:21:02 PDT Date: Tue, 29 May 2007 10:21:02 -0700 (PDT) From: Marsha Ramsey Subject: Grant SELECT on all tables of a database To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-409708472-1180459262=:96075" Content-Transfer-Encoding: 8bit Message-ID: <336463.96075.qm@web31809.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.852 tagged_above=0 required=5 tests=AWL=-0.639, BAYES_50=0.001, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, HTML_50_60=0.134, HTML_MESSAGE=0.001 X-Spam-Level: ** X-Archive-Number: 200705/304 X-Sequence-Number: 25624 --0-409708472-1180459262=:96075 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hello all, How do I GRANT SELECT to all my tables at once? I have over 1026 tables in this database... Thanks...Marsha --------------------------------- It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. --0-409708472-1180459262=:96075 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hello all,

How do I GRANT SELECT to all my tables at once?  I have over 1026 tables in this database...

Thanks...Marsha


It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar. --0-409708472-1180459262=:96075-- From olly@lfix.co.uk Tue May 29 19:51:56 2007 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 From msramsey22@yahoo.com Tue May 29 20:03:50 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id CC6059FB804 for ; Tue, 29 May 2007 17:03:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 35328-02 for ; Tue, 29 May 2007 17:03:43 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web31810.mail.mud.yahoo.com (web31810.mail.mud.yahoo.com [68.142.207.73]) by postgresql.org (Postfix) with SMTP id 615A89FB530 for ; Tue, 29 May 2007 17:03:46 -0300 (ADT) Received: (qmail 99004 invoked by uid 60001); 29 May 2007 20:03:45 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=Q7erxVKT+E+KEY9dpBoy+UgEgX0vNzRXkyxmmAhl9t27pTpYxkb9QCJopvHfuY1sxvQjVXBsFXv9/EoS4Gy69PTsimiklxTetRV/bty3N5quBgYT7qweDgJuJpLIcLypuNj3XC7+6CThluZUPpVRLR8rYfLeKS/R2fuhDRuPNRc=; X-YMail-OSG: gGLgipkVM1kXDyTTOsDYXDiDSF.qV9n4JneZWNQBS..E.HiOBsbDV_Xe7wgL3964QjMfCEaPyQMl8z8gzfbIu0nxfg1oItSe4KxTfjG3S._G7Gd.h_E- Received: from [170.153.62.251] by web31810.mail.mud.yahoo.com via HTTP; Tue, 29 May 2007 13:03:44 PDT Date: Tue, 29 May 2007 13:03:44 -0700 (PDT) From: Marsha Ramsey Subject: Re: Grant SELECT on all tables of a database To: Oliver Elphick , pgsql-admin@postgresql.org In-Reply-To: <1180468302.15764.31.camel@linda.lfix.co.uk> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1761832337-1180469024=:98855" Content-Transfer-Encoding: 8bit Message-ID: <27253.98855.qm@web31810.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.731 tagged_above=0 required=5 tests=AWL=-1.093, BAYES_40=-0.185, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, HTML_40_50=0.496, HTML_IMAGE_ONLY_20=1.157, HTML_MESSAGE=0.001 X-Spam-Level: *** X-Archive-Number: 200705/306 X-Sequence-Number: 25626 --0-1761832337-1180469024=:98855 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit This does the JOB...thank you very much!!!! Oliver Elphick wrote: 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend --------------------------------- Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. --0-1761832337-1180469024=:98855 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit This does the JOB...thank you very much!!!!

Oliver Elphick <olly@lfix.co.uk> wrote:
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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. --0-1761832337-1180469024=:98855--