Recover lost Confluence password

Occasionally it is necessary to log in into a Confluence System for which you do not have the password.

This happens if you work in customer support and a customer sends you a Confluence dump for support or debugging purposes. Most likely the customer has set his Confluence instance to private mode, so that you can't create an account to log in. Even if not, you do not have admin privileges.

The solution is to find an existing Confluence Administrator account and reset the password to a known one.

To list all Confluence Administrators in a given Confluence 4.x instance, issue the SQL command below:

select
    user_name, 
    display_name, 
    email_address 
from 
    cwd_user, 
    cwd_membership, 
    cwd_group 
where 
    cwd_user.id = cwd_membership.child_user_id and 
    cwd_membership.parent_id = cwd_group.id and 
    cwd_group.group_name='confluence-administrators';

For previous Confluence releases, use this command instead:

select
    users.id,
    name,
    email
from
    local_members,
    users,
    groups
where
    local_members.userid=users.id and
    local_members.groupid=groups.id and
    groupname='confluence-administrators';

You should get a response similar to this:

+--------+---------------------------+-------------------------------------+--------------------------------------------------------------+
| id     | name                      | email                               |                                                              |
+--------+---------------------------+-------------------------------------+--------------------------------------------------------------+
| 393217 | admin                     | arne.schirmacher@softwaretesting.de | rRU7enAt79vrMPFEx6r3H6qFudTNsGb5KA6LXy5dpgWGJCo4xbLqKgA...== |
+--------+---------------------------+-------------------------------------+--------------------------------------------------------------+

1 rows in set (0.00 sec)

If you have an old Confluence site (< v. 2.5), the user information might be stored in different tables. If the statement above does not return any rows, try the next one:

select
    *
from
    os_user,
    os_group,
    os_user_group
where
    os_group.groupname='confluence-administrators' and
    os_user_group.group_id=os_group.id and
    os_user_group.user_id=os_user.id;

You should get a response similar to this:

+----+-------------+---------------------------------------------+----+---------------------------+----------+---------+
| id | username    | passwd                                      | id | groupname                 | group_id | user_id |
+----+-------------+---------------------------------------------+----+---------------------------+----------+---------+
|  1 | admin       | 5d7edTmEZWDz19dlpCnuF4eSXZx7ZD+2cVdi9w...== |  1 | confluence-administrators |        1 |       1 |
|  3 | admin2      | AIemXIs3vSn1NnR/KMlnK+wcrndi0snza2AT8g...== |  1 | confluence-administrators |        1 |       3 |
+----+-------------+---------------------------------------------+----+---------------------------+----------+---------+
2 rows in set (0.01 sec)

The data in the password (or passwd) field is the hashed value of the user's password.
To force it to a known password, enter the hash

'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='

for the user. You can then login with this user name and the password 'admin'. If it does not work, restart the Tomcat server (Confluence caches data and will not see manual changes to the database).

update
    users
set
    password='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
where
    id=393217;

To update the passwd field in the os_user table in an old Confluence instance, use

update
    os_user
set
    passwd='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
where id=1;

Note

This is not a hack to break in an arbitrary Confluence Site. You must have access to this site's database.

It is also possible to edit the XML Backup File and insert the password hash string manually.

Child pages

1 Comment

Anonymous