EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges

Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have been used. This task may be easy if there are only one or two objects available in your database and one or two roles and/or users you need to check for privilege. But if the database is marginally big and either the number of objects or number of database roles or/and users are high, then this exercise is going to be hectic and time consuming.

Fortunately, the functionality of finding and identifying unnecessary granted privileges is now available in EDB Postgres Advanced Server (EPAS) v16. This feature is called Privilege Analysis.

Why Privilege Analysis is Important

Privilege analysis dynamically captures privileges used by database users during a specified time period. This EPAS feature helps to identify vulnerability by examining used and unused privilege to implement the least privilege model.

In the minimum or least privilege model, users are only given the privileges and access they need to perform their work. Most of the time, even though users use a small set of privileges to perform their tasks, they are granted the same set of powerful privileges. Without the privilege analysis feature, figuring out the privileges that each user must have can be challenging, and users could end up with having more privileges than they actually need.

The privilege capture policy captures the privileges used by users, and views can be queried to see those privileges. For syntax and more details of the CAPTURE PRIVILEGES POLICY, refer to the EDB Postgres Advanced Server 16 documentation. 

Privilege Capture Policy Highlights

Here are some key points about privilege capture policy:

1. You must  have the CAPTURE_ADMIN role to use or administer the privilege capture policy.
2. Privilege capture policy behavior depends on the type of the policy mentioned while creating the policy. Policy can be on the database, the list of role/user or context, i.e. expression.
3. One database can have multiple policies available, but only one can be in an enabled state at a point of time.

These are the simple steps for using privilege analysis:

1. Create Policy
2. Enable policy
3. Perform required database operations
4. Disable policy
5. Query views to find out used and unused privileges

How to Use Privilege Analysis

Let’s walk through the detailed steps to capture role/user level privileges, where privileges for specific users or roles will be captured:

The Superuser in the database is vaibhav. Now, create a new database and user.

CREATE DATABASE mydb;
CREATE USER myuser;

Grant ALL to user myuser on public schema

GRANT ALL ON SCHEMA public TO myuser;

Connect to new database and then create table, grant privileges to user myuser on created table

\c mydb 
CREATE TABLE mytable(a INT);
GRANT select,insert,delete,update ON mytable TO myuser;

Create policy to capture privileges of user myuser. The superuser has the CAPTURE ADMIN role granted, but if any other user wants to administer the policy then CAPTURE ADMIN needs to be granted to it.

CREATE CAPTURE PRIVILEGES POLICY mypolicy ON USER myuser;

Check policy details

SELECT name,type,enabled,roles FROM dba_priv_captures;
   name   |  type  | enabled |  roles   
----------+--------+---------+----------
 mypolicy | G_ROLE | f       | {myuser}
(1 row)

Enable policy using ALTER syntax

ALTER CAPTURE PRIVILEGES POLICY mypolicy ENABLE myrun;

Switch to user myuser and perform operations on table mytable

SET ROLE myuser;
INSERT INTO mytable VALUES(1);
SELECT * FROM mytable;

Switch to user vaibhav

SET ROLE vaibhav;

Disable the policy

ALTER CAPTURE PRIVILEGES POLICY mypolicy DISABLE;

Check used privileges during enabling and disabling of policy mypolicy

SELECT * FROM dba_used_privs;
 policy_name | run_name | object_class |  object_name   | column_name | application | role_name | privilege_typ
e 
-------------+----------+--------------+----------------+-------------+-------------+-----------+-------------
 mypolicy    | myrun    | table        | public.mytable | -           | edb-psql    | myuser    | INSERT
 mypolicy    | myrun    | table        | public.mytable | -           | edb-psql    | myuser    | SELECT
(2 rows)

So, user myuser used INSERT and SELECT privileges on table mytable out of all granted privileges.

Check unused privileges for user myuser

SELECT * FROM dba_unused_privs where role_name = 'myuser';
 policy_name | run_name | object_class |  object_name   | column_name | application | role_name | privilege_typ
e 
-------------+----------+--------------+----------------+-------------+-------------+-----------+--------------
--
 mypolicy    | myrun    | table        | public.mytable | -           | edb-psql    | myuser    | INSERT
 mypolicy    | myrun    | table        | public.mytable | -           | edb-psql    | myuser    | UPDATE
(2 rows)

For user myuser, unused privileges are UPDATE and DELETE.

These unused privileges can be revoked to avoid security vulnerabilities.

Drop policy. This deletes all data related to policy, i.e., captured data.

DROP CAPTURE PRIVILEGES POLICY mypolicy;

Conclusion

Analyzing privilege use is beneficial for finding unnecessary granted privileges and implementing least privilege best practices. The privilege capture policy feature of EDB Postgres Advanced Server (EPAS) 16 now provides this functionality.

Contributions

My colleague Suraj Kharage and I worked on the capture privilege policy feature. Amul Sul and Rushabh Lathia reviewed it, and it was tested by my colleague Neha Sharma.