Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id B9AA91337BE2 for ; Wed, 30 Mar 2011 15:06:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 89240-02-2 for ; Wed, 30 Mar 2011 18:06:34 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from web39702.mail.mud.yahoo.com (web39702.mail.mud.yahoo.com [209.191.106.48]) by mail.postgresql.org (Postfix) with SMTP id 03D061337C0C for ; Wed, 30 Mar 2011 15:06:23 -0300 (ADT) Received: (qmail 6460 invoked by uid 60001); 30 Mar 2011 18:06:21 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1301508381; bh=ui7Po3RnZ5mSn1s72XDijyvrIbzjcBux8rBm+9gwyTo=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=DAljWO0buRTAHmlh/5ON70kj6ZIQWtVQ1SnUINl/WAXP1o5DimOWMNvgW5oQfInPvsFjLhgnbPrFi5jotvJVCwJyyGz/5y94EbA++e7NESAVPn6BxrpaC2nPkOLbtqxPP893kjabCIoA0+63SE05BVUrnmUIBbSqvkJ9//xMtX8= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=X0mqHI4wjtp2qUV1Yr9pHWCgFoHYyhxMZ4zm1xFvkLI9+1iTusIxRjzeocYJdS+7CihERBKF42KgdWbDqSJ7Pq3mM8RvB/TczQBR52RFFRRzymh1nn4mHf7KKp4YmM3VQHR6HuGeMqkuIO1qbCzez5nzvBTSUtkJ6o6S7Wx+JJk=; Message-ID: <883883.1817.qm@web39702.mail.mud.yahoo.com> X-YMail-OSG: CIUcb0cVM1mXT_zG4aXVcCbH9BHFAw_NertEZJfqvMCRGAa zcA6P_guJHCFDFaSjEaQoyW36CtA4arr15uVZoTuho6SeRM1P0V7bD2w6rzC BJ7e6lUzNZT0DYGMkCU2Z9uG_uhyVF.G5EbZcTJ8Guh0aIGZep_urh.yyJWW bapR.Hckz6RxJnposaWVaN2T2swRrn8_RG18zcHJqy45FgqXAuwSXCHvWzF7 oCYI9DsXyWbGGnyKxoyEA9BEnKNupqLvIej3tGbTodmgHLgsIK2Ki2DIRjCl y9s2gp9ZEz9Ms.fUKs0P18wJK1szYpLWZGMpTErM0KnoEvZ6sNRm_VwmlqQr nZ.yNFXotmgGgIKqD5ZvRjrkk.G_ohVWiK4phzkLZZfMksMZ5bY0Vn_8HSFh 5t1uF.qUFMqkBSg-- Received: from [64.126.29.122] by web39702.mail.mud.yahoo.com via HTTP; Wed, 30 Mar 2011 11:06:21 PDT X-Mailer: YahooMailClassic/12.0.2 YahooMailWebService/0.8.109.295617 Date: Wed, 30 Mar 2011 11:06:21 -0700 (PDT) From: Bob Lunney Subject: Re: grant select script To: "pgsql-admin@postgresql.org" , Marc Fromm In-Reply-To: <93361C67E4EE844A80935863A4FF4B6C04E613C3@Exch2010MB-2.univ.dir.wwu.edu> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1320598356-1301508381=:1817" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.908 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/278 X-Sequence-Number: 37150 --0-1320598356-1301508381=:1817 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Marc, Try pg_stat_user_tables - it will eliminate the tables in pg_catalog, infor= mation_schema, and the toast tables. Bob Lunney --- On Wed, 3/30/11, Marc Fromm wrote: From: Marc Fromm Subject: [ADMIN] grant select script To: "pgsql-admin@postgresql.org" Date: Wednesday, March 30, 2011, 1:54 PM =0A=0A =0A =0A=0A=0A =0A=0AI am working with a script to automate grant sel= ect to all tables in a database to a user. =0A=C2=A0 1 #!/bin/bash =0A=C2= =A0 2 for table in 'echo "SELECT relname FROM pg_stat_all_tables;" | psql c= swe2 | grep -v "pg_" | grep "^ "'; =0A=C2=A0 3 do =0A=C2=A0 4 echo "GRANT S= ELECT ON TABLE $table to tom;" =0A=C2=A0 5 echo "GRANT SELECT ON TABLE $tab= le to tom;" | psql cswe2 =0A=C2=A0 6 done =0A =C2=A0 =0AThe script works=E2= =80=94meaning it grants the select to the user, but it generates errors on = tables that do not exist like the following. =0AThe data base cswe2 does no= t contain the table sql_languages, unless it is hidden. Is there a way to t= ell the script to ignore them? =0AGRANT SELECT ON TABLE sql_languages to to= m; =0AERROR:=C2=A0 relation "sql_languages" does not exist =0A =C2=A0 =0ATh= anks =0A =C2=A0 =0AMarc=0A =0A =C2=A0 =0A =C2=A0 =0AMarc Fromm =0AInformation Technology Specialist II =0AFinancial Aid Department =0AWestern Washington University =0APhone: 360-650-3351 =0AFax:=C2=A0=C2=A0 360-788-0251 =0A=0A =0A=0A=0A=0A=0A --0-1320598356-1301508381=:1817 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Marc,

Try pg_stat_user_tab= les - it will eliminate the tables in pg_catalog, information_schema, and t= he toast tables.

Bob Lunney

--- On Wed, = 3/30/11, Marc Fromm <Marc.Fromm@wwu.edu> wrote:

From: Marc Fromm <Marc.Fromm@wwu.edu>
Subjec= t: [ADMIN] grant select script
To: "pgsql-admin@postgresql.org" <pgsq= l-admin@postgresql.org>
Date: Wednesday, March 30, 2011, 1:54 PM
<= br>
=0A=0A =0A =0A=0A=0A =0A
=0A

I am working with a script to automate grant sel= ect to all tables in a database to a user.

=0A

  1 #!/bin/bash

=0A

&n= bsp; 2 for table in 'echo "SELECT relname FROM pg_stat_all_tables;" | psql = cswe2 | grep -v "pg_" | grep "^ "';

=0A

  3 do

=0A

  4 echo "GRA= NT SELECT ON TABLE $table to tom;"

=0A

  5 echo "GRANT SELECT ON TABLE $table to tom;" | psql cswe2

=0A=

  6 done

=0A

 

=0A

The scrip= t works=E2=80=94meaning it grants the select to the user, but it generates = errors on tables that do not exist like the following.

=0A

The data base cswe2 does not contain the table sql_la= nguages, unless it is hidden. Is there a way to tell the script to ignore t= hem?

=0A

GRANT SELECT ON TABLE sql_la= nguages to tom;

=0A

ERROR:  rela= tion "sql_languages" does not exist

=0A

 

=0A

Thanks

=0A

 

=0A

Marc=0A

=0A

 

=0A

 =

=0A

Marc Fromm
=0AInformation Technology Specialist II
=0AFinancial Aid= Department
=0AWestern Washington University
=0APhone: 360-650-3351=0AFax:   360-788-0251

=0A
=0A =0A=0A

=0A=0A --0-1320598356-1301508381=:1817--