How to identify the SSH access key used for Git operations in Bitbucket Data Center
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary:
When a Git request is authenticated using an access key linked to a project or repository, a username that isn't present in Bitbucket's user directories is logged. This occurs because the SSH key at the project or repository level doesn't have a corresponding user account. As a result, a system-generated username is assigned when the key is first stored, and this username appears in the logs. However, administrators may occasionally need to verify which SSH key was used for a specific Git operation.
Environment:
The solution has been validated in Bitbucket Data Center 8.9.20 but may apply to other versions.
Solution:
Important Note on DB Queries
This Database Query / Functionality is not officially supported!
- Atlassian is not responsible for translating queries for different databases.
- This query should only be used as an example. Any desired adjustments should be checked with your local Database Administrator, including queries related to different DBMS that are not present/listed in this KB article, like Oracle Database.
- Atlassian will not be providing support for changes, adjustments, or inquiries regarding the below queries, as this is not part of our Atlassian Support Offerings.
- Atlassian does not provide any support to sample database queries provided in our Knowledge Base documents. Extraction of data from Bitbucket should be performed using our REST API Documentation because we do not keep all data relationships in the DB. Many connections to data are handled in the code. The REST API can go through the code paths to get the data. A SQL would have to do that manually or, in some cases, it can’t be done at all.
First, find the request in the atlassian-bitbucket-access.log file.
10.211.XX.XX | ssh | i@1OFQVVx683x107x0 | 25c9b3b30d4d6abc | 2024-12-01 11:23:16,560 | SSH - git-upload-pack '/pro1/repo5.git' | "SSH-2.0-OpenSSH_8.8" | - | - | - | - | - | 1ab6cd2 | 10.211.XX.XX | ssh | o@1OFQVVx683x107x0 | 25c9b3b30d4d6abc | 2024-12-01 11:23:16,812 | SSH - git-upload-pack '/pro1/repo5.git' | "SSH-2.0-OpenSSH_8.8" | 0 | 179 | 176 | protocol:2, refs, ssh:user:id:3 | 252 | 1ab6cd2 |- Identify the user ID field. For instance, in this log, the user ID is recorded as "ssh:user:id:3." This user ID(3 in this case) will be used in the following queries to retrieve the Access Key.
- The Access key may be linked at either the project or repository level, so both need to be checked.
To view the access key details associated at the project level: If no data is returned, it means the SSH key is not associated with the project.
SELECT ssu.name, ssu.user_id, CASE WHEN pp.perm_id = 2 THEN 'PROJECT_READ' WHEN pp.perm_id = 3 THEN 'PROJECT_WRITE' END AS permission, p.id as project_id, p.name as project_name, p.project_key as project_key, ssu.display_name, ssu.label as label, spk."CREATED_DATE", spk."EXPIRY_DAYS", spk."KEY_MD5", spk."KEY_TEXT" as SSH_KEY, spk."LABEL" as label, spk."KEY_TYPE" FROM project p LEFT JOIN sta_project_permission pp ON pp.project_id = p.id LEFT JOIN sta_service_user ssu on (pp.user_id = ssu.user_id) LEFT JOIN "AO_FB71B4_SSH_PUBLIC_KEY" spk on ssu.user_id = spk."USER_ID" where ssu.user_id = <user-id-identified-in-step2>;
To view the access key details associated at the repository level:
SELECT ssu.name, ssu.user_id, CASE WHEN rp.perm_id = 0 THEN 'REPO_READ' WHEN rp.perm_id = 1 THEN 'REPO_WRITE' END AS permission, r.id as repo_id, p.project_key as project_key, r.project_id as project_id, r.slug as repo_slug, r.name as repo_name, ssu.display_name, ssu.label as label, spk."CREATED_DATE", spk."EXPIRY_DAYS", spk."KEY_MD5", spk."KEY_TEXT" as SSH_KEY, spk."LABEL" as label, spk."KEY_TYPE" FROM repository r LEFT JOIN sta_repo_permission rp ON rp.repo_id = r.id LEFT JOIN sta_service_user ssu on (rp.user_id = ssu.user_id) LEFT JOIN PROJECT p on r.project_id = p.id LEFT JOIN "AO_FB71B4_SSH_PUBLIC_KEY" spk on ssu.user_id = spk."USER_ID" where ssu.user_id = <user-id-identified-in-step2>;