[Hypercare] `restrict_direct_pg_catalog_access` implementation

[mikealfare]

Description

This issue collects bugs immediately following the release of the flag restrict_direct_pg_catalog_access. It will remain open for the duration of the pg_catalog migration as well as a short period following the implementation. Users are encouraged to report bugs and regressions via this ticket.

Bug reports

Preview Give feedback

1. [Bug] dbt build fails when flag restrict_direct_pg_catalog_access set True #914
   type:bug +1
   
2. https://dbt-labs.slack.com/archives/C067EUS2D7S/p1727453184616519 (multiple in this thread)