Limit postgres_indexes to owned schemas
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
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
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