Role-Based Access Control: Privileges, Roles & Least Privilege

What Is Role-Based Access Control?

Role-Based Access Control (RBAC) is a security model in which permissions are assigned to roles rather than directly to individual users. A user then receives permissions by being made a member of a role. This approach keeps security policies consistent, easy to audit, and straightforward to change when job responsibilities shift. RBAC is a core concept in every major database platform and is tested on the PostgreSQL CE, Oracle DBA CA, Microsoft DP-300, and DAMA CDMP Associate exams.

The Principle of Least Privilege

Least privilege means every account or role is granted only the permissions it needs to perform its defined job — nothing more. A data-entry clerk who only inserts new patient intake records has no business deleting records or reading billing data. Granting excess privileges is one of the most common causes of data breaches, compliance violations (HIPAA, PCI-DSS), and accidental data loss. Always start with no privileges and add only what is explicitly required.

Safety note: In a production database, incorrectly granting elevated privileges — especially DELETE, TRUNCATE, or DROP — to an unprivileged role can lead to irreversible data loss. Before any privilege change on a live system, verify you are connected to the correct database, confirm the target role, and ensure a current backup exists. Treat a superuser session the way you would treat an energized electrical panel: use it only when necessary, close it immediately when done, and never leave a superuser psql session unattended.

PostgreSQL Roles and Inheritance

PostgreSQL uses a unified concept called a role for both users and groups. A role can log in (if `LOGIN` is granted) and can inherit privileges from parent roles (if `INHERIT` is set, which is the default).

```sql

-- Create a base role with no login

CREATE ROLE data_entry_clerk NOLOGIN;

-- Create a login account that belongs to that role

CREATE ROLE alice LOGIN PASSWORD 'changeme';

GRANT data_entry_clerk TO alice;

```

When Alice logs in, she automatically has whatever privileges `data_entry_clerk` holds, because inheritance is enabled by default.

GRANT and REVOKE

`GRANT` assigns a privilege on a database object to a role. `REVOKE` removes it.

```sql

-- Allow data_entry_clerk to insert into patients

GRANT INSERT ON patients TO data_entry_clerk;

-- Allow nurse_supervisor to select and update

GRANT SELECT, UPDATE ON patients TO nurse_supervisor;

-- Remove a privilege if it was granted in error

REVOKE DELETE ON patients FROM nurse_supervisor;

```

Common object-level privileges include `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, and `EXECUTE` (for functions). Schema-level `USAGE` must also be granted before a role can see objects inside a schema.

Role Hierarchy in the Hospital Scenario

For a hospital records database, a sensible hierarchy looks like this:

• data_entry_clerk — INSERT only on `patients`; no read access to clinical notes.
• nurse_supervisor — inherits `data_entry_clerk` privileges, plus SELECT and UPDATE on `patients` and `clinical_notes` for their ward.
• auditor — SELECT only, across all tables; no INSERT, UPDATE, or DELETE.
• dba_admin — full privileges; used only for schema changes and maintenance tasks, never for routine queries.

In practice, `dba_admin` maps to a role that is a member of the PostgreSQL built-in `pg_monitor` and has been granted explicit DDL rights — it is not the PostgreSQL superuser for day-to-day work.

Row-Level Security (RLS)

Object-level privileges control whether a role can touch a table at all. Row-Level Security (RLS) controls which rows within that table the role can see or modify. RLS must be explicitly enabled on each table:

```sql

-- Enable RLS on the patients table

ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

-- A policy that lets nurses see only their ward's rows

CREATE POLICY nurse_ward_policy ON patients

FOR SELECT

TO nurse_supervisor

USING (ward_id = current_setting('app.current_ward')::int);

```

The `USING` clause is evaluated for every row. Only rows where the expression is true are returned. If no policy applies to a role, the default behavior after enabling RLS is to return zero rows (a safe default).

Table owners and superusers bypass RLS by default. To make RLS apply to the table owner as well, use `ALTER TABLE patients FORCE ROW LEVEL SECURITY;`.

Testing With SET ROLE

During development you can impersonate a role within your session without logging out:

```sql

SET ROLE data_entry_clerk;

DELETE FROM patients WHERE patient_id = 1; -- should fail

RESET ROLE;

```

A correctly configured policy returns an error such as `ERROR: permission denied for table patients`. Document every error message during testing — the message itself is evidence that the policy is enforced.

Auditing and Logging

Always enable `pgaudit` or PostgreSQL's built-in `log_statement` parameter in a security-sensitive environment. Log all DDL and privilege changes. Without an audit trail, you cannot prove a policy was enforced at a given point in time — a requirement for HIPAA and other regulated environments.

Summary

RBAC in PostgreSQL rests on three pillars: well-designed roles with clear inheritance, precise `GRANT`/`REVOKE` statements, and Row-Level Security policies that limit row visibility. Applying least privilege at every layer — schema, table, and row — dramatically reduces your attack surface and supports compliance auditing.