DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=XsjRhPw8F7Rjj+/F37USVbmV0WfHTqa4WQGFW4aSIk95d+oxq0WvloAYIDrns2qvv9
	9M+2rgZc2U1PJgEPoLnIymJNjLa3HB4mWpLaf++fJHZ5RwRf0OQUVG8w4G0kLJXvVBgF
	/cy0SB5bDRMBt+wetoeW7iJNABjiuumycq03I=
MIME-Version: 1.0
In-Reply-To: <20090806152039.GO23840@tamriel.snowman.net>
References: <4A38A956.8080600@pjmodos.net>
	<a301bfd90907170254re7dd52es9fedb007376d8055@mail.gmail.com>
	<4A6059B4.5010004@pjmodos.net> <4A607997.3030305@pjmodos.net>
	<603c8f070907191628r6929055coe7627726a33ed143@mail.gmail.com>
	<a301bfd90907192312s17e1b16cy3bcd6e0876960c04@mail.gmail.com>
	<603c8f070908050932m19e7db65u9dab6d8001c5a237@mail.gmail.com>
	<20810.1249490458@sss.pgh.pa.us> <4A79B7E5.1020509@dunslane.net>
	<20090806152039.GO23840@tamriel.snowman.net>
Date: Thu, 6 Aug 2009 20:43:21 +0200
Message-ID: <162867790908061143u5764161codbc3381edd6a1888@mail.gmail.com>
Subject: Re: GRANT ON ALL IN schema
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Andrew Dunstan <andrew@dunslane.net>, Tom Lane <tgl@sss.pgh.pa.us>,
	Robert Haas <robertmhaas@gmail.com>,
	Nikhil Sontakke <nikhil.sontakke@enterprisedb.com>,
	Petr Jelinek <pjmodos@pjmodos.net>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

>
> \cmd grant select on * to user
>

when I wrote epsql I implemented \fetchall metastatement.
http://okbob.blogspot.com/2009/03/experimental-psql.html

It's should be used for GRANT

DECLARE x CURSOR FOR SELECT * FROM information_schema.tables ....
\fetchall x GRANT ALL ON :table_name TO public;

CLOSE x;

regards
Pavel Stehule

> Of course, our new psql * handling would mean this would grant
> select on everything in pg_catalog too, at least if we do the same as
> \d *
>
> I've got a simple perl script which does this, and I know others have
> pl/pgsql functions and the like for doing it. =C2=A0Adding that capabilit=
y to
> psql, if we can do it cleanly, would be nice.
>
> Adding some kind of 'run-multiple' stored proc is an interesting idea
> but I'm afraid the users this is really targetting aren't going to
> appreciate or understand something like:
>
> select
> =C2=A0cmd('grant select on '
> =C2=A0 || quote_ident(nspname)
> =C2=A0 || '.'
> =C2=A0 || quote_ident(relname)
> =C2=A0 || ' to public')
> from pg_class
> join pg_namespace on (pg_class.nspoid =3D pg_namespace.oid)
> where pg_namespace.nspname =3D 'myschema';
>
> Writing a function which takes something like:
> select grant('SELECT','myschema','*','role');
> or takes any kind of actual syntax like:
> select cmd('grant select on * to role');
> just strikes me as forcing users to use a function for the sake of it
> being a function.
>
> I really feel like we should be able to take a page from the unix book
> here and come up with some way to handle wildcards in certain
> statements, ala chmod.
>
> grant select on * to role;
> grant select on myschema.* to role;
> grant select on ab* to role;
>
> We don't currently allow "*" in GRANT syntax, and I strongly doubt that
> the SQL committee will some day allow it AND make it mean something
> different. =C2=A0If we're really that worried about it, we could have
> 'GRANTALL' or 'MGRANT' or something.
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0Thanks,
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Stephen
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkp69McACgkQrzgMPqB3kii3wQCfUweO4zEIjg2aLd84hxlYGgT1
> pqAAnAnT4FlJkIZ6K3YMjQaCOj3Hww7H
> =3DiUXy
> -----END PGP SIGNATURE-----
>
>