Skip to content

2.31.0.0-b180

2.31.0.0-b180: [#32000] YSQL: Preload pg_auth_members catalog caches
@kai-franz kai-franz tagged this 19 Jun 00:33 
Summary:
**Problem**

The catalog-cache prefetch descriptor table `YbGetPrefetchableTableInfo` (`src/postgres/src/backend/utils/cache/relcache.c`) marked `pg_auth_members` as `YB_TABLE_CACHE_TYPE_NO_CACHE`. As a result, even when `pg_auth_members` was included in `ysql_catalog_preload_additional_table_list` (or via the boolean `ysql_catalog_preload_additional_tables`), its catalog caches `AUTHMEMMEMROLE` and `AUTHMEMROLEMEM` were never populated: `YbFillCaches` only fills tables for which `YbHasAssociatedCache` is true, which excludes `NO_CACHE` tables. The rows were prefetched into the prefetcher buffer, but no syscache was filled, so `cache->yb_cc_is_fully_loaded` stayed false.

**Why it matters**

`roles_is_member_of()` is invoked on every non-superuser permission/ACL check (database CONNECT, table privilege checks, `pg_has_role()`, etc.) and issues `SearchSysCacheList1(AUTHMEMMEMROLE, ...)`. Because the cache was never fully loaded, the local list-build fast path `YbBuildCatCacheListFromPreloadedCache` (gated on `yb_cc_is_fully_loaded` and `yb_catcache_list_from_preloaded_limit`) could not engage, so every membership list lookup fell through to a full-table scan / master RPC -- once per fresh backend. Under high connection churn this is a steady stream of avoidable master reads.

**Fix**

Give `pg_auth_members` a real catalog cache (`CAT_CACHE_WITH_INDEX` over `{AUTHMEMMEMROLE, AUTHMEMROLEMEM}`), matching how `pg_authid`, `pg_operator`, `pg_class`, etc. are already configured. When `pg_auth_members` is preloaded, both caches are populated and marked fully-loaded, and role-membership list lookups are served locally with no RPC.

**Notes**

- **No-op when `pg_auth_members` is not preloaded:** `YbFillCaches` only fills registered tables, so a backend that does not preload `pg_auth_members` is unaffected. `pg_auth_members` is not in the default preload set; this helps deployments that add it to the preload list.
- **Consistency is unchanged** from the other preloaded caches: `yb_cc_is_fully_loaded` is cleared by `CatCacheInvalidate` and `ResetCatalogCache`, so role-membership DDL (GRANT/REVOKE), which bumps the catalog version and triggers a cache refresh, invalidates the preloaded cache correctly.

Test Plan:
```
./yb_build.sh release --cxx-test pg_libpq-test \
  --gtest_filter 'PgAuthMembersTest.*'
```

Also manually verified on a local RF=1 release cluster (clang21) started with the following flags:
  --ysql_catalog_preload_additional_tables=true
  --ysql_catalog_preload_additional_table_list=pg_statistic,pg_amop,pg_operator,pg_namespace,pg_auth_members,pg_range
  --ysql_enable_auth=true
  --ysql_pg_conf_csv=yb_debug_log_catcache_events=true

Created a non-superuser role that is a member of another role, granted SELECT on
a table to the parent role, and opened 20 fresh backends as the member role,
each running a role-membership check (`pg_has_role('parent','MEMBER')` plus a
SELECT requiring the granted privilege). Counted catalog-cache events for the
pg_auth_members cache (id 8 = AUTHMEMMEMROLE) in the PG server log.

Before (pg_auth_members = NO_CACHE):
  - pg_auth_members catcache entries preloaded: 0
  - "Catalog cache list miss on cache with id: 8" (master RPC): 41
  - lists built locally from preloaded cache: 0

After (this change):
  - pg_auth_members catcache entries preloaded: 25 (cache id 8)
  - "Catalog cache list miss on cache with id: 8": 0
  - "Built catalog cache list from preloaded cache 8": 41

i.e. the 41 membership-list lookups that were master RPCs are now served
entirely from the local fully-loaded cache. `pg_operator` (id 37) list misses
remained 0 both before and after, confirming it already serves locally.

Reviewers: myang, sanketh

Reviewed By: myang

Subscribers: yql, ybase

Differential Revision: https://phorge.dev.yugabyte.com/D54050
Assets 2
Loading