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.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from