On Aug 5, 2009, at 11:59 AM, Tom Lane wrote:
Robert Haas <robertmh...@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 deci...@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
