PostgreSQL: Find which object assigns to which user or role and vice versa

As a Database Administrator, we are following common practice by creating Database User or Role and assign only require permission to it.

When we are assigning different object level permission to particular Database Role, It is also important check total assigned object to particular Database Role or Which object assigned to which Role/User?

Most of the Database Administrators do not have practice to audit the list of assigned permissions of particular Database User/Role.

It is fine that we are securing database access using specific role, but that is also similarly important to check information about the assigned object and Database Role.
Periodically, Database Administrator should review the list of permissions which assigned to a Database Role/User.

PostgreSQL provides list of system functions for auditing User, Role, and its permissions.

In PostgreSQL, these functions called as Access Privilege Inquiry Functions.
You can find list of functions in this official document.

I have also prepared a small demonstration of this.

First, Create a sample restricted database ROLE:

Assign SELECT, INSERT permission on one sample tbl_employees table:

Using has_table_privilege(), Script to find list of Users or Roles, who have a INSERT permission on tbl_employees.

Using pg_has_role(), Script to find list of objects, on which particular User or Role have INSERT permission.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of