I have a big database with 40 schemas and plenty of tables inside each one. Some tables have a GRANT SELECT ON TABLE schema1.table1 TO user1grp. I no longer need user1grp group role.

If I delete this group role, are the related GRANT going to be removed from related tables?

Or do I need to remove the GRANT on each table before deleting the group role? If this is the good process, is there a way to list all tables related to this group role?


The privileges are not automatically removed. Let's consult the documentation, as always:

dropping a role is often not just a matter of a quick DROP ROLE. Any objects owned by the role must first be dropped or reassigned to other owners; and any permissions granted to the role must be revoked.

So, if the role owns any objects, you have to either transfer the ownership to someone else, or drop the object. These can be done by

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;

The important fact is that DROP OWNED does not only the dropping:

DROP OWNED also takes care of removing any privileges granted to the target role for objects that do not belong to it. Because REASSIGN OWNED does not touch such objects, it's typically necessary to run both REASSIGN OWNED and DROP OWNED (in that order!) to fully remove the dependencies of a role to be dropped.

A limitation you have to observe is that DROP OWNED works only on the current database, and not on other databases that might also contain objects with privileges granted to the to-be-deleted role.

  • That is not the same story, effectively... Thanks for these precisions @dezso! – wiltomap Mar 11 '16 at 15:28

The goal of a role is that you grant all grants that you need to it. After that you only need to grant this role to a user (or other role). If you remove the role then all grants to that role are 'revoked' so you need to do nothing else.

The information on the roles can be found in information_schema.role_table_grants:

FROM   information_schema.role_table_grants
WHERE  grantee='user1grp';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.