PostgreSQL Query Catalog

Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY.

Find all security policies.

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.

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.

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.

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.

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.

Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application.

Know the privileges that users have in your system. Probably all the database users do not need these 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.

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.

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.

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.

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.

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.

Find membership relations between roles.

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.

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.

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.

You must give rights to use routines to the users/roles that correspond to applications.