Identify which project or repository a SSH key is associated with

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

This guide will help you determine which project or repository an SSH key is being used for as an access key. This can be useful if you're trying to add a SSH key to your account but receiving the following message:

This SSH key is already used to access a repository or project

Solution

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

Last modified on Nov 2, 2018

Was this helpful?

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