Query to Check Groups Actively Used in Space Permissions and Page Restrictions
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
LDAP/AD connections can often bring in more groups than intended and it is difficult to scope what groups are currently being used in Confluence. The following query can be used to determine the group and space for all groups used in Confluence.
WITH combined AS (
SELECT DISTINCT GROUPNAME AS name, SPACENAME, 'page' as label
FROM CONTENT_PERM
INNER JOIN CONTENT_PERM_SET on CONTENT_PERM.CPS_ID = CONTENT_PERM_SET.ID
INNER JOIN CONTENT on CONTENT_PERM_SET.CONTENT_ID = CONTENT.CONTENTID
INNER JOIN SPACES on CONTENT.SPACEID = SPACES.SPACEID
WHERE GROUPNAME IS NOT NULL AND GROUPNAME NOT IN ('confluence-users','confluence-administrators')
UNION
SELECT DISTINCT PERMGROUPNAME as name, SPACENAME, 'space' as label
FROM SPACEPERMISSIONS
INNER JOIN SPACES on SPACEPERMISSIONS.SPACEID = SPACES.SPACEID
WHERE PERMGROUPNAME IS NOT NULL AND PERMGROUPNAME NOT IN ('confluence-users','confluence-administrators')
)
SELECT DISTINCT name, SPACENAME, label
FROM combined
WHERE name IS NOT NULL;
The "label" column will list 'space' or 'page' to denote if the entry applies to a space permission or page restriction.
This query has been tested against SQL Server and PostgreSQL. Other databases may require altered syntax.