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
UNION ALL
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
UNION ALL
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:
information_schema goodness. As we've mentioned before the information_schema is a more or less ANSI standard schema you will find on other relational databases that stores all sorts of meta-data such as the names of tables, views, functions and in addition the priviledges for each. It unforntatunely is not complete but we use it wherever we can just because it works on multiple databases we work with so limits the number of things we need to remember.
routine information_schema views. Tables with routine in the name provide information about functions and stored procedures. The routine_privileges view lists all the permissions for each stored procedure/function. The grantee being the role who has the permission and grantor the role that granted the permission. Sadly this talbe does not exist in all databases supporting information_schema. MySQL has it for example, but SQL Server in any version I can think of does not have it though it does have a routines view.
What the hell is this: pg_get_function_identity_arguments( (regexp_matches(specific_name, E'.*\_([0-9]+)'))[1]::oid). Okay this is us cheating a bit. In the routine_privileges view, there is a column called specific_name which is a unique across the database name of a function. So this means that overloaded functions will all have the same routine_name but different specific_name. PostgreSQL formulates the specific_name by appending _ followed by the procedure object identifier (oid) of the function. The PostgreSQL function pg_get_function_identity_arguments given the object identifier (oid) of a procedure, will return a comma separated list of the argument types that are inputs to the function. Our regular expression pulls this function oid out and casts it back to an oid so it can be used by this function.
WITH -- this script only works in 8.4 and above since it uses Common Table Expressions (CTE). One of my favorite uses for CTE is for storing variables to be used in later queries. It's an SQL idiom we use often to emulate procedural variable declaration. So in this the r CTE is used in the output statement to filter the records.