From: Pavel Stehule <pavel.stehule@gmail.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Nikhil Sontakke <nikhil.sontakke@enterprisedb.com>
Cc: Petr Jelinek <pjmodos@pjmodos.net>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: GRANT ON ALL IN schema
Date: Thu, 6 Aug 2009 20:43:21 +0200
Message-ID: <162867790908061143u5764161codbc3381edd6a1888@mail.gmail.com> (raw)
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>
>
> \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. Adding that capability 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
> cmd('grant select on '
> || quote_ident(nspname)
> || '.'
> || quote_ident(relname)
> || ' to public')
> from pg_class
> join pg_namespace on (pg_class.nspoid = pg_namespace.oid)
> where pg_namespace.nspname = '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. If we're really that worried about it, we could have
> 'GRANTALL' or 'MGRANT' or something.
>
> Thanks,
>
> Stephen
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkp69McACgkQrzgMPqB3kii3wQCfUweO4zEIjg2aLd84hxlYGgT1
> pqAAnAnT4FlJkIZ6K3YMjQaCOj3Hww7H
> =iUXy
> -----END PGP SIGNATURE-----
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pavel.stehule@gmail.com, sfrost@snowman.net, andrew@dunslane.net, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, nikhil.sontakke@enterprisedb.com, pjmodos@pjmodos.net
Subject: Re: GRANT ON ALL IN schema
In-Reply-To: <162867790908061143u5764161codbc3381edd6a1888@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox