Tom Lane wrote:
> Note that the above is overly simplistic --- it doesn't pay attention
> to schemas, for example.
These are what I use.
BEGIN;
CREATE SCHEMA util;
CREATE OR REPLACE FUNCTION util.exec_all(objtype name, schname name,
objname name, cmd TEXT) RETURNS text AS $$
DECLAREr RECORD;sql TEXT;out TEXT;
BEGINout := cmd || ': ';
-- Tables includes viewsIF objtype = 'tables' THEN FOR r IN SELECT table_name AS nm FROM information_schema.tables
WHERE table_schema=schname AND table_name LIKE objname LOOP sql := regexp_replace(cmd, E'\\?',
quote_ident(schname)|| '.' ||
quote_ident(r.nm)); EXECUTE sql; out := out || r.nm || ' '; END LOOP;ELSIF objtype = 'sequences' THEN
FOR r IN SELECT sequence_name AS nm FROM information_schema.sequences WHERE sequence_schema=schname AND
sequence_nameLIKE objname LOOP sql := regexp_replace(cmd, E'\\?', quote_ident(schname) || '.' ||
quote_ident(r.nm)); EXECUTE sql; out := out || r.nm || ' '; END LOOP;END IF;
RETURN out;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION util.grant_all(objtype name, schname name,
objname name, perms TEXT, roles TEXT) RETURNS text AS $$
DECLAREr RECORD;sql TEXT;g TEXT;
BEGINg := perms || ' ON ' || schname || '( ';
-- Tables includes viewsIF objtype = 'tables' THEN FOR r IN SELECT table_name AS nm FROM information_schema.tables
WHERE table_schema=schname AND table_name LIKE objname LOOP sql := 'GRANT ' || perms || ' ON TABLE ' ||
quote_ident(schname)||
'.' || quote_ident(r.nm) || ' TO ' || roles; -- RAISE NOTICE 'granting: %', sql; EXECUTE sql; g :=
g|| r.nm || ' '; END LOOP;ELSIF objtype = 'sequences' THEN FOR r IN SELECT sequence_name AS nm FROM
information_schema.sequences WHERE sequence_schema=schname AND sequence_name LIKE objname LOOP sql :=
'GRANT' || perms || ' ON SEQUENCE ' || quote_ident(schname)
|| '.' || quote_ident(r.nm) || ' TO ' || roles; -- RAISE NOTICE 'granting: %', sql; EXECUTE sql; g
:=g || r.nm || ' '; END LOOP;END IF;g := g || ') TO ' || roles;
RETURN g;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION util.grant_all(objtype name, schname name, objname
name, perms TEXT, roles TEXT) IS
$$Pattern-matching permission granter.objtype - (tables|sequences) where "tables" includes viewsschname - target
schema(NOT wildcarded)objname - wildcard (_%) name to matchperms - permissions to grantroles - comma-separated
listof roles to grant perms to.
$$;
CREATE OR REPLACE FUNCTION util.revoke_all(objtype name, schname name,
objname name, perms TEXT, roles TEXT) RETURNS text AS $$
DECLAREr RECORD;sql TEXT;g TEXT;
BEGINg := perms || ' ON ' || schname || '( ';
-- Tables includes viewsIF objtype = 'tables' THEN FOR r IN SELECT table_name AS nm FROM information_schema.tables
WHERE table_schema=schname AND table_name LIKE objname LOOP sql := 'REVOKE ' || perms || ' ON TABLE ' ||
quote_ident(schname)||
'.' || quote_ident(r.nm) || ' FROM ' || roles; -- RAISE NOTICE 'granting: %', sql; EXECUTE sql; g
:=g || r.nm || ' '; END LOOP;ELSIF objtype = 'sequences' THEN FOR r IN SELECT sequence_name AS nm FROM
information_schema.sequences WHERE sequence_schema=schname AND sequence_name LIKE objname LOOP sql :=
'REVOKE' || perms || ' ON SEQUENCE ' || quote_ident(schname)
|| '.' || quote_ident(r.nm) || ' FROM ' || roles; -- RAISE NOTICE 'granting: %', sql; EXECUTE sql;
g:= g || r.nm || ' '; END LOOP;END IF;g := g || ') TO ' || roles;
RETURN g;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION util.revoke_all(objtype name, schname name, objname
name, perms TEXT, roles TEXT) IS
$$Pattern-matching permission revoker. See grant_all(...) for details.
$$;
CREATE OR REPLACE FUNCTION util.drop_all_roles(pattern name) RETURNS
TEXT AS $$
DECLAREr RECORD;sql TEXT;res TEXT;
BEGINres := 'Dropped: ';FOR r IN SELECT rolname FROM pg_roles WHERE rolname LIKE patternLOOP sql := 'DROP ROLE ' ||
quote_ident(r.rolname); res := res || r.rolname || ' '; EXECUTE sql;END LOOP;res := substr(res, 1,
length(res)-1);
RETURN res;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION util.drop_all_roles(name) IS
$$Drop all roles matching the supplied pattern.
$$;
COMMIT;
-- Richard Huxton Archonet Ltd