Re: GRANT ON ALL IN schema

On Aug 5, 2009, at 11:59 AM, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828