Skip to content

Limit postgres_indexes to owned schemas

Andreas Brandl requested to merge ab/reindexing-limit-schema into master

What does this MR do?

This adds a filter to postgres_indexes view, so that it only includes indexes from gitlab-owned tables. That is, we look into current_schema() and the two schemas for partitions.

Noteworthy, the current_schema() filter is evaluated dynamically (not at migration time). The view definition looks like so:

 SELECT (pg_namespace.nspname::text || '.'::text) || pg_class.relname::text AS identifier,
    pg_index.indexrelid,
    pg_namespace.nspname AS schema,
    pg_class.relname AS name,
    pg_index.indisunique AS "unique",
    pg_index.indisvalid AS valid_index,
    pg_class.relispartition AS partitioned,
    pg_index.indisexclusion AS exclusion,
    pg_indexes.indexdef AS definition,
    pg_relation_size(pg_class.oid::regclass) AS ondisk_size_bytes
   FROM pg_index
     JOIN pg_class ON pg_class.oid = pg_index.indexrelid
     JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
     JOIN pg_indexes ON pg_class.relname = pg_indexes.indexname
  WHERE pg_namespace.nspname <> 'pg_catalog'::name AND (pg_namespace.nspname = ANY (ARRAY["current_schema"(), 'gitlab_partitions_dynamic'::name, 'gitlab_partitions_static'::name]));

I kept the <> 'pg_catalog' filter because this made sense on its own (irrespective of what the current_schema() is).

See #254995 (closed)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Andreas Brandl

Merge request reports