Purpose

The Confluence UI does not currently provide a way to bulk delete groups.  If a customer has mistakenly created 100s of groups due to a mistake in configuring their LDAP filter, there is no easy way to remove the extraneous groups.  SQL commands can be used to remove the unwanted groups, provided you take proper care.

When making the following actions...

• It's important to shut down Confluence while deleting the groups to prevent your users from encountering errors.
• A restart is also necessary to reset in-memory caches of users and groups.
• If you use LDAP or Crowd for user management the groups will be re-created on the next sync unless you modify your LDAP filter and/or groups in Crowd.

• Our warning about testing SQL queries on a test installation is particularly important here. Once you delete groups or group memberships they are irrevocably gone.

Diagnosis

• You have 50+ groups in Confluence that interfere with your ability to manage group membership. You are unable to remove the groups via the UI or by modifying the LDAP filter. 

Workaround

Before proceeding with the instructions below, check for usage of these groups in the Confluence UI (http://<baseurl>/admin/users/browsegroups.action) to be sure that removing these groups will not break your instance, and then use the queries provided below to make additional checks. 

IMPORTANT:  The user and group names in the queries are case sensitive.  These queries have been tested in Postgres and MySQL. You may need to modify the syntax to work for other supported databases. 

Section 1: Check to see if the group(s) is safe to delete: 

NOTE: A group that is safe to delete has no members; and is not referenced in content permissions, space permissions or global permissions

1. Run the SQL script below to find any empty groups: 

   SELECT id, lower_group_name FROM cwd_group
   WHERE cwd_group.id NOT IN
   (SELECT cwd_membership.parent_id FROM cwd_membership);

   Note: The ids and group name will be used in subsequent scripts.

2. Run the SQL script below to confirm that the groups to be deleted do not contain any members:
   

   Select cwd_user.id, user_name
   FROM cwd_user INNER JOIN cwd_membership
   ON cwd_user.id = cwd_membership.child_user_id
   WHERE parent_id IN (id1, id2); 

   
   

3. Run the SQL script below to check if groups are used in content permissions:

   SELECT * FROM content_perm INNER JOIN cwd_group
   ON cwd_group.lower_group_name = content_perm.groupname
   WHERE cwd_group.lower_group_name IN ('group-to-delete', 'another-group-to-delete');

4. Run the SQL script below to check if groups are using in Space permissions:

   SELECT * FROM spacepermissions INNER JOIN cwd_group
   ON cwd_group.lower_group_name = spacepermissions.permgroupname
   WHERE cwd_group.lower_group_name IN ('group-to-delete', 'another-group-to-delete');

Section 2:  SQL scripts to remove groups

When you are certain it is safe to remove the groups run these scripts in order: 

1.  Remove group members, if any users were returned in Step 2 above: 

   DELETE FROM cwd_membership 
   WHERE parent_id IN (id1, id2)
   

2. Remove any content permissions associated with the groups, if any results were returned in Step 3 above: 

   DELETE FROM content_perm 
   WHERE groupname IN ('group-to-delete', 'another-group-to-delete');

3. Remove any space permissions associated with the groups, if any results were returned in Step 4 above: 

   DELETE FROM spacepermissions 
   WHERE permgroupname IN ('group-to-delete', 'another-group-to-delete');

4. Remove the groups:

   DELETE from cwd_membership WHERE parent_id IN (id1, id2);
   DELETE from cwd_group WHERE id IN (id1, id2);