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:

1. 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

2. 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';

3. 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';

4. 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:

Identify the KEY_ID associated to the SSH Key in question. There is no direct API approach for this, you will need to find it via DB with the steps from the previous step or by scanning the access keys from the repos, projects or user profile to obtain the KEY_ID:

• To retrieve the SSH Access keys and Key Ids for a given project: 

curl -u <username>:<password> -v -X GET -H "Content: application/json" http://<bitbucket_url>/rest/keys/1.0/projects/{projectKey}/ssh 

• To retrieve the SSH Access keys and Key Ids for a given repository.

curl -u <username>:<password> -v -X GET -H "Content: application/json" http://<bitbucket_url>/rest/keys/1.0/projects/{projectKey}/ssh/repos/{repositorySlug}/ssh

• To retrieve the SSH Access keys and Key Ids from the user profile. This command will list the keys from the current authenticated user.

curl -u <username>:<password> -v -X GET -H "Content: application/json" http://<bitbucket_url>/rest/ssh/1.0/keys

You can then run a REST API call with the endpoint /rest/keys/1.0/ssh/<KEY_ID>/projects or  /rest/keys/1.0/ssh/<KEY_ID>/repos to identify all the projects or repositories where this key is found.

Examples:

Identify the KEY_ID, in this case, we obtained this key by reviewing the access keys from the user profile.

curl -u admin:password -v -X GET -H "Content: application/json" http://localhost:6890/b890/rest/ssh/1.0/keys
Note: Unnecessary use of -X or --request, GET is already inferred.
*   Trying [::1]:6890...
* Connected to localhost (::1) port 6890
* Server auth using Basic with user 'kalyan'
> GET /b890/rest/ssh/1.0/keys HTTP/1.1
> Host: localhost:6890
> Authorization: Basic a2FseWFuOnBhc3N3b3Jk
> User-Agent: curl/8.4.0
> Accept: */*
> Content: application/json
>
< HTTP/1.1 200
< X-AREQUESTID: @FUYUG4x1065x1040x0
< X-AUSERID: 102
< X-AUSERNAME: admin
< Cache-Control: no-cache, no-transform
< Vary: x-ausername,x-auserid,cookie,accept-encoding
< X-Content-Type-Options: nosniff
< Content-Type: application/json;charset=UTF-8
< Transfer-Encoding: chunked
< Date: Tue, 23 Jan 2024 16:45:10 GMT
<
* Connection #0 to host localhost left intact
{"size":1,"limit":25,"isLastPage":true,"values":[{"id":45,"text":"ssh-XXX XXXXXXXXXX xxxxx@atlassian.com","label":"SSH_XXX","algorithmType":"XXX","bitLength":256,"createdDate":1706026110305}],"start":0}%

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.