Hi guys,

The latest thing we've noticed in the IRC channel and the phpPgAdmin 
lists is that people want to be able to grant on all objects in a 
database, etc:

grant select on all tables to blah;

or even:

grant rule on all views in schema myschema to blah;

This seriously is asked every other day on #postgresql, followed by us 
saying they have to write a stored proc to do it, followed by them 
saying that that's crap...

The obvious trick here is what do do if you aren't a grantor for that 
privilege.

I confess I find this an annoying omission also...

Chris


Sun, 23 May 2004 12:00:29 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Latest requests from IRC
On Sun, May 23, 2004 at 12:00:29 +0800,
  Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> Hi guys,
> 
> The latest thing we've noticed in the IRC channel and the phpPgAdmin 
> lists is that people want to be able to grant on all objects in a 
> database, etc:

The right way to do this is to make sure there is a group that has access
to "everything" and just add people to the group.

Of, course it might be nice if there was a contrib function that made
such a group in case you have gotten pretty far without doing any
grants.

Sun, 23 May 2004 10:57:46 -0500
Bruno Wolff III <bruno@wolff.to>
Re: Latest requests from IRC
Bruno Wolff III <bruno@wolff.to> writes:
>   Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
>> ... people want to be able to grant on all objects in a 
>> database, etc:

> The right way to do this is to make sure there is a group that has access
> to "everything" and just add people to the group.

Doesn't seem like that magically solves the problem, though.  You still
have lots of pain involved in granting privs on everything to that
group.

I don't have any fundamental problem with something like "GRANT SELECT
ON TABLE * TO foo", seeing as how we already allow grants on multiple
tables.  But we'd have to be very careful about how the scope of the *
wildcard is defined.  For instance, if a superuser does it, does it
really grant privs on *all* tables?  I'd hope that the system catalogs,
at least, are not implicitly included in the wildcard scope.  For lesser
mortals there is also the question of whether to error out or just
ignore tables that you don't have privileges for.

Would it make sense to restrict the wildcard to a particular schema, viz
	GRANT SELECT ON TABLE myschema.* TO foo
This would neatly solve the question of how to exclude the system
catalogs, and in most scenarios where people are wishing for this,
I bet they've put all the objects in one schema anyway.

			regards, tom lane

Sun, 23 May 2004 12:18:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Latest requests from IRC 
> Bruno Wolff III <bruno@wolff.to> writes:
>>   Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
>>> ... people want to be able to grant on all objects in a
>>> database, etc:

For things like this I use simple,
but super-powerful eval function:

CREATE OR REPLACE FUNCTION eval(text)
  RETURNS int4
  VOLATILE
  LANGUAGE 'plpgsql'
  SECURITY INVOKER
  AS 'DECLARE
  body ALIAS FOR $1;
  result INT;
BEGIN
EXECUTE body;
GET DIAGNOSTICS result = ROW_COUNT;
RETURN result;
END;
';

Then you say something like:

SELECT eval('GRANT SELECT ON TABLE '||TABLE_NAME||' TO PUBLIC')
FROM INFORMATION_SCHEMA.TABLES
WHERE schema_name=current_schema()
AND type_type='BASE TABLE';

Also works great for other similar operations, such as
renaming, changing owners, etc... anything you can
generate with SQL, which is quite a lot, really.

Is this considered ok or extreme abuse?



Sun, 23 May 2004 21:40:56 +0200 (CEST)
"Ziga Kranjec" <ziga@ljudmila.org>
Re: eval function

Added to TODO:

	* Allow GRANT/REVOKE permissions to be given to all schema
	  objects with one command


---------------------------------------------------------------------------

Tom Lane wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> >   Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> >> ... people want to be able to grant on all objects in a 
> >> database, etc:
> 
> > The right way to do this is to make sure there is a group that has access
> > to "everything" and just add people to the group.
> 
> Doesn't seem like that magically solves the problem, though.  You still
> have lots of pain involved in granting privs on everything to that
> group.
> 
> I don't have any fundamental problem with something like "GRANT SELECT
> ON TABLE * TO foo", seeing as how we already allow grants on multiple
> tables.  But we'd have to be very careful about how the scope of the *
> wildcard is defined.  For instance, if a superuser does it, does it
> really grant privs on *all* tables?  I'd hope that the system catalogs,
> at least, are not implicitly included in the wildcard scope.  For lesser
> mortals there is also the question of whether to error out or just
> ignore tables that you don't have privileges for.
> 
> Would it make sense to restrict the wildcard to a particular schema, viz
> 	GRANT SELECT ON TABLE myschema.* TO foo
> This would neatly solve the question of how to exclude the system
> catalogs, and in most scenarios where people are wishing for this,
> I bet they've put all the objects in one schema anyway.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 23 May 2004 23:22:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Latest requests from IRC

Just my two pence contribution:

> 	* Allow GRANT/REVOKE permissions to be given to all schema
> 	  objects with one command
>
> > 	GRANT SELECT ON TABLE myschema.* TO foo

As for the style of the syntax, maybe one could consider to reuse
already available sql regexpr rather than import shell-like regexpr?

GRANT SELECT ON TABLE 'myschema.%' TO calvin;

Also, there is no reason why the schema themselves could not been
fixed similarly:

REVOKE ALL FROM SCHEMA 'abc%' FROM hobbes;

On the other hand, having actual sql regexpr may make a potential
implementation harder wrt just handling a 'myschema.*' special case.


Have a nice day,

-- 
Fabien.

Mon, 24 May 2004 09:06:33 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: Latest requests from IRC