Restore validation SQL query for secrets-manager Runway service

Why are we doing this work

For GitLab Secrets Manager beta, Cloud SQL backups for the secrets-manager Runway service needs to be monitored. See gitlab-com/gl-infra/readiness!258 (diffs, comment 2790770801)

Further details

Runway provides backup validation for Cloud SQL databases. See https://docs.runway.gitlab.com/runtimes/cloud-run/managed-services/cloudsql/#restore-validation-1

Each project has a scheduled pipeline that restores the latest backup to another Cloud SQL instance and, optionally, runs a validation query against the restored database.

For production Cloud SQL instances, an alert will be fired if a restore validation pipeline was not triggered in the last 24 hours or if a pipeline has started but not completed within 2 hours.

The query should return 1 row of at least 2 columns to pass validation. The two columns can be data of any kind and will be printed in the CI job.

Relevant links

• https://openbao.org/docs/configuration/storage/postgresql/#manually-creating-tables
• https://docs.runway.gitlab.com/runtimes/cloud-run/managed-services/cloudsql/#restore-validation-1
• https://gitlab.com/gitlab-com/gl-infra/platform/runway/provisioner/-/blob/main/config/runtimes/cloud-run/cloud-sql/managed.yml

Non-functional requirements

• Documentation:
• Feature flag:
• Performance:
• Testing:

Implementation plan

• Determine an SQL query for backup restore validation.
• Update config/runtimes/cloud-run/cloud-sql/managed.yml and provide a restore_validation SQL query as documented.

Verification steps

Check backup restore validation on ops.gitlab.net.

• Check successful restore validation.
• Check failed restore validation. This triggers an alert.

/cc @cipherboy-gitlab @srajadas