Role-Based Access Control and Security

In this lesson, you will learn how to implement granular Role-Based Access Control (RBAC) in PostgreSQL to enforce the principle of least privilege by defining specific permission layers.

Understanding RBAC

In an enterprise environment, assigning permissions to individual database users is a maintenance nightmare. Instead, we use Roles—logical containers that group sets of privileges. By granting these roles to users, we ensure that a user only possesses the specific access required to perform their job function, effectively minimizing the blast radius of a potential security breach.

The Hierarchy of Privilege

To enforce the principle of least privilege, we avoid using the superuser or db_owner roles for day-to-day operations. Instead, we create a tiered structure:

1. The Base Role: A group representing a functional area (e.g., analyst_role).
2. Schema/Object Permissions: Granting access only to the necessary namespaces.

Consider a scenario where we want an analyst to read specific tables without the ability to modify or drop data. We accomplish this by decoupling the login identity from the permissions:

-- 1. Create the base role for analysts
CREATE ROLE analyst_role;

-- 2. Grant schema usage (required to even see the objects)
GRANT USAGE ON SCHEMA reports TO analyst_role;

-- 3. Grant read-only access to specific tables
GRANT SELECT ON TABLE reports.sales_data TO analyst_role;

-- 4. Create a specific user and assign them the role
CREATE USER bob WITH PASSWORD 'secure_pass';
GRANT analyst_role TO bob;

Granular Control via Revocation

Security isn't just about what you grant; it’s about what you deny. Because PostgreSQL roles inherit privileges, if you discover an over-provisioned role, you can use REVOKE to prune specific access points.

When you apply this logic, you shift from "everything is open" to "everything is blocked by default." By explicitly granting access only to the schema and tables required for a specific task, you ensure that even if an account is compromised, the attacker is trapped within the narrow scope of that user's assigned role.

Key Takeaway: RBAC allows you to define a "minimum viable privilege" set, ensuring users inherit only the specific schema and table access necessary to perform their functions, rather than relying on dangerous broad-spectrum permissions.