Identify which project or repository a SSH key is associated with
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.
Summary
When trying to add an SSH key for a user the following error occurs:
This SSH key is already used to access a repository or project.
In a large instance of Bitbucket Server/DC it can be difficult to find which repositories and/or projects use this access key.
This guide will help you determine which project or repository an SSH key is being used for as an access key.
Environment
Bitbucket Server/DC
Solution
Unfortunately, there isn't a great way to do this in Bitbucket Server at the moment. It's possible, but it will involve querying your database and/or making REST API calls.
Solution via dabatase:
The easiest way to accomplish this is by running a query similar to the following:
Identify the public key text, this will be similar to the contents of
~/.ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC3eahbupC1FeZQCZd7BbTioBnRFwiO39OpsdmN8Bn5MWM1Qg6pPZIr8j5OAAGZ9Qyn307Bfu1K5rFddsPoat69FLPaQAMGSysa59A62MJkYnBIDDOrue/GQpXmx3Ns2ntQdgUH9ls67uPdK7ca2Q2J2MNJUJSbPZA7FwrOiejnL6gLOT6V2hTy6Hjl8L7s3KrFJgZnKX8YcujErHHqBw8d9PIak9PfafcvUBwbkRrYHun967lbJ+HJpaEuXkSWuRkWyGpBAaOMx1tQVAbwNLbPJib/i+QlQIf9DR6GQRIVQX8NJB/KH30cyfg1YoVhmCVb5xgh8irBsJkRux83owC9 example@company.com
Using the public key, run the following query on Bitbucket's database to find the Project this key may be associated with:
select project.name, project.project_key from "AO_FB71B4_SSH_PUBLIC_KEY" spk join sta_project_permission spp on spp.user_id = spk."USER_ID" join project on project.id = spp.project_id where spk."KEY_TEXT" = 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC3eahbupC1FeZQCZd7BbTioBnRFwiO39OpsdmN8Bn5MWM1Qg6pPZIr8j5OAAGZ9Qyn307Bfu1K5rFddsPoat69FLPaQAMGSysa59A62MJkYnBIDDOrue/GQpXmx3Ns2ntQdgUH9ls67uPdK7ca2Q2J2MNJUJSbPZA7FwrOiejnL6gLOT6V2hTy6Hjl8L7s3KrFJgZnKX8YcujErHHqBw8d9PIak9PfafcvUBwbkRrYHun967lbJ+HJpaEuXkSWuRkWyGpBAaOMx1tQVAbwNLbPJib/i+QlQIf9DR6GQRIVQX8NJB/KH30cyfg1YoVhmCVb5xgh8irBsJkRux83owC9 example@company.com';
The key may be used at the repository level, if so, the following query will review the repository that the key belongs to:
select repository.name, repository.slug, project.name from "AO_FB71B4_SSH_PUBLIC_KEY" spk join sta_repo_permission srp on srp.user_id = spk."USER_ID" join repository on repository.id = srp.repo_id join project on project.id = repository.project_id where spk."KEY_TEXT" = 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC3eahbupC1FeZQCZd7BbTioBnRFwiO39OpsdmN8Bn5MWM1Qg6pPZIr8j5OAAGZ9Qyn307Bfu1K5rFddsPoat69FLPaQAMGSysa59A62MJkYnBIDDOrue/GQpXmx3Ns2ntQdgUH9ls67uPdK7ca2Q2J2MNJUJSbPZA7FwrOiejnL6gLOT6V2hTy6Hjl8L7s3KrFJgZnKX8YcujErHHqBw8d9PIak9PfafcvUBwbkRrYHun967lbJ+HJpaEuXkSWuRkWyGpBAaOMx1tQVAbwNLbPJib/i+QlQIf9DR6GQRIVQX8NJB/KH30cyfg1YoVhmCVb5xgh8irBsJkRux83owC9 example@company.com';
It's also possible the key is associated with a user profile. You may also run the following query to verify if the key belongs to a specific user:
select snu.name, snu.slug from "AO_FB71B4_SSH_PUBLIC_KEY" spk join sta_normal_user snu on snu.user_id = spk."USER_ID" where spk."KEY_TEXT" = 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC3eahbupC1FeZQCZd7BbTioBnRFwiO39OpsdmN8Bn5MWM1Qg6pPZIr8j5OAAGZ9Qyn307Bfu1K5rFddsPoat69FLPaQAMGSysa59A62MJkYnBIDDOrue/GQpXmx3Ns2ntQdgUH9ls67uPdK7ca2Q2J2MNJUJSbPZA7FwrOiejnL6gLOT6V2hTy6Hjl8L7s3KrFJgZnKX8YcujErHHqBw8d9PIak9PfafcvUBwbkRrYHun967lbJ+HJpaEuXkSWuRkWyGpBAaOMx1tQVAbwNLbPJib/i+QlQIf9DR6GQRIVQX8NJB/KH30cyfg1YoVhmCVb5xgh8irBsJkRux83owC9 example@company.com';
Solution via rest API:
Use the first 30 or so characters of you SSH key to make sure it's unique. This will return the KEY_ID
of that key.
You can then run a REST API call with the endpoint /rest/keys/1.0/ssh/<KEY_ID>/projects
and /rest/keys/1.0/ssh/<KEY_ID>/repos
.
Examples:
First, list all keys by opening this url:
http://<bitbucket_url>/rest/ssh/1.0/keys
This example will use 45 as the KEY_ID:
http://<bitbucket_url>/rest/keys/1.0/ssh/45/repos
http://<bitbucket_url>/rest/keys/1.0/ssh/45/projects
You can also use a curl command rather than a URL:
curl -u <username>:<password>-v -X GET -H "Content: application/json" http://<bitbucket_url>/rest/keys/1.0/ssh/<id>/repos
curl -u <username>:<password>-v -X GET -H "Content: application/json" http://<bitbucket_url>/rest/keys/1.0/ssh/<id>/projects
This will return a list of repositories or projects that use this key. You can then use REST API to delete those keys or manually delete them from the UI if there aren't many.
If you want to delete all access keys with the same ID you can use a curl command like the following:
curl -i -u <username>:<password> -X DELETE -H "Content: application/json" -H "Content-Type: application/json" http://<bitbucket_url>/rest/keys/1.0/ssh/10
Please see our documentation here for more REST API endpoints related to SSH keys.