<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/contributor-success/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Label this issue](https://contributors.gitlab.com/manage-issue?action=label&projectId=278964&issueIid=587484) - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=587484) </details> <!--IssueSummary end--> ## Experiment summary I assume that the proposed script optimizes the work of the gitlab-rake gitlab:doctor:secrets check. To test this, we tried to check 2.3 million+ records in the p_ci_builds table, the difference was about 5-6 hours. ## Hypothesis Good day! I’d like to suggest considering a possible improvement to the encrypted data decryption check in the p_ci_builds table. I have Gitlab in production. Every time I update, I run the decryption check with the command: gitlab-rake gitlab:doctor:secrets The ci:builds check takes the longest – about 6 hours, as the table contains 2.3 million+ records. While discussing the issue with the AI, it became clear that the check checks every record in the table, regardless of whether it contains an encrypted element. Together with the AI, we wrote a script that checks only rows containing encrypted elements, and the check time was reduced from 6 hours to 10 minutes. I’d like to ask you to review this script to determine whether it’s correct for the check. ## Business problem Checking gitlab:doctor:secrets after updating gitlab-rake requires stopping Gitlab. This results in downtime for code creation. ## Supporting data For us, this is a difference of 5+ hours with the development pipeline downtime, when using the test after updating Gitlab with the gitlab-rake gitlab:doctor:secrets command and using the proposed script ## Expected outcome I'm looking forward to hearing from you as developers if this script can be used to replace gitlab-rake's gitlab:doctor:secrets check step for ci::builds ? ## Experiment design & implementation For testing, I suggest using a GitLab database running PostgreSQL versions 14-17 with over 2.3 million records in the p_ci_builds table. Run the test using the gitlab-rake gitlab:doctor:secrets command and the suggested script, and compare the execution time. ## Results, lessons learned, next steps I'm looking forward to hearing from you as developers if this script can be used to replace gitlab-rake's gitlab:doctor:secrets check step for ci::builds ? ```ruby #!/usr/bin/env ruby # frozen_string_literal: true require 'logger' require 'rainbow' # Initialize logger logger = Logger.new(STDOUT) logger.level = Logger::INFO # Parse command line arguments options = {} OptionParser.new do |opts| opts.banner = "Usage: check_ci_builds_secrets.rb [options]" opts.on("-n N", "--last N", Integer, "Check only the last N records") do |n| options[:last] = n end opts.on("-h", "--help", "Show this help message") do puts opts exit end end.parse! # Check attribute decryption correctness def valid_attribute?(data, attr, logger) data.send(attr) true rescue OpenSSL::Cipher::CipherError, TypeError logger.debug Rainbow(" ❌ Failed to decrypt #{attr} for record with ID: #{data.id}").red false rescue StandardError => e logger.debug Rainbow(" ❌ Error for #{attr} in record with ID: #{data.id}: #{e}").red false end # Main check logic def check_ci_builds_secrets(options, logger) model = Ci::Build encrypted_attributes = [] # Collect only found encrypted attributes if model.respond_to?(:encrypted_attributes) && !model.encrypted_attributes.nil? encrypted_attributes += model.encrypted_attributes.to_a logger.info Rainbow(" ✅ Found #{model.encrypted_attributes.size} encrypted attributes via `encrypts`: #{model.encrypted_attributes.to_a.join(', ')}").green end if model.respond_to?(:attr_encrypted_attributes) && !model.attr_encrypted_attributes.nil? encrypted_attributes += model.attr_encrypted_attributes.keys logger.info Rainbow(" ✅ Found #{model.attr_encrypted_attributes.keys.size} encrypted attributes via `attr_encrypted`: #{model.attr_encrypted_attributes.keys.join(', ')}").green end if model.respond_to?(:encrypted_token_authenticatable_fields) && !model.encrypted_token_authenticatable_fields.nil? encrypted_attributes += model.encrypted_token_authenticatable_fields.map { |attr| "#{attr}_encrypted" } logger.info Rainbow(" ✅ Found #{model.encrypted_token_authenticatable_fields.size} encrypted tokens: #{model.encrypted_token_authenticatable_fields.join(', ')}").green end if encrypted_attributes.empty? logger.info Rainbow("❌ No encrypted attributes found for model #{model}.").red return end encrypted_attributes = encrypted_attributes.uniq logger.info Rainbow("✅ Found #{encrypted_attributes.size} unique encrypted attributes: #{encrypted_attributes.join(', ')}").green logger.info Rainbow("Starting check...").blue # Select only necessary records scope = options[:last] ? model.order(id: :desc).limit(options[:last]) : model.all total_records = scope.count logger.info Rainbow("Total records to check: #{total_records}").blue failures_per_row = Hash.new { |h, k| h[k] = [] } processed_records = 0 # Increase batch size for speed scope.find_each(batch_size: 5000) do |data| encrypted_attributes.each do |attr| unless valid_attribute?(data, attr, logger) failures_per_row[data.id] << attr end end processed_records += 1 logger.debug Rainbow("Processed #{processed_records}/#{total_records} records").cyan if (processed_records % 5000).zero? end # Output results if failures_per_row.empty? logger.info Rainbow("✅ No errors found! All encrypted attributes are correct.").green else logger.info Rainbow("❌ Found #{failures_per_row.keys.count} records with errors:").red failures_per_row.each do |row_id, attrs| logger.info Rainbow(" - Record with ID: #{row_id}, failed to decrypt attributes: #{attrs.join(', ')}").red end end end # Run the script check_ci_builds_secrets(options, logger) ``` ## Checklist * [ ] Fill in the experiment summary and write more about the details of the experiment in the rest of the issue description. Some of these may be filled in through time (the "Result, learnings, next steps" section for example) but at least the experiment summary should be filled in right from the start. * [ ] Add the label of the `group::` that will work on this experiment (if known). * [ ] Mention the Product Manager, Engineering Manager, and at least one Product Designer from the group that owns the part of the product that the experiment will affect. * [ ] Fill in the values in the [ICE score table](#ice-score) ping other team members for the values you aren’t confident about (i.e. engineering should almost always fill out the ease section). Add the ~"ICE Score Needed" label to indicate that the score is incomplete. * [ ] Replace the ~"ICE Score Needed" with an ICE low/medium/high score label once all values in the ICE table have been added. * [ ] Mention the [at]gitlab-core-team team and ask for their feedback.