How to Update the PAT Token for Github Repositories in Bamboo Data Center Repository Configuration using Database Queries

Summary

This article explains how to generate a list of Idle Bamboo agent reports that have been unused since a specific date for build plans and deployments from the database.

Environment

The SQL queries listed on this page have been tested on Bamboo 9.2.1 with Postgres 10 DB but may also work with other versions of Bamboo.

Solution

Bamboo stores PAT tokens for linked/plan repositories in the database in an encrypted format. Therefore, you would be required to encrypt the PAT token generated in Github prior to replacing it using the updated Database query.

To encrypt the PAT token, you can refer to the documentation Bamboo Specs encryption

Steps for updating the encrypted PAT token using the Database:

1. Stop Bamboo

2. Back up your Bamboo database for restore/recovery purposes

3. Run the following select query to list out all the existing GitHub repositories and corresponding encrypted PAT tokens. Old encrypted PAT values will be required in the next update query to replace it with new encrypted PAT values

   PostgresDB:

   Postgres DB query

   1 2 3 4 5 SELECT name AS Bamboo_linked_repo_name, CAST(UNNEST(XPATH('//serverConfiguration/entry/string[text()="repository.github.repository"]/../string[2]/text()', XML_DEFINITION_DATA::XML)) AS VARCHAR) AS Github_reponame, CAST(UNNEST(XPATH('//serverConfiguration/entry/string[text()="repository.github.password"]/../string[2]/text()', XML_DEFINITION_DATA::XML)) AS VARCHAR) AS PAT_token_encrypted FROM VCS_LOCATION WHERE PLUGIN_KEY LIKE '%gh2'

   Example

   

4. Run the following update query, replacing <old-pat-encrypted> and <new-pat-encrypted> as needed:

   PostgresDB:

   Postgres DB query

   1 2 3 4 5 6 7 8 9 -- Change to new password(PAT) UPDATE VCS_LOCATION SET xml_definition_data = replace( xml_definition_data, '<string>repository.github.password</string><string><old-pat-encrypted></string>', '<string>repository.github.password</string><string><new-pat-encrypted></string>' ) WHERE xml_definition_data LIKE '%<old-pat-encrypted>%';

   Example

   1 2 3 4 5 6 7 8 9 10 -- Example: UPDATE VCS_LOCATION SET xml_definition_data = replace( xml_definition_data, '<string>repository.github.password</string><string>BAMSCRT@0@0@68fkyNSRuLEgm6JzeFFWScmUC3sLXLyEQqyUCu8ioImsnDw8HV3XhKzANpw49rTD</string>', '<string>repository.github.password</string><string>BAMSCRT@0@0@D9TM89unAWadCJz2YIf1kegeW9GSKGhR5xRQZZoh+QUoFNj4LI6WRbH0xh6fmGWH</string>' ) WHERE xml_definition_data LIKE '%BAMSCRT@0@0@68fkyNSRuLEgm6JzeFFWScmUC3sLXLyEQqyUCu8ioImsnDw8HV3XhKzANpw49rTD%';

   Customise the DB query as required.

   The specific DB query in Step 4 replaces the PAT tokens for all the linked/planned repositories which has been configured with encrypted PAT value of 'BAMSCRT@0@0@68fkyNSRuLEgm6JzeFFWScmUC3sLXLyEQqyUCu8ioImsnDw8HV3XhKzANpw49rTD.

   Please consider modifying the WHERE condition in the DB query based on your use case.

   For example to replace the PAT token for a specific linked repository with repository name 'github_repo_1', the WHERE statement in the DB query needs to be modified as below:

   1 2 3 4 5 6 7 8 -- Example: UPDATE VCS_LOCATION SET xml_definition_data = replace( xml_definition_data, '<string>repository.github.password</string><string>BAMSCRT@0@0@68fkyNSRuLEgm6JzeFFWScmUC3sLXLyEQqyUCu8ioImsnDw8HV3XhKzANpw49rTD</string>', '<string>repository.github.password</string><string>BAMSCRT@0@0@D9TM89unAWadCJz2YIf1kegeW9GSKGhR5xRQZZoh+QUoFNj4LI6WRbH0xh6fmGWH</string>' ) WHERE xml_definition_data LIKE '%BAMSCRT@0@0@68fkyNSRuLEgm6JzeFFWScmUC3sLXLyEQqyUCu8ioImsnDw8HV3XhKzANpw49rTD%' AND name = 'github_repo_1';