Purpose

For auditing or administration purposes, Confluence administrators may find it useful to see all descendants of a particular page in Confluence and any restrictions placed on any of those descendants.
This is helpful when examining a large space, to see which restrictions are placed in each page. This can be done via a SQL query.

Solution

To run the query provided below you'll need the page ID from the parent page, to do so you can go to the parent page in the UI, click the ellipsis icon '...' in the top right corner and go to Page Information, then you will see the "pageId" value in the browser address bar.

Before running the query below against the Confluence database, replace <parent_pageid> with the page ID value.

SELECT c.contentid,
       c.title,
       um.username AS USER,
       cp.groupname AS GROUP,
       cp.cp_type
FROM content c
FULL OUTER JOIN confancestors ca ON ca.descendentid = c.contentid
FULL OUTER JOIN content_perm_set cps ON c.contentid = cps.content_id
FULL OUTER JOIN content_perm cp ON cps.id = cp.cps_id
FULL OUTER JOIN user_mapping um ON um.user_key = cp.username
WHERE ca.ancestorid = '<parent_pageid>'
    AND c.content_status = 'current'
    AND c.prevver IS NULL;

 The above query was only tested in PostreSQL and might need syntax changes depending on your database.

Related pages

• Permissions and restrictions
• How to List All Pages Restricted to a Specific Group
• How to Get a Listing or Count of Child Pages Under a Parent Page