This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
1 2 |
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:
1 |
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.
1 2 3 |
SELECT rolname FROM pg_roles 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.
1 2 3 4 5 |
SELECT DISTINCT relname FROM pg_roles CROSS JOIN pg_class WHERE pg_has_role('test_user', rolname, 'MEMBER') AND has_table_privilege(rolname, pg_class.oid, 'INSERT'); |
Leave a Reply