How to Regain Confluence Admin Access via Database - Emergency Guide

Consider this scenario: It's Monday morning, and a team needs to add a new member to a crucial Confluence space. The problem? The only admin has just started a two-week vacation completely off-grid. For anyone in this situation, panic might set in... until they remember having access to the server where Confluence is running. This hypothetical scenario illustrates when the following technical solution might be necessary.

What You Need First

Before you start, make sure you have:

• Access to the server where Confluence's database runs.
• The username and password to connect to the Confluence database. (Hint: Check the confluence.cfg.xml file!)
• The Confluence username you want to make an admin.

Step 1: Find Your Confluence Database Details

Confluence stores its database connection info in a file called confluence.cfg.xml. This file is usually in the Confluence 'home' directory on your server (often something like /var/atlassian/application-data/confluence).

Find the file:

If you know the Confluence home directory path, you can use a command like this (replace /path/to/confluence/home with the actual path):

find /path/to/confluence/home -name \"confluence.cfg.xml\"

If you're not sure where the Confluence home directory is, try searching from the root directory (this might take a while) or navigate to a likely parent directory (like /var/atlassian or /opt) and search from there:

# Navigate to a potential parent directory first, then run:
find . -name \"confluence.cfg.xml\"

This command searches the current directory (.) and all its subdirectories for the file.

Read the file:

Once you find it, open confluence.cfg.xml. Look for lines like these:

<property name=\"hibernate.connection.url\">jdbc:postgresql://your-db-host:5432/confluence</property>
<property name=\"hibernate.connection.username\">confluenceuser</property>
<property name=\"hibernate.connection.password\">yourpassword</property>

These lines tell you the database type (like PostgreSQL or MySQL), server address (your-db-host), database name, username, and password you need to connect.

Step 2: Connect to the Database

Now, use the details you found to log into the database.

If it's PostgreSQL:

psql -h your-db-host -U confluenceuser -d confluence

If it's MySQL:

mysql -h your-db-host -u confluenceuser -p confluence

Enter the password when asked.

Step 3: Get the User ID and Admin Group ID

You need two pieces of information from the database: the ID for the user and the ID for the admin group.

Find the User's ID (user_key):

Run this SQL command. Replace 'your-username' with the actual Confluence username you want to make an admin:

SELECT user_key FROM cwd_user WHERE user_name = 'your-username';

Write down the user_key it gives you (it will be a number).

Find the Admin Group's ID:

Run this SQL command:

SELECT id FROM cwd_group WHERE group_name = 'confluence-administrators';

Write down the id it gives you (this is the admin group ID).

Step 4: Add the User to the Admin Group

This is the crucial step where you link the user to the admin group.

Find the Next Available ID for Link:

Run this to see the highest current ID in the membership table:

SELECT MAX(id) FROM cwd_membership;

Take the number it gives you and add 1. For example, if it shows 1000, you'll use 1001. This will be the unique ID for the new link.

Create the Link:

Run the following `INSERT` command. **Carefully replace** `NEXT_MEMBERSHIP_ID` with the ID you just calculated (e.g., `1001`), `ADMIN_GROUP_ID` with the admin group ID you found, and `USER_KEY` with the user's ID you found. Also, replace `'your-username'` with the actual username again.

INSERT INTO cwd_membership (id, parent_id, child_user_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name) 
VALUES (NEXT_MEMBERSHIP_ID, ADMIN_GROUP_ID, USER_KEY, 'GROUP_USER', 'GROUP', 'confluence-administrators', 'confluence-administrators', 'your-username', 'your-username');

Example:

-- Make sure to use the actual IDs you found!
INSERT INTO cwd_membership (id, parent_id, child_user_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name) 
VALUES (1001, 4587522, 241106945, 'GROUP_USER', 'GROUP', 'confluence-administrators', 'confluence-administrators', 'jane.doe', 'jane.doe');

(Note: If you get an error about missing columns, your Confluence version might be different. You might only need `id`, `parent_id`, and `child_user_id` in the `INSERT` statement. Check your table structure if needed.)

Step 5: Restart Confluence

The change won't work until you restart Confluence.

Option 1: Using systemd (common on Linux)

sudo systemctl restart confluence

Option 2: Using Confluence's scripts

cd /path/to/confluence/bin  # Go to Confluence's bin directory
./shutdown.sh
./startup.sh             # Wait a bit after shutdown before starting

Once Confluence is back up, the user you selected should be able to log in with full administrator rights!

Quick Tips & Warnings

• Backup First! Seriously, always back up your database before changing anything directly.
• Database Matters: The SQL commands might need small tweaks if you use Oracle or SQL Server instead of PostgreSQL/MySQL.
• Check Names: If commands fail, double-check table names (cwd_user, cwd_group, cwd_membership) as they could vary slightly between Confluence versions.
• Be Careful: You're bypassing normal safety checks. Make absolutely sure you're giving admin rights to the correct user account.
• Check Your Policies: This technique should only be used in emergency situations when you have proper authorization. Many organizations have policies about database access and admin rights. Always ensure you're following your company's IT governance rules and have appropriate approval before making these changes.
• Document Your Actions: If you do use this method, document what you did, when, and why. Transparency is key when making system-level changes like this.

Conclusion

Losing admin access to your Confluence instance can be a stressful situation, especially when critical work depends on it. While the method described in this guide should be considered a last resort, it provides IT administrators with a viable emergency solution when proper authorization exists. Remember that prevention is always better than cure, implementing proper admin access management and documentation can help avoid these situations entirely.