Bulk Revoke of Permissions for Specific Group/User role

UPDATE Turns out there is a simpler way of getting rid of roles that have explicit permissions to objects as Tom Lane pointed out in the comments.

DROP OWNED BY some_role;

Will drop the permissions to objects a user has rights to even if they don't own the objects. Of course this needs to be applied with caution since it will drop tables and other things you don't want necessarily dropped. So it is best to first run a:
REASSIGN OWNED BY some_role TO new_role; And then run the DROP OWNED BY.
The REASSIGN OWNED BY which is what we did originally is not sufficient since it doesn't drop the permissions or reassign them as we assumed it would. This is noted in the docs.
And then you will be allowed to DROP ROLE some_role

One of the things that is still tricky in PostgreSQL is permission management. Even though 9.0 brought us default privileges and the like, these permissions aren't retroactive so still a pain to deal with if you already have objects defined in your database.

One of the annoyances we come across with is deleting roles. Lets say you have a role and it has explicit permissions to an object. PostgreSQL won't allow you to delete this role if it owns objects or has explicit permissions to objects. In order to delete it seems you have to go in and clear out all those permissions. To help with that -- we wrote a quickie script that will generate a script to revoke all permissions on objects for a specific role. It looks like this:

WITH r AS (SELECT 'role_to_revoke'::text As param_role_name)
SELECT DISTINCT 'REVOKE ALL ON TABLE ' || table_schema || '.' || table_name || ' FROM ' || r.param_role_name || ';' As sql
FROM information_schema.table_privileges CROSS JOIN r
WHERE grantee ~* r.param_role_name
SELECT DISTINCT 'REVOKE ALL ON FUNCTION ' || routine_schema || '.' || routine_name || '(' 
    ||  pg_get_function_identity_arguments(
        (regexp_matches(specific_name, E'.*\_([0-9]+)'))[1]::oid) || ') FROM ' || r.param_role_name || ';' As sql
FROM information_schema.routine_privileges CROSS JOIN r
WHERE grantee ~* r.param_role_name
SELECT 'REVOKE ALL ON SEQUENCE ' || sequence_schema || '.' || sequence_name || ' FROM ' || r.param_role_name || ';' As sql
FROM information_schema.sequences CROSS JOIN r ;

Our script if we run by replacing 'role_to_revoke' with 'public' will look something like this:

--- output --
REVOKE ALL ON TABLE pg_catalog.pg_tables FROM public;
REVOKE ALL ON FUNCTION public.st_crosses(geometry, geometry) FROM public;
REVOKE ALL ON FUNCTION public.st_addband(torast raster, fromrast raster, fromband integer) FROM public;
REVOKE ALL ON SEQUENCE topology.topology_id_seq FROM public;

Some items in this script probably look cryptic to the untrained or unknowing eye: