PostgreSQL Design Check Queries

Catalog of PostgreSQL queries for finding information about a PostgreSQL database and its design problems

	Choose collection:
AND	Choose query type:
AND	Choose query reliability:
AND	Choose category:	Queries of this category provide information about the security measures.
AND	Choose data source:	From where does the query gets its information?
AND	Enter string:
AND	Has fixing 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.

There are 59 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	All database privileges	Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY.	General	system catalog base tables only	2021-11-23 20:28	MIT License	View
2	All security policies	Find all security policies.	General	system catalog base tables only	2023-11-17 18:41	MIT License	View
3	Are the passwords hashed?	Find base table columns that name refers to the possibility that these are used to register passwords. Return a value from each such column. Make sure that the password is not registered as open text.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-10 12:14	MIT License	View
4	Base table columns for storing username, password, and salt	Find base table columns that name refers to the possibility that these are used to register usernames, passwords, and salt. Password should not be open text. It should be a hash value that has been hashed by using salt.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
5	Database can be accessed through PUBLIC privileges	Find as to whether the database access by users (applications) can take place thanks to PUBLIC privileges, i.e., find as to whether PUBLIC (all current and future users) has CONNECT privilege in the database. PUBLIC gets the privilege by default.	Problem detection	system catalog base tables only	2022-10-31 10:19	MIT License	View
6	Database connect privilege is missing	Find non-superusers who have a privilege to use a table or a routine but do not have the privilege to connect to the database.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-21 13:51	MIT License	View
7	Different search paths of SECURITY DEFINER functions	Find the different search paths used in case of SECURITY DEFINER functions and the number of their occurrences. Make sure that these have been specified correctly and consistently and that they do not refer to any non-existent schemas.	General	INFORMATION_SCHEMA+system catalog base tables	2020-12-16 10:59	MIT License	View
8	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
9	Extension routines that execution privilege has been granted to PUBLIC	Know the privileges that users have in your system. Probably all the database users do not need these privileges.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
10	Grantable column privileges	Find column privileges 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	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
11	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
12	Grantable roles	Find roles that a member can grant to others, i.e., the role has been granted with ADMIN 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:42	MIT License	View
13	Grantable routine privileges	Find routine privileges 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	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
14	Grantable table privileges	Find table privileges 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	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
15	Grantable usage privileges	Find usage privileges 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	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
16	Granted roles	Find membership relations between roles.	General	system catalog base tables only	2024-01-07 13:30	MIT License	View
17	Grantees	Database must be used by users who have minimal set of privileges for performing tasks. The query helps to find out as to whether some user/role other than PUBLIC and a superuser have rights to use tables and routines of the database.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
18	Incorrect password hash update	Find row level update triggers that incorrectly implement update of password hash. It should not be that the new password hash is calculated based on the existing hash.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
19	Installed extensions	Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
20	Insufficient routine privileges	You must give rights to use routines to the users/roles that correspond to applications.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View