PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 13 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name ▲ Goal Type Data source Last update License
1 All foreign tables Find all foreign tables. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
2 All user mappings Find all user mappings for foreign servers and tables General system catalog base tables only 2025-11-07 10:11 MIT License View
3 Excessive privileges on databases, schemas, domains, types, languages, foreign data wrappers, and foreign servers Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
4 Find all publications Find publications of tables that have been created in order to enable logical replication. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
5 Foreign servers without user mappings Find foreign servers that do not have any associated user mappings. "A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource." Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
6 Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
7 Insufficient number of user-defined foreign tables This query assesses the utilization of PostgreSQL's Foreign Data Wrapper (FDW) capabilities. It verifies the existence of at least 2 user-defined foreign tables within the database schema. This requirement ensures that the implemented solution demonstrates the ability to integrate and query data from external sources, extending the data model beyond local storage. Problem detection INFORMATION_SCHEMA only 2025-11-30 09:31 MIT License View
8 Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that are not for the owner and not for the systemic user postgres. Make sure that there is the right amount of privileges for each and every relevant user. General system catalog base tables only 2025-11-07 10:11 MIT License View
9 Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have). Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
10 Publications with no tables Find publications that do not contain any table. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
11 Unused foreign data wrappers Find foreign data wrappers that do not have any associated foreign servers. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
12 Unused foreign servers Find foreign servers that do not have any associated foreign tables. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
13 Updatable foreign tables that refer to another PostgreSQL table This query identifies foreign tables established via the postgres_fdw (PostgreSQL Foreign Data Wrapper) that are configured to permit data modification (updatability). While postgres_fdw supports INSERT, UPDATE, and DELETE operations on remote tables, enabling this capability introduces complexity regarding distributed transactions, performance, and security. The query serves as an audit tool to verify that the updatability of these foreign tables is a deliberate architectural requirement and not an unintended default configuration. General INFORMATION_SCHEMA only 2025-11-20 11:45 MIT License View