`null_indexes` query breaks when logical replication is enabled

[gstokkink]

Hi,

First of all, thanks for your hard work on this excellent gem!

We are having issues when trying to run the null_indexes query (or index_info, which uses this query as well) on our database. It will fail with the following error message:

PRODUCTION [15] irb(main)> RailsPgExtras.null_indexes
(irb):15:in `<main>': PG::InsufficientPrivilege: ERROR:  permission denied for attribute pg_subscription.subconninfo (ActiveRecord::StatementInvalid)
/srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:55:in `exec': ERROR:  permission denied for attribute pg_subscription.subconninfo (PG::InsufficientPrivilege)
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:55:in `block (2 levels) in raw_execute'
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:1028:in `block in with_raw_connection'
        from /srv/bundle/ruby/3.3.0/gems/activesupport-7.1.3.4/lib/active_support/concurrency/null_lock.rb:9:in `synchronize'
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:1000:in `with_raw_connection'
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:54:in `block in raw_execute'
        from /srv/bundle/ruby/3.3.0/gems/activesupport-7.1.3.4/lib/active_support/notifications/instrumenter.rb:58:in `instrument'
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:1143:in `log'
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:53:in `raw_execute'
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract/database_statements.rb:521:in `internal_execute'
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract/database_statements.rb:131:in `execute'
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract/query_cache.rb:25:in `execute'
        from /srv/bundle/ruby/3.3.0/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `execute'
        from /srv/bundle/ruby/3.3.0/gems/rails-pg-extras-5.3.1/lib/rails-pg-extras.rb:46:in `run_query'
        from /srv/bundle/ruby/3.3.0/gems/rails-pg-extras-5.3.1/lib/rails-pg-extras.rb:19:in `block (2 levels) in <module:RailsPgExtras>'

Looking at the query it executes (https://github.com/pawurb/ruby-pg-extras/blob/main/lib/ruby_pg_extras/queries/null_indexes.sql) it seems to query each table & attribute combination for which an index is defined. However, from the PostgreSQL spec (https://www.postgresql.org/docs/current/catalog-pg-subscription.html):

Access to the column subconninfo is revoked from normal users, because it could contain plain-text passwords.

Note that our database runs on RDS, so we are not superusers. Perhaps it's possible to skip this particular column? Or make this an option?

[pawurb]

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.

[gstokkink]

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.

[pawurb]

@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?

[gstokkink]

@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.

[pawurb]

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?