Received: from xyzzy.lan.internal (turbo-137.kingston.net [216.123.190.137]) by postgresql.org (Postfix) with ESMTP id D0AEE475A9A for ; Thu, 7 Mar 2002 15:41:13 -0500 (EST) Received: from drew by xyzzy.lan.internal with local (Exim 3.35 #1 (Debian)) id 16j4h9-00060C-00; Thu, 07 Mar 2002 15:40:59 -0500 Date: Thu, 7 Mar 2002 15:40:59 -0500 To: Paul Ogden Cc: Eric Du , pgsql-sql@postgresql.org Subject: Re: How to grant a privilege on all tables or views or both of a database to someone? Message-ID: <20020307204059.GA22890@xyzzy.dhs.org> References: Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="Qxx1br4bt0+wmkIi" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.3.27i From: "Andrew G. Hammond" X-Fnord: +++ath X-WebTV-Stationery: Standard; BGColor=black; TextColor=black X-Message-Flag: Message text blocked: ANTI-MICROSOFT OPINIONS X-BeenThere: drew@xyzzy.dhs.org X-Archive-Number: 200203/90 X-Sequence-Number: 6971 --Qxx1br4bt0+wmkIi Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Thu, Mar 07, 2002 at 10:55:51AM -0800, Paul Ogden wrote: > How to grant a privilege on all tables or views or both of a database to > someone?Here's what I did, using psql: Personally I solved this using plpgsql: -- usage: SELECT grant_all('privilidge', 'user'); -- grants privilidge (SELECT, UPDATE, INSERT, DELETE or ALL) to the user -- for all non-pg_ objects in the database (except indices). DROP FUNCTION grant_all(text, text); CREATE FUNCTION grant_all(text, text) RETURNS boolean AS ' DECLARE item RECORD; priv ALIAS FOR $1; user ALIAS FOR $2; BEGIN=20 FOR item IN SELECT * FROM pg_class=20 WHERE relname !~ ''^pg_''=20 AND relkind <> ''i'' LOOP EXECUTE ''GRANT '' || priv=20 || '' ON '' || quote_ident(item.relname) || '' TO '' || quote_ident(user); END LOOP; RETURN ''t''; END;' LANGUAGE 'plpgsql'; DROP FUNCTION grant_all_tables(text, text); CREATE FUNCTION grant_all_tables(text, text) RETURNS boolean AS ' DECLARE item RECORD; priv ALIAS FOR $1; user ALIAS FOR $2; BEGIN FOR item IN SELECT * FROM pg_class=20 WHERE relname !~ ''^pg_'' AND relkind =3D ''r'' LOOP EXECUTE ''GRANT '' || priv || '' ON '' || quote_ident(item.relname) || '' TO '' || quote_ident(user); END LOOP; RETURN ''t''; END;' LANGUAGE 'plpgsql'; DROP FUNCTION grant_all_tables_and_views(text, text); CREATE FUNCTION grant_all_tables_and_views(text, text) RETURNS boolean AS ' DECLARE item RECORD; priv ALIAS FOR $1; user ALIAS FOR $2; BEGIN FOR item IN SELECT * FROM pg_class WHERE relname !~ ''^pg_'' AND relkind IN (''r'', ''v'') LOOP EXECUTE ''GRANT '' || priv || '' ON '' || quote_ident(item.relname) || '' TO '' || quote_ident(user); END LOOP; RETURN ''t''; END;' LANGUAGE 'plpgsql'; --=20 Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481= =20 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me --Qxx1br4bt0+wmkIi Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjyH0FsACgkQCT73CrRXhLGWaQCffDvy4HBBuKsl8DDo0ewD1KN+ lkUAn24W6StN2aD4CGTOXbu6oV1L3Jau =pFL8 -----END PGP SIGNATURE----- --Qxx1br4bt0+wmkIi--