If you’re trying to figure out how migrate your local users over to an external authentication provider, you’ve probably discovered that there’s no built in ability to link a local account to external account. Fortunately, there is a way to do this, but it requires making edits to the matrix-synapse database.
There’s a lot that we could get into here, but for the sake of brevity, I’m going to assume the following:
- Matrix-Synapse is configured to use postgresql
- KeyCloak is the external authentication provider and has already been configured
- Matrix-Synapse has already been configured for KeyCloak OIDC
- The local usernames that exist in matrix-synapse and the external provider (KeyCloak) usernames are the same.
Before you make any changes, stop the matrix-synapse service and make sure you take a backup!
There are two matrix-synapse database tables that we will be working with, users and user_external_ids.
The users table (shown above) contains a password hash for the local user. This will need to be removed. Use the sql query below and replace ‘@test_user:rickelobe.com’ with the user you wish to modify.
UPDATE users SET password_hash = '' WHERE name = '@test_user:rickelobe.com';
user_external_ids:
The user_external_ids table contains a list of all user accounts that are using an external authentication provider. We’ll need to add an entry to this table for the KeyCloak user account with the same username. In the screenshot below I’m verifying that there does not already exist an external provider account with the same username.
The query returned zero results, so we’re OK to move on. If you have a non-zero number of results, you either have another provider configured that the account is associated with, or you (or the user) probably already attempted to login via KeyCloak with the user account. If your case is the former, STOP here & revert any change you made following this guide. If your case is the latter, you’re done and can proceed to logging in via KeyCloak provided the auth_provider value matches what you’ve setup for KeyCloak.
For those of you that do not yet have any entries in the user_external_ids table for the user account, see below (I expect this is most of you):
The user_external_ids table contains three items:
- auth_provider: type of external provider (saml, oidc, ldap, etc) configured in homeserver.yaml.
- external_id: this is the unique ID for the user account – provided by the external provider (KeyCloak in this case)
- user_id: this is the username of the account provided by the external provider (again, this is KeyCloak in this case)
To get the external_id log in to the KeyCloak Administration console and view the user list.
The first column is the ID value we are looking for. You will need to click on the ID link to retrieve the full ID.
Copy the full ID value and use the following query to add the required entries into the user_external_ids table. Replace KEYCLOAK_USER_ID with the copied ID and @USERNAME:DOMAIN.TLD with the user account you’re migrating.
INSERT INTO user_external_ids VALUES('oidc-keycloak','YOUR_ID_FROM_KEYCLOAK_HERE','@USERNAME:DOMAIN.TLD');
In the screenshot above the values have been inserted into the table and I’ve verified the additions. Next, you can login to your Matrix client and use the External Auth SSO provider for the account!
Success!
I hope this was helpful. I spent several hours digging into how to get this accomplished. This is a compilation of information gathered from many sources, but the core of this solution was found in an issue thread on GitHub: https://github.com/matrix-org/synapse/issues/7633
P.S. I discovered that you can also link non-like usernames. It’s really just the KeyCloak user ID (the long alphanumeric string) that matters; the ID that you use when inserting the values into the user_external_ids table is what links them. The KeyCloak username does not need to match. You do still need to remove the password_hash from the users table though.