Re: How to grant a privilege on all tables or views or both of a database to someone?

Andrew,

That's a nice solution. Was wondering if you think its feasible to do the
same, but as only one function with an additional parameter for relation
type ( table, view, sequence, all ).

Also, say I have tables owned by different users ( ie some tables owned by a
taskmanager process and others owned by web application ) and I only want to
issue grants on objects owned by one of these users?

Thanks,

Paul Ogden

-----Original Message-----
From: Andrew G. Hammond [mailto:drew(at)xyzzy(dot)dhs(dot)org]
Sent: Thursday, March 07, 2002 12:41
To: Paul Ogden
Cc: Eric Du; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] How to grant a privilege on all tables or views or
both of a database to someone?

On Thu, Mar 07, 2002 at 10:55:51AM -0800, Paul Ogden wrote:
> How to grant a privilege on all tables or views or both of a database to
> someone?Here's what I did, using psql:

Personally I solved this using plpgsql:

-- usage: SELECT grant_all('privilidge', 'user');
-- grants privilidge (SELECT, UPDATE, INSERT, DELETE or ALL) to the user
-- for all non-pg_ objects in the database (except indices).

DROP FUNCTION grant_all(text, text);
CREATE FUNCTION grant_all(text, text) RETURNS boolean AS '
DECLARE item RECORD;
priv ALIAS FOR $1;
user ALIAS FOR $2;
BEGIN
FOR item IN SELECT * FROM pg_class
WHERE relname !~ ''^pg_''
AND relkind <> ''i''
LOOP
EXECUTE ''GRANT '' || priv
|| '' ON '' || quote_ident(item.relname)
|| '' TO '' || quote_ident(user);
END LOOP;
RETURN ''t'';
END;' LANGUAGE 'plpgsql';

DROP FUNCTION grant_all_tables(text, text);
CREATE FUNCTION grant_all_tables(text, text) RETURNS boolean AS '
DECLARE item RECORD;
priv ALIAS FOR $1;
user ALIAS FOR $2;
BEGIN
FOR item IN SELECT * FROM pg_class
WHERE relname !~ ''^pg_''
AND relkind = ''r''
LOOP
EXECUTE ''GRANT '' || priv
|| '' ON '' || quote_ident(item.relname)
|| '' TO '' || quote_ident(user);
END LOOP;
RETURN ''t'';
END;' LANGUAGE 'plpgsql';

DROP FUNCTION grant_all_tables_and_views(text, text);
CREATE FUNCTION grant_all_tables_and_views(text, text) RETURNS boolean AS '
DECLARE item RECORD;
priv ALIAS FOR $1;
user ALIAS FOR $2;
BEGIN
FOR item IN SELECT * FROM pg_class
WHERE relname !~ ''^pg_''
AND relkind IN (''r'', ''v'')
LOOP
EXECUTE ''GRANT '' || priv
|| '' ON '' || quote_ident(item.relname)
|| '' TO '' || quote_ident(user);
END LOOP;
RETURN ''t'';
END;' LANGUAGE 'plpgsql';

--
Andrew G. Hammond mailto:drew(at)xyzzy(dot)dhs(dot)org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me