PostgreSQL Query Catalog

Find partial or case insensitive unique indexes. These implement uniqueness constraints that are impossible to enforce with the help of SQL's regular UNIQUE constraint.

Find patterns of the Boolean expressions of simple CHECK constraints (involve only one column). Do not solve the same task in different places differently. The same rule could be implemented with CHECK constraints that have different Boolean expressions. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)

Find the patterns of the names of columns of simple primary keys. Make sure that the naming is consistent. Ideally, the names should indicate as to whether the column is a surrogate or a natural key column. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog.

Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.

Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.

Find the patterns of the names of trigger and rule names. Make sure that the naming is consistent.

Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs.

Working with sets of rows rather than processing each row separately is more effective.

Find routines that have a parameter (input or output) that can have values from different types. This parameter has one of the PostgreSQL polymorphic types. The set of polymorphic types in PostgreSQL is a proper subset of its pseudo-types. The use of such a parameter allows a single routine definition to operate on many different data types, with the specific data type(s) being determined by the data types actually passed to it in a particular call.

Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less.

Find for different types of database objects all the prefixes that are used in different names. One should be consistent in naming, including in the use of prefixes. If you use prefix in the name of a database object, then it could refer to the type of the database object. Do not use different prefixes in the names of database objects that have the same type.

This query performs a comprehensive security audit of access control lists (ACLs) across a wide range of database objects, including the database itself, schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers. It retrieves privileges granted to any principal other than the object's owner or the systemic postgres superuser. The objective is to identify and validate "third-party" access rights, ensuring compliance with the principle of least privilege and preventing unauthorized access accumulation.

Find privileges to execute routines that have been given to non-superusers. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to modify data in the database (in case of table functions read data), then they must execute a routine.

If you do give privileges to base tables, then these should follow the principle of least privilege - the smallest possible amount of privileges to the smallest possible set of columns

Users (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables.

Find privileges to use views. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to read data from a database, then they should use views.

Find privileges to use system catalog base tables or views that have been granted to public.

Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments.

Find user-defined routines with the same name and parameters (including the order of parameters) in different schemas.

Make sure that your parameters have appropriate types in order to avoid unnecessary type casting.