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 2020-11-06 15:20 MIT License View
2 All user mappings Find all user mappings for foreign servers and tables General system catalog base tables only 2020-11-11 17:00 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 2021-12-31 14:40 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 2020-11-18 15:09 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 2021-02-25 17:29 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 2024-01-07 13:43 MIT License View
7 Insufficient number of user-defined foreign tables There must be at least n (two in this case) user-defined foreign tables in the database. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 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 2021-03-07 20:57 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 2022-10-21 15:53 MIT License View
10 Publications with no tables Find publications that do not contain any table. Problem detection system catalog base tables only 2021-02-25 17:30 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 2021-02-25 17:30 MIT License View
12 Unused foreign servers Find foreign servers that do not have any associated foreign tables. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
13 Updataple foreign tables that refer to another PostgreSQL table Find foreign tables that have been defined based on postgresql_fwd foreign data wrapper and that are updatable. Make sure that updatability is in this case needed. General INFORMATION_SCHEMA only 2020-11-06 17:15 MIT License View