Skip to content
Functions and views to facilitate PostgreSQL object access inspection
Branch: master
Clone or download
jconway Bug fixes for role_path, function disambiguation, and WITH GRANT OPTION
role_path was incorrectly set when there was more than one level in the
role hierarchy. Fix that.

Additionally add IN argument types to the function name to disambiguate
multiple same named functions the same way PostgreSQL would do.

Finally, WITH GRANT OPTION decoration was inadvertantly left off for
object type view.
Latest commit 654a577 Apr 25, 2019

README.md

crunchy_check_access

Functions and views to facilitate PostgreSQL object access inspection

Overview

Typically install this script as the database superuser.

Once installed, to find all user privileges in the database while ignoring the system catalog and information schema, do:

SELECT * FROM all_access() WHERE base_role != CURRENT_USER;

To find all user privileges in the database including the system catalog and information schema, do:

SELECT * FROM all_access(true) WHERE base_role != CURRENT_USER;

By default, execute has been revoked from PUBLIC on the installed functions except my_privs() and my_privs_sys() and their corresponding convenience views my_privs and my_privs_sys. These functions/views allow users to discover their own privileges.

Note that the privileges are discovered by recursing through all roles accessable via a GRANT, including non-inherited ones (need to specifically use SET ROLE to escalate and gain said privilege). The source path to a given privilege shown in the output is available in the role_path column; base_role was the entry point (initially logged in user), while as_role shows the role with the actual privilege.

You can’t perform that action at this time.