In PostgreSQL 9.0+ you would do the following:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA MY_SCHEMA TO MY_GROUP;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA MY_SCHEMA TO MY_GROUP;
If you want to enable this for newly created relations too, then set the default permissions:
ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
GRANT ALL PRIVILEGES ON TABLES TO MY_GROUP;
ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
GRANT ALL PRIVILEGES ON SEQUENCES TO MY_GROUP;
However, seeing that you use 8.1 you have to code it yourself:
CREATE FUNCTION grant_all_in_schema (schname name, grant_to name) RETURNS integer AS $$
DECLARE
rel RECORD;
BEGIN
FOR rel IN
SELECT c.relname
FROM pg_class c
JOIN pg_namespace s ON c.namespace = s.oid
WHERE s.nspname = schname
LOOP
EXECUTE 'GRANT ALL PRIVILEGES ON ' || quote_ident(schname) || '.' || rel.relname || ' TO ' || quote_ident(grant_to);
END LOOP;
RETURN 1;
END; $$ LANGUAGE plpgsql STRICT;
REVOKE ALL ON FUNCTION grant_all_in_schema(name, name) FROM PUBLIC;
This will set the privileges on all relations: tables, views, indexes, sequences, etc. If you want to restrict that, filter on pg_class.relkind
. See the pg_class docs for details.
You should run this function as superuser and as regular as your application requires. An option would be to package this in a cron job that executes every day or every hour.