Identify which project or repository a SSH key is associated with
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
- 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 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';