Dynamic RBAC in PostgreSQL: Elevate privileges only when it’s needed

Hola!!! 👋🏻

The Challenge: Balancing Access and Security

Imagine a database used by analysts who primarily query data but occasionally need to make changes. Granting users write access permanently increases the risk of accidental or malicious modifications. At the same time, forcing admin intervention every time write access is needed could hinder productivity.

Dynamic RBAC

The solution? Use PostgreSQL roles and permissions to allow temporary privilege escalation for write operations while ensuring users have read-only access by default. This minimizes risks while maintaining flexibility.

The Solution: Role Hierarchy

I have implemented a role-based hierarchy that allows users to dynamically elevate their privileges temporarily for specific tasks. Here’s how it works:

1. write_access: A non-login role that grants permissions to modify data (SELECT, INSERT, UPDATE, DELETE) for developers who need both read and write access temporarily.
2. data_analyst: A non-login role that grants permissions to modify data (SELECT, INSERT, UPDATE, DELETE, EXECUTE) for analysts who need read, write and execute access temporarily.
3. user: A login-enabled role with default read_only access. The user can temporarily elevate to the data_analyst role or write_access role when needed.

How it works?

select * from clpoc.customer;

----query successful----

update customer_table set customer_id = 43781 where id = 22727;

----permission denied----

set role data_analyst; 

----Switch to data_analyst role for elevated permissions----

update customer_table set customer_id = 43781 where id = 22727;

----query successful----

reset role;

----Revert back to the default read-only role----

Inheritance

PostgreSQL’s role inheritance feature simplifies permission management by enabling a role to inherit the privileges of roles it is granted. Here’s how it works:

1. With INHERIT (Default):
   a) The role automatically gains permissions from its granted roles.
   b) Example: If user is granted data_analyst, they can perform both read and write operations without explicit role switching.
2. With NOINHERIT:
   a) The role does not inherit permissions by default.
   b) Users must explicitly switch to a granted role using SET ROLE.

In this setup, the user role is configured with NOINHERIT, ensuring they operate with read_only permissions by default. This setup aligns with the principle of least privilege, minimizing security risks.

Note:

1. Make sure “ALTER ROLE “user” NOINHERIT;” is configured so that the user won’t inherit the access from parent roles by default.
2. Elevated privileges are active only during that session.

Benefits of This Approach

1. Enhanced Security
   Users operate with minimal permissions (read_only) by default.
2. Reduced Risk of Accidental Changes
   By default, users cannot perform write operations, reducing the risk of accidental modifications.
3. Auditable Privilege Escalation
   All SET ROLE commands are logged, providing a clear audit trail for privileged actions.
4. Granular Access Control
   Roles are structured with clear responsibilities, making permission management easier.
5. Flexibility
   Users can dynamically elevate their privileges as needed without requiring admin intervention.

Try this in your PostgreSQL setup and share your experience. 🚀

Thanks for reading my blog. Feel free to hit me up for any AWS/DevOps/Open Source-related discussions. 🙂

Manoj Kumar — LinkedIn.