Filter Queries

Found 1092 queries.

Page 39 of 55
  • 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 only

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 License: MIT (opens in new tab)

#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 only

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 License: MIT (opens in new tab)

#763. Privileges to execute routines

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#764. Privileges to use base table columns

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#765. Privileges to use base tables

INFORMATION_SCHEMA+system catalog 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 License: MIT (opens in new tab)

#766. Privileges to use views

INFORMATION_SCHEMA+system catalog 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.

General License: MIT (opens in new tab)

#767. Procedures cannot have START TRANSACTION and SAVEPOINT

INFORMATION_SCHEMA+system catalog base tables

You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11.

Problem detection License: MIT (opens in new tab)

#768. Publications with no tables

system catalog base tables only

Find publications that do not contain any table.

Problem detection License: MIT (opens in new tab)

#769. PUBLIC has TEMPORARY privilege in the database

system catalog base tables only

Find as to whether PUBLIC (all current and future users) has TEMPORARY privilege in the database. PUBLIC gets the privilege by default.

Problem detection License: MIT (opens in new tab)

#770. PUBLIC has the USAGE privilege of a schema

INFORMATION_SCHEMA+system catalog base tables

Find schemas where PUBLIC has the usage privilege.

Problem detection License: MIT (opens in new tab)

#771. Publicly accessible system catalog tables

INFORMATION_SCHEMA only

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

General License: MIT (opens in new tab)

#772. Public schema privileges granted to PUBLIC

system catalog base tables only

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 License: MIT (opens in new tab)

#773. Quoted numbers indicating poor type selection

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#774. Range lower bound can be NULL

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#775. Range lower bound is not restricted

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#776. Range lower bound restriction does not consider -infinity

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#777. Range upper bound can be NULL

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#778. Range upper bound is not restricted

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#779. Range upper bound restriction dos not consider infinity

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#780. Recursive relationships with the same source and target

system catalog base tables only

Find incorrectly implemented adjacency lists.

Problem detection License: MIT (opens in new tab)
# 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