Filter 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 the relatively small amount of data in the system catalog of a database.
#761. Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers
system catalog base tables onlyThis 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.
#762. Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser
system catalog base tables onlyFind 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).
#763. Privileges to execute routines
INFORMATION_SCHEMA+system catalog base tablesFind 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.
#764. Privileges to use base table columns
INFORMATION_SCHEMA+system catalog base tablesIf 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
#765. Privileges to use base tables
INFORMATION_SCHEMA+system catalog base tablesUsers (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.
#766. Privileges to use views
INFORMATION_SCHEMA+system catalog base tablesFind 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.
#767. Procedures cannot have START TRANSACTION and SAVEPOINT
INFORMATION_SCHEMA+system catalog base tablesYou cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11.
#768. Publications with no tables
system catalog base tables onlyFind publications that do not contain any table.
#769. PUBLIC has TEMPORARY privilege in the database
system catalog base tables onlyFind as to whether PUBLIC (all current and future users) has TEMPORARY privilege in the database. PUBLIC gets the privilege by default.
#770. PUBLIC has the USAGE privilege of a schema
INFORMATION_SCHEMA+system catalog base tablesFind schemas where PUBLIC has the usage privilege.
#771. Publicly accessible system catalog tables
INFORMATION_SCHEMA onlyFind privileges to use system catalog base tables or views that have been granted to public.
#772. Public schema privileges granted to PUBLIC
system catalog base tables onlyThis query checks whether the public schema has CREATE or USAGE privileges granted to the PUBLIC pseudo-role (all database users). While CREATE is a critical vulnerability that allows unprivileged users to create rogue objects in a shared namespace, leaving the USAGE privilege open to PUBLIC is also redundant and violates the principle of least privilege. In a hardened environment, all default ambient access to the public schema should be removed, ensuring that schema traversal and object creation are restricted to explicitly authorized roles.
#773. Quoted numbers indicating poor type selection
INFORMATION_SCHEMA+system catalog base tablesThis query identifies potential type mismatches by flagging numeric literals enclosed in single quotes. This pattern often triggers unnecessary type casting overhead or indicates a schema design flaw where a textual data type (e.g., VARCHAR) is used to store exclusively numeric data. The recommendation is to either align the data type with the content (e.g., switch to SMALLINT for codes like '1', '2') or ensure the data justifies the textual type by including non-numeric characters. The query explicitly excludes standard SQL error codes found in exception handling routines, as these are syntactically required to be strings.
#774. Range lower bound can be NULL
INFORMATION_SCHEMA+system catalog base tablesThis query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL lower bound. This highlights ranges that can be "unbounded" on their starting side, which may be unintentional and could impact query logic and data constraints.
#775. Range lower bound is not restricted
INFORMATION_SCHEMA+system catalog base tablesThis query finds range columns of base tables that are missing a safety check on their starting value. It looks for columns where the start of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their starting points.
#776. Range lower bound restriction does not consider -infinity
INFORMATION_SCHEMA+system catalog base tablesThis query identifies RANGE type columns in base tables that have a NOT NULL constraint on their lower bound and an additional CHECK constraint, but this check does not account for -infinity. This may indicate a "magic number" problem, where a fixed lower limit (e.g., '1900-01-01') is used instead of the more explicit and semantically correct unbounded (-infinity) value.
#777. Range upper bound can be NULL
INFORMATION_SCHEMA+system catalog base tablesThis query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL upper bound. This highlights ranges that can be "unbounded" on their ending side, which may be unintentional and could impact query logic and data constraints.
#778. Range upper bound is not restricted
INFORMATION_SCHEMA+system catalog base tablesThis query finds range columns of base tables that are missing a safety check on their ending value. It looks for columns where the end of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their ending points.
#779. Range upper bound restriction dos not consider infinity
INFORMATION_SCHEMA+system catalog base tablesThis query identifies RANGE type columns in base tables that have a NOT NULL constraint on their upper bound and an additional CHECK constraint, but this check does not account for infinity. This may indicate a "magic number" problem, where a fixed upper limit (e.g., '2900-01-01') is used instead of the more explicit and semantically correct unbounded (infinity) value.
#780. Recursive relationships with the same source and target
system catalog base tables onlyFind incorrectly implemented adjacency lists.
| # | Name(sorted ascending, activate to sort descending) | Goal(activate to sort ascending) | Type(activate to sort ascending) | Data source(activate to sort ascending) | Last update(activate to sort ascending) | License | Actions |
|---|---|---|---|---|---|---|---|
| 761 | Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers | 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. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 762 | 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 | MIT (opens in new tab) | View | |
| 763 | Privileges to execute routines | 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. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 764 | Privileges to use base table columns | 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 | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 765 | Privileges to use base tables | 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. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 766 | Privileges to use views | 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. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 767 | Procedures cannot have START TRANSACTION and SAVEPOINT | You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 768 | Publications with no tables | Find publications that do not contain any table. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 769 | PUBLIC has TEMPORARY privilege in the database | Find as to whether PUBLIC (all current and future users) has TEMPORARY privilege in the database. PUBLIC gets the privilege by default. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 770 | PUBLIC has the USAGE privilege of a schema | Find schemas where PUBLIC has the usage privilege. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 771 | Publicly accessible system catalog tables | Find privileges to use system catalog base tables or views that have been granted to public. | General | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 772 | Public schema privileges granted to PUBLIC | This query checks whether the public schema has CREATE or USAGE privileges granted to the PUBLIC pseudo-role (all database users). While CREATE is a critical vulnerability that allows unprivileged users to create rogue objects in a shared namespace, leaving the USAGE privilege open to PUBLIC is also redundant and violates the principle of least privilege. In a hardened environment, all default ambient access to the public schema should be removed, ensuring that schema traversal and object creation are restricted to explicitly authorized roles. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 773 | Quoted numbers indicating poor type selection | This query identifies potential type mismatches by flagging numeric literals enclosed in single quotes. This pattern often triggers unnecessary type casting overhead or indicates a schema design flaw where a textual data type (e.g., VARCHAR) is used to store exclusively numeric data. The recommendation is to either align the data type with the content (e.g., switch to SMALLINT for codes like '1', '2') or ensure the data justifies the textual type by including non-numeric characters. The query explicitly excludes standard SQL error codes found in exception handling routines, as these are syntactically required to be strings. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 774 | Range lower bound can be NULL | This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL lower bound. This highlights ranges that can be "unbounded" on their starting side, which may be unintentional and could impact query logic and data constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 775 | Range lower bound is not restricted | This query finds range columns of base tables that are missing a safety check on their starting value. It looks for columns where the start of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their starting points. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 776 | Range lower bound restriction does not consider -infinity | This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their lower bound and an additional CHECK constraint, but this check does not account for -infinity. This may indicate a "magic number" problem, where a fixed lower limit (e.g., '1900-01-01') is used instead of the more explicit and semantically correct unbounded (-infinity) value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 777 | Range upper bound can be NULL | This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL upper bound. This highlights ranges that can be "unbounded" on their ending side, which may be unintentional and could impact query logic and data constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 778 | Range upper bound is not restricted | This query finds range columns of base tables that are missing a safety check on their ending value. It looks for columns where the end of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their ending points. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 779 | Range upper bound restriction dos not consider infinity | This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their upper bound and an additional CHECK constraint, but this check does not account for infinity. This may indicate a "magic number" problem, where a fixed upper limit (e.g., '2900-01-01') is used instead of the more explicit and semantically correct unbounded (infinity) value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 780 | Recursive relationships with the same source and target | Find incorrectly implemented adjacency lists. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View |