Eric Jain wrote:
>
> I came across some PL/pgSQL procedures for doing batch GRANTs, but
> they appear to be outdated (i.e. don't work with 8.3) and are "run at
> your own risk". There was also mention that pgAdmin had a function to
> batch GRANTs, but I couldn't find any such thing in the current
> version...
Must be pretty old if they don't work in 8.3. I've attached code for a
plpgsql exec_all function that matches table-names etc per schema.
Should be obvious enough how to wrap it for a grant_all or revoke_all
function. It assumes you've got a "util" schema to put it in.
--
Richard Huxton
Archonet Ltd
--
-- This code is being placed in the public domain - R.Huxton 2008
--
CREATE OR REPLACE FUNCTION util.exec_all(objtype name, schname name, objname name, cmd text) RETURNS text
AS $$
DECLARE
obj_qry TEXT := '';
r RECORD;
sql TEXT;
out TEXT;
BEGIN
out := cmd || ': ';
-- Tables includes views
IF objtype = 'tables' THEN
obj_qry := $a$SELECT table_name AS nm FROM information_schema.tables WHERE table_schema=$a$ ||
quote_literal(schname)|| $a$ AND table_name LIKE $a$ || quote_literal(objname);
ELSIF objtype = 'tables-base' THEN
obj_qry := $a$SELECT table_name AS nm FROM information_schema.tables WHERE table_type='BASE TABLE' AND
table_schema=$a$|| quote_literal(schname) || $a$ AND table_name LIKE $a$ || quote_literal(objname);
ELSIF objtype = 'views' THEN
obj_qry := $a$SELECT table_name AS nm FROM information_schema.tables WHERE table_type='VIEW' AND
table_schema=$a$|| quote_literal(schname) || $a$ AND table_name LIKE $a$ || quote_literal(objname);
ELSIF objtype = 'sequences' THEN
obj_qry := $a$SELECT sequence_name AS nm FROM information_schema.sequences WHERE sequence_schema=$a$ ||
quote_literal(schname)|| $a$ AND sequence_name LIKE $a$ || quote_literal(objname);
END IF;
FOR r IN EXECUTE obj_qry LOOP
sql := regexp_replace( cmd, E'\\?\\?', quote_ident(r.nm), 'g' );
sql := regexp_replace( sql, E'\\?', quote_ident(schname) || '.' || quote_ident(r.nm), 'g' );
EXECUTE sql;
out := out || r.nm || ' ';
END LOOP;
RETURN out;
END;
$$
LANGUAGE plpgsql;
COMMENT ON FUNCTION util.exec_all(name,name,name,text) IS $$
util.exec_all(OBJECT-TYPE, SCHEMA, OBJECT-NAME, COMMAND-TEXT)
Runs COMMAND-TEXT over the database objects matched by the wildcarded pattern in OBJECT-NAME.
OBJECT-TYPE: tables | tables-base | views | sequences
SCHEMA: name of a single schema
OBJECT-NAME: wildcarded name (e.g. 'sales_%')
COMMAND-TEXT: SQL statement(s) to execute, with object-names replaced by '?' or '??'
?? is replaced by the object-name
? is replaced by the full <schema-name>.<object-name>
EXAMPLE
=======
util.exec_all('tables', 'reports', '%', 'GRANT SELECT ON ? TO someuser');
$$;
