How to Migrate Local Group Memberships Between Directories
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
This KB will help you migrate your group memberships between any combination of the following directory types:
This process requires the use of direct database manipulation and is not part of Confluence's intended functionality. As such, this process is not covered under the Atlassian Support Offerings and the information on this page is provided as-is. It should be thoroughly tested in a development or staging environment before implementing any changes in your production instance.
Do note that to complete the steps mentioned in this KB, you would need to use the 3rd party plugin Confluence Command Line Interface (CLI) from Appfire.
Solution
Procedure
Step 1: Create a CSV of user-group relationships
First, you will need to create a CSV by exporting the username and group pairing of all users in your directory. You will need the directory ID for the source users and groups. To find out your directory ID, run the following query:
SELECT id, directory_name, description, directory_type
FROM cwd_directory;MySQL and Postgres queries are provided below that will create the proper output for the CSV file.
MySQL
SELECT cu.lower_user_name, cg.lower_group_name
FROM cwd_user cu
JOIN cwd_membership cm ON cu.id=cm.child_user_id
JOIN cwd_group cg ON cm.parent_id=cg.id
WHERE cu.directory_id=<souce_directory_id> AND cg.directory_id=<source_directory_id>
INTO OUTFILE '/tmp/outfile.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n';PostgreSQL
COPY (
SELECT cu.lower_user_name, cg.lower_group_name
FROM cwd_user cu
JOIN cwd_membership cm
ON cu.id=cm.child_user_id
JOIN cwd_group cg
ON cm.parent_id=cg.id
WHERE cu.directory_id=<souce_directory_id> AND cg.directory_id=<souce_directory_id>
)
TO '/tmp/user-groups.csv'
WITH CSV;If you are migrating from and either of the LDAP-backed directories with local and external groups, and you do not want your LDAP groups added to your internal directory, add cg.local='T' to your WHERE clause. This is not necessary if your source directory is a standard internal directory.
The output of these queries should create a csv with content similar to the following:
admin,confluence-administrators
admin,confluence-usersStep 2: Create your new directory and add users
The most common directories to migrate local memberships to are LDAP Directories with Local Groups or an Internal Directory with LDAP Authentication, but you can also use this method for an additional Internal Directory. Once you have created your desired directory, you will need to meet the following prerequisites:
The new directory must be moved to the top of the directory list in Confluence Admin >> User Directories.
All of the usernames that need their group memberships migrated need to already exist in the new directory. In an LDAP directory that supports syncing, this will happen once you create the directory. For an internal directory, you will need to populate the directory manually. This can be accomplished via the CLI with the
addUserWithFileaction. Each line in your CSV will need to be in the format: user,password,email,fullNameYou can alter the SQL statements above so that the select portion of the query pulls the necessary columns to create an addUser CSV. For example,
SELECT lower_user_name, 'password', email_address, display_name FROM cwd_user JOIN..., replacing password with any random string for a temporary password. Your users can then use the password reset option before logging into Confluence after the migration.
Step 3: Download and configure the Atlassian CLI Tool
First, you need to have downloaded and configured the Atlassian CLI Tool. Details on usage can be found on the Confluence CLI wiki page. The example script in step 4 assumes that you have added your authentication credentials to the confluence.sh or confluence.bat.
Step 4: Use the CLI to add users to their groups
The action addUserToGroupWithFile was deprecated based on the release notes of version 7.0 CLI. All newer versions will not have this action.
The replacement is to use runFromCsv together with addUserToGroup actions. runFromCsv is now the standard way of doing these types of actions based on the Appfire documentation.
The CLI tool has a built in action that will automatically add users to groups using a CSV. Run the following command from the CLI directory root directory:
./confluence.sh --action addUserToGroupWithFile --file "/Users/alaskowski/Downloads/test.csv" --autoGroupFor Windows users, replace ./confluence.sh with confluence.bat. Once the script has completed, you can move the new directory to any ordering you prefer. If you are unsure of how ordering your directories affects authentication and permissions, there is more information available in our doc on Managing Multiple Directories.
Was this helpful?