`null_indexes` query breaks when logical replication is enabled about ruby-pg-extras HOT 5 OPEN

Hi. pg_subscription.subconninfo column is not used by any of the gem queries. Also I've used this gem with multiple RDS database and never seen this error.

Can you rerun the raw SQL query from https://github.com/pawurb/ruby-pg-extras/blob/main/lib/ruby_pg_extras/queries/null_indexes.sql (you have to substitute %{min_relation_size_mb}) and check if the problem persists? I think it could be related to your AR connection setup.

from ruby-pg-extras.

Did any of those RDS databases have logical replication enabled? We're running PostgreSQL version 16.3R2 on RDS, by the way.

I already ran the raw SQL query, same error. I also managed to reproduce the issue with the following, more limited, query:

SELECT *
FROM
    pg_class c
    JOIN pg_index i ON i.indexrelid = c.oid
    JOIN pg_attribute a ON a.attrelid = c.oid
    JOIN pg_class c_table ON c_table.oid = i.indrelid
    LEFT JOIN pg_stats s ON s.tablename = c_table.relname AND a.attname = s.attname;

ERROR: permission denied for attribute pg_subscription.subconninfo

Note that if I leave out the LEFT JOIN pg_stats s ON s.tablename = c_table.relname AND a.attname = s.attname; bit, it works fine. Apparently sensitive information may be stored in the pg_stats catalog for that column.

from ruby-pg-extras.

@gstokkink I don't think I've used it with logical replication only read replicas. I don't know how to resolve this issue, and currently don't have time to reproduce and debug it. I'm open to any PRs that would fix it.

Maybe a workaround for you is to run this query on a read replica which does not have replication enabled?

from ruby-pg-extras.

@pawurb would it be an acceptable fix for you to explicitly leave out system catalogs in the various queries? This can be done, for example, by ignoring all namespaces that start with pg_. See https://www.postgresql.org/docs/current/catalogs.html for more details. I don't think people are particularly interested in the indexes that PG maintains itself on the system catalogs.

from ruby-pg-extras.

I'm not sure I understand, how filtering out the indexes would resolve this issue? Can you provide a sample PR? Also is only null_indexes not working with your current setup or other queries as well?

from ruby-pg-extras.