Re: GRANT ON ALL IN schema

On Aug 5, 2009, at 11:59 AM, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> ... bulk-grant could be based on object type,
>> object name (with wildcard or regexp pattern), schema membership, or
>> maybe other things, and I think that would be quite useful if we can
>> figure out how to make it clean and elegant.
>
> Yeah.  In the end you can always write a plpgsql function that filters
> on anything at all.  The trick is to pick some useful subset of
> functionality that can be exposed in a less messy way.
>
> Or maybe we are going at this the wrong way?  Would it be better to  
> try
> harder to support the write-a-plpgsql-function approach?  I don't  
> think
> the documentation even mentions that approach, let alone provides any
> concrete examples.  It might be interesting to document it and see if
> there are any simple things we could do to file off rough edges in  
> doing
> grants that way, rather than implementing what must ultimately be a
> limited solution directly in GRANT.

I'm not sure if this is what you were thinking, but something I've  
added to all our databases is a simple exec function (see below).  
This makes it a lot less painful to perform arbitrary operations.  
Perhaps we should add something similar to the core database? On a  
related note, I also have tools.raise(level text, messsage text) that  
allows you to perform a plpgsql RAISE command from sql; I've found  
that to be very useful in scripts to allow for raising an exception.

In this specific case, I think there's enough demand to warrant a  
built-in mechanism for granting, but if something like exec() is  
built-in then the bar isn't as high for what the built-in GRANT  
mechanism needs to handle.

CREATE OR REPLACE FUNCTION tools.exec(    sql text    , echo boolean
) RETURNS text LANGUAGE plpgsql AS $exec$
BEGIN    RAISE DEBUG 'Executing dynamic sql: %', sql;    EXECUTE sql;
    IF echo THEN        RETURN sql;    ELSE        RETURN NULL;    END IF;
END;
$exec$;

The echo parameter is sometimes useful in scripts so you have some  
idea what's going on; but it should be optional.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828