Identify which project or repository a SSH key is associated with

Still need help?

The Atlassian Community is here for you.

Ask the community

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:

  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:

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.


Last modified on Aug 12, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.