How to extract restrictions data ?

[Peter Jakobsen]

Hi community
Our customer has a huge Confluence space which needs to be made searchable again. The root cause for this is unknown as of now. Perhaps a corrupted database?

Currently all pages in the specific space cannot be found in a search by a user. A re-indexing of the space will fix this. This has been tested on a small space. However this cannot be completed due to the existence of various page restrictions on the space. These are custom restrictions setup individually for each page.

The solution roadmap to fix this, is so far:

1. Backup Confluence DB
2. Extract (backup) restrictions
3. Remove all restrictions
4. Perform a re-indexing of space
5. Import (restore) restrictions
6. Verification

We need help on bullet 2. - on how to backup/extract "restrictions" data before we can re-index the pages to make them searchable again.
Is it possible to extract "restrictions" data via a ScriptRunner script? So far I have not been able to find this a viable route.
Instead I have tried understand the Confluence datamodel and made som DB sql commands to extract the "restrictions" data manually (for later import #5).

The 2 sql command I have made is based on the "List pages restricted on a specific space" found in this article
Instead of extracting "pages" I have reversed the SQL to give me data from "CONTENT_PERM" and "CONTENT_PERM_SET" in relation to specific "space".

However, since I am new to Confluence datamodel, I am unsure if I am missing something vital. Hope you can help here :-) 

So far the SQL looks like this. The goal is to extract all relevant "restrictions" data for later import.

1. select cps.*
   from CONTENT_PERM_SET cps
   JOIN CONTENT c ON c.CONTENTID = cps.CONTENT_ID
   JOIN SPACES s ON c.SPACEID = s.SPACEID
   WHERE s.SPACEKEY = '<space key>';
   
   
2. SELECT cp.*
   FROM SPACES s
   JOIN CONTENT c ON s.SPACEID = c.SPACEID
   JOIN CONTENT_PERM_SET cps ON c.CONTENTID = cps.CONTENT_ID
   JOIN CONTENT_PERM cp ON cps.ID = cp.CPS_ID
   WHERE s.SPACEKEY = '<space key>';

These are based on the above mentioned article ("List pages restricted on a specific space")

SELECT c.contentid, c.title, s.spacekey, cps.cont_perm_type
FROM SPACES s
JOIN CONTENT c ON s.spaceid = c.spaceid
JOIN CONTENT_PERM_SET cps ON c.contentid = cps.content_id
JOIN CONTENT_PERM cp ON cps.id = cp.cps_id
WHERE s.spacekey = '<space_key>';

 
Kind regards
Peter Jakobsen