Скачать или смотреть How to Limit pg_catalog.pg_class Queries by search_path in PostgreSQL

Learn why PostgreSQL does not restrict pg_class queries by search_path and the recommended strategies to isolate schemas or applications securely.
---
This video is based on the question https://stackoverflow.com/q/79349647/ asked by the user 'John Winston' ( https://stackoverflow.com/u/14026062/ ) and on the answer https://stackoverflow.com/a/79350607/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How can I restrict query in `pg.catalog.pg_class` to what the search_path is allowed?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to drop me a comment under this video.
---
Introduction

When working with PostgreSQL, users often rely on the search_path to control schema visibility. However, when querying system catalog tables like pg_catalog.pg_class, the search_path does not restrict results by default. This can cause unexpected behavior, especially if multiple schemas contain identically named tables.

The Problem

Consider a scenario where multiple schemas each have a table named users. An application running under a specific search_path expects to interact with only one schema’s users table. Yet, a query like:

[[See Video to Reveal this Text or Code Snippet]]

returns rows for all users tables across all schemas, ignoring the search_path. This can lead to data confusion or security concerns if the application assumes limited access.

Why Can't PostgreSQL Enforce search_path on Catalog Queries?

The search_path affects name resolution in queries, not the rows returned by system catalog tables.

Catalog tables like pg_class store metadata about all database objects; applying a row-level security policy or automatic filtering by search_path is not supported.

PostgreSQL does not have built-in row-level security for system catalogs.

Recommended Solutions

1. Use Separate Databases per Application Instance

If you have multiple similar application instances each requiring isolated schemas:

Prefer separate databases instead of multiple schemas.

Each database comes with its own set of catalog tables.

This ensures that catalog queries return only the objects relevant to that instance.

It provides better security isolation and avoids catalog confusion.

2. Application-Level Fixes

If multiple schemas are required by design, the application must disambiguate schema-qualified object names explicitly.

Queries should filter by schema OID in catalogs, e.g., relnamespace = 'myschema'::regnamespace.

As these queries are part of the application, request bug fixes from the application vendor or modify the source if possible.

3. No Workarounds on PostgreSQL Side

Row-level security policies cannot be applied to system catalogs.

No automatic way exists to restrict catalog visibility by search_path or role.

Summary

PostgreSQL’s search_path controls name resolution, not catalog visibility.

Catalog queries like those on pg_class will return all matching objects regardless of schema restrictions.

The best practice is to design your environment with isolated databases for isolated application instances.

If multiple schemas are necessary, the application itself must handle schema qualification properly.

By understanding PostgreSQL architecture, you can better design schemas and applications to avoid ambiguity and maintain security.