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:
CREATE ROLE Test_User WITH LOGIN PASSWORD 'Test1234'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
Assign SELECT, INSERT permission on one sample tbl_employees table:
GRANT SELECT,INSERT ON tbl_employees TO Test_User;
Using has_table_privilege(), Script to find list of Users or Roles, who have a INSERT permission on tbl_employees.
WHERE has_table_privilege(rolname, 'tbl_employees', 'INSERT');
Using pg_has_role(), Script to find list of objects, on which particular User or Role have INSERT permission.
FROM pg_roles CROSS JOIN pg_class
WHERE pg_has_role('test_user', rolname, 'MEMBER')
AND has_table_privilege(rolname, pg_class.oid, 'INSERT');
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 dbrnd.com, 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.