Fix Column Privileges for pg_subscription.subwalrcvtimeout

Core Problem

When the subwalrcvtimeout column was added to the pg_subscription system catalog (commit fb80f38), the corresponding column-level privilege grant was omitted. PostgreSQL's pg_subscription catalog has a deliberate security design: all columns are publicly readable except subconninfo (which contains connection strings that may include passwords or other sensitive credentials). This is enforced through explicit column-level SELECT privileges granted via system_views.sql.

The omission means that non-superusers cannot read the subwalrcvtimeout column, breaking the documented and intended security model:

-- As non-superuser:
SELECT subwalrcvtimeout FROM pg_subscription;
ERROR:  permission denied for table pg_subscription

This is a catalog privilege maintenance problem that recurs whenever new columns are added to pg_subscription — developers must remember to update the GRANT statements in system_views.sql to include the new column.

Architectural Context

pg_subscription Privilege Model

PostgreSQL system catalogs are generally world-readable, but pg_subscription is a special case. Because it stores connection information (subconninfo) that may contain credentials, table-level SELECT is not granted to PUBLIC. Instead, individual column-level grants are issued for every column except subconninfo. This is managed in system_views.sql with an explicit enumeration of columns.

This design creates a maintenance hazard: every time a new column is added to pg_subscription, the developer must also add a corresponding column privilege grant. If forgotten, the new column becomes inaccessible to non-superusers — a silent regression that may not surface until users actually try to query the column without superuser privileges.

The Fix

The patch itself is trivial — adding subwalrcvtimeout to the list of columns granted SELECT privilege to PUBLIC in system_views.sql. However, since this modifies catalog behavior, committing requires a catalog version bump.

Proposed Regression Test

Fujii proposes a proactive solution to prevent future recurrences: a regression test that dynamically checks all columns of pg_subscription against the expected privilege model. The test uses pg_attribute and has_column_privilege() to verify:

1. subconninfo is not readable by a non-superuser test role
2. All other non-dropped columns are readable by that test role

This is an elegant approach because it's self-maintaining — when a new column is added, the test will automatically fail if the privilege grant is missing, rather than requiring someone to remember to update both the grant and a hardcoded test. The query returns count(*) = 0 for violations, meaning any column that doesn't conform to the expected model (either subconninfo being readable, or any other column being unreadable) will cause a test failure.

Technical Implications

• Catalog version bump required: Any change to system catalog privileges requires updating the catalog version number, ensuring pg_upgrade compatibility checks work correctly.
• Backport consideration: This is a bug fix that likely needs backporting to whatever branch introduced subwalrcvtimeout (via commit fb80f38), since users on that version cannot access the column without superuser privileges.
• Pattern recognition: This is a recurring class of bug in PostgreSQL development. The privilege model for pg_subscription uses an allowlist approach (explicitly granting each column), which is error-prone. The regression test mitigates this but doesn't eliminate the root cause architecturally.

Key Design Tradeoff

The fundamental design tension is between security (denying access to subconninfo) and usability (allowing access to everything else). An alternative design might use a view that excludes subconninfo and grant SELECT on that view, but the current column-level privilege approach was chosen, presumably to keep the catalog directly queryable without an indirection layer. The cost is this ongoing maintenance burden.