Purpose

Users are listed in the People Directory who do not have Confluence Use permission, and should not appear in the People Directory.

Solution

The following has been tested in PostgreSQL, and may need to be modified for other database types.

1. Stop Confluence

2. Run test query to see which results will be deleted:

   SELECT DISTINCT lower_username FROM user_mapping WHERE lower_username not in (
   SELECT DISTINCT u.lower_user_name
   FROM cwd_user u
   JOIN cwd_membership m ON u.id = child_user_id
   JOIN cwd_group g ON m.parent_id = g.id
   JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
   JOIN cwd_directory d on u.directory_id = d.id
   WHERE PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T');

3. Run query to delete users from People Directory:

   DELETE
   FROM CONTENT
   WHERE contenttype = 'USERINFO' AND username IN 
   (SELECT DISTINCT user_key FROM user_mapping WHERE lower_username not in (
   SELECT DISTINCT u.lower_user_name
   FROM cwd_user u
   JOIN cwd_membership m ON u.id = child_user_id
   JOIN cwd_group g ON m.parent_id = g.id
   JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
   JOIN cwd_directory d on u.directory_id = d.id
   WHERE PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T'));

4. Start Confluence
5. Reindex Confluence
6. Check People Directory to confirm users are removed.