Grant access to postgres_exporter to gitlab_partitions_dynamic schema

Production Change

Change Summary

I noticed on staging (https://nonprod-log.gitlab.net/app/r/s/D3BCG) that we have these errors:

  • permission denied for schema gitlab_partitions_dynamic
  • column pg_stat_statements.blk_read_time does not exist

The SQL query in question for gitlab_partitions_dynamic:

WITH column_saturation_values AS ( SELECT\n    pg_class.relname AS table_name,\n    pg_attribute.attname AS col_name,\n    pg_type.typname AS type_name,\n    (histogram_bounds::text::int8[])[array_upper(histogram_bounds, $4)] AS current_largest_value,\n    power($5, pg_type.typlen * $6 - $7) AS column_max_value,\n    pg_relation_size(pg_namespace.nspname || $8 || pg_class.relname) AS relsize\nFROM\n    pg_stats\n    INNER JOIN pg_namespace ON pg_stats.schemaname = pg_namespace.nspname\n    INNER JOIN pg_class ON pg_stats.tablename = pg_class.relname\n        AND pg_class.relnamespace = pg_namespace.oid\n    INNER JOIN pg_attribute ON pg_attribute.attname = pg_stats.attname\n        AND pg_attribute.attrelid = pg_class.oid\n    INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid\nWHERE\n    pg_type.typname = $9\n    AND pg_attribute.attname ILIKE $10\n    AND pg_namespace.nspname IN ($11, $12, $13)\n) SELECT\n    table_name || $1 || col_name AS column_name,\n    current_largest_value,\n    column_max_value\nFROM\n    column_saturation_values\nWHERE relsize > $2 ORDER BY\n    current_largest_value::float8 / column_max_value::float8 DESC nulls LAST\nLIMIT $3\n",

The permission error is also happening in production.

Provide a high-level summary of the change and its purpose.

Change Details

In this change, we grant SELECT permissions to postgres_exporter for the gitlab_partitions_dynamic_schema and its future tables that are missing this grant. This is similar to #6141 (closed) and #3167 (closed).

Change Details

  1. Services Impacted - ServicePostgres
  2. Change Technician - ``
  3. Change Reviewer - ``
  4. Time tracking - 1
  5. Downtime Component - none

Set Maintenance Mode in GitLab

If your change involves scheduled maintenance, add a step to set and unset maintenance mode per our runbooks. This will make sure SLA calculations adjust for the maintenance period.

Pre-execution steps

  • Make sure all tasks in Change Technician checklist are done
  • For C1 and C2 change issues, the SRE on-call has been informed prior to change being rolled out. (In #production channel, mention @sre-oncall and this issue and await their acknowledgement.)
    • The SRE on-call provided approval with the eoc_approved label on the issue.
  • For C1, C2, or blocks deployments change issues, Release managers have been informed prior to change being rolled out. (In #production channel, mention @release-managers and this issue and await their acknowledgment.)
  • There are currently no active incidents that are severity1 or severity2
  • If the change involves doing maintenance on a database host, an appropriate silence targeting the host(s) should be added for the duration of the change.

Detailed steps for the change

Change Steps - steps to take to execute the change

Estimated Time to Complete (mins) - 1

Run on primary:

-- Grant USAGE permission
GRANT USAGE ON SCHEMA gitlab_partitions_dynamic TO gitlab_exporter;

-- Grant SELECT on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA gitlab_partitions_dynamic TO gitlab_exporter;

-- Grant SELECT on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA gitlab_partitions_dynamic 
GRANT SELECT ON TABLES TO gitlab_exporter;

Post-Change Steps - steps to take to verify the change

On a patroni host, log into psql and check that this does not result in a permissions error:

SET ROLE postgres_exporter;

Rollback

Rollback steps - steps to be taken in the event of a need to rollback this change

Monitoring

Key metrics to observe

  • Metric: Permission Denied Error Logs
    • Location: Logs, Prod Logs
    • What changes to this metric should prompt a rollback: Continued logging of permission denied errors post-GRANT (rollback so we can investigate further)
  • Metric: Database CPU
    • Location: patroni-ci
    • What changes to this metric should prompt a rollback: Sharp, sustained dip in apdex or sharp, sustained increase in CPU usage.

Change Reviewer checklist

C4 C3 C2 C1:

  • Check if the following applies:
    • The scheduled day and time of execution of the change is appropriate.
    • The change plan is technically accurate.
    • The change plan includes estimated timing values based on previous testing.
    • The change plan includes a viable rollback plan.
    • The specified metrics/monitoring dashboards provide sufficient visibility for the change.

C2 C1:

  • Check if the following applies:
    • The complexity of the plan is appropriate for the corresponding risk of the change. (i.e. the plan contains clear details).
    • The change plan includes success measures for all steps/milestones during the execution.
    • The change adequately minimizes risk within the environment/service.
    • The performance implications of executing the change are well-understood and documented.
    • The specified metrics/monitoring dashboards provide sufficient visibility for the change.
      • If not, is it possible (or necessary) to make changes to observability platforms for added visibility?
    • The change has a primary and secondary SRE with knowledge of the details available during the change window.
    • The change window has been agreed with Release Managers in advance of the change. If the change is planned for APAC hours, this issue has an agreed pre-change approval.
    • The labels blocks deployments and/or blocks feature-flags are applied as necessary.

Change Technician checklist

  • The change plan is technically accurate.
  • This Change Issue is linked to the appropriate Issue and/or Epic
  • Change has been tested in staging and results noted in a comment on this issue.
  • A dry-run has been conducted and results noted in a comment on this issue.
  • The change execution window respects the Production Change Lock periods.
  • For C1 and C2 change issues, the change event is added to the GitLab Production calendar.
  • For C1 and C2 change issues, the Infrastructure Manager provided approval with the manager_approved label on the issue. Mention @gitlab-org/saas-platforms/inframanagers in this issue to request approval and provide visibility to all infrastructure managers.
  • For C1, C2, or blocks deployments change issues, confirm with Release managers that the change does not overlap or hinder any release process (In #production channel, mention @release-managers and this issue and await their acknowledgment.)