How to fetch Confluence's external directory credentials from the database
Platform Notice: Data Center Only - This article only applies to Atlassian products 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
When connecting your Confluence instance to an external Atlassian application for user management, such as Jira or Crowd, you need to define an application name and application password that will be used by Confluence to authenticate to the user management server. After this configuration is successfully completed, the application password that was used is sanitized in the UI for safety reasons.
There are scenarios, however, where an instance administrator might lose track of these credentials due to poor documentation or when inheriting an instance that was previously managed by other teams.
In these situations, the simplest solution is to create a new application name and password for both Confluence and the target application used for user management. Then, update the settings on both applications according to the instructions provided on Connecting to Crowd or Jira for User Management.
However, if an administrator still needs to retrieve those credentials for documenting purposes, this can be achieved through SQL queries.
Solution
🚩 Please ensure that you're familiar with SQL syntax and its effects before directly interacting with your instance's database. Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Jira's and/or Crowd's external directory information are stored in two tables within Confluence's database, and the only way to retrieve its credentials is by directly running queries to these tables and compiling their data.
The tables that hold such information are:
cwd_directory
cwd_directory_attribute
By running the following query, we can retrieve the application.name and application.password stored within Confluence:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
dir.directory_name,
app_name.attribute_value AS application_name,
app_password.attribute_value AS application_password
FROM
cwd_directory AS dir
LEFT JOIN
cwd_directory_attribute AS app_name
ON dir.id = app_name.directory_id AND app_name.attribute_name = 'application.name'
LEFT JOIN
cwd_directory_attribute AS app_password
ON dir.id = app_password.directory_id AND app_password.attribute_name = 'application.password';🚩 The above query was tested on a PostgreSQL database so you might need to adjust its syntax if running on a different database server.
Example
A test Confluence instance is linked to both Jira and Crowd for user management:
Jira's user directory was configured with the following credentials:
Application Name: jira
Application Password: jira
Crowd's user directory was configured with the following credentials:
Application Name: crowddirectory
Application Password: crowddirectory
When running the above query to that instance's database, we're then able to fetch these exact credentials:
Was this helpful?