Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,293,373
Community Members
 
Community Events
165
Community Groups

How to extract restrictions data ?

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

0 answers

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

733 views 5 21
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you