Role-Based Access Control (RBAC)

HeliosDB-Lite provides full role-based access control with table-level and column-level grants, role hierarchies, and delegation via WITH GRANT OPTION.

Prerequisites

HeliosDB-Lite v3.5+ running (embedded, REPL, or PostgreSQL wire protocol)
Superuser access for initial role setup

Step 1: Create Roles

Roles are the foundation of RBAC. Create roles with various options:

-- Basic role (no login by default)
CREATE ROLE analysts;

-- Role with login capability
CREATE ROLE app_user WITH LOGIN;

-- Superuser role
CREATE ROLE dba WITH SUPERUSER LOGIN;

-- Role with password
CREATE ROLE reporter WITH LOGIN PASSWORD 'secure_pass';

Expected output:

CREATE ROLE

Step 2: Set Up Test Data

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer TEXT,
    amount DECIMAL,
    region TEXT,
    internal_notes TEXT
);

INSERT INTO orders VALUES (1, 'Acme Corp', 1500.00, 'EMEA', 'Priority client');
INSERT INTO orders VALUES (2, 'Globex', 750.00, 'APAC', 'Payment pending');
INSERT INTO orders VALUES (3, 'Initech', 3200.00, 'AMER', 'Renewal due Q2');

Step 3: Grant Table-Level Privileges

Grant full CRUD access or specific operations:

-- Grant SELECT-only access to analysts
GRANT SELECT ON orders TO analysts;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;

-- Grant all privileges
GRANT ALL ON orders TO dba;

Expected output:

GRANT

Step 4: Grant Column-Level Privileges

Restrict access to specific columns when users should not see sensitive data:

-- analysts can read most columns but NOT internal_notes
GRANT SELECT (id, customer, amount, region) ON orders TO analysts;

-- app_user can update amount but not other fields
GRANT UPDATE (amount) ON orders TO app_user;

Step 5: Use WITH GRANT OPTION

Allow a grantee to pass their privileges to other roles:

-- dba can grant SELECT on orders to anyone
GRANT SELECT ON orders TO dba WITH GRANT OPTION;

When is_grantable is true, the grantee can run GRANT statements for that privilege on that table.

Step 6: Role Hierarchies

Assign roles to other roles to build a hierarchy:

CREATE ROLE senior_analyst WITH LOGIN;

-- senior_analyst inherits all privileges of analysts
GRANT analysts TO senior_analyst;

Now senior_analyst has every privilege granted to analysts plus any privileges granted directly to senior_analyst.

Step 7: Alter Role Settings

Configure per-role session parameters:

ALTER ROLE app_user SET statement_timeout = '30000';
ALTER ROLE analysts SET work_mem = '256MB';

Step 8: Revoke Privileges

Remove privileges when they are no longer needed:

-- Revoke INSERT from app_user
REVOKE INSERT ON orders FROM app_user;

-- Revoke column-level grant
REVOKE UPDATE (amount) ON orders FROM app_user;

-- Revoke all privileges
REVOKE ALL ON orders FROM analysts;

Expected output:

REVOKE

Step 9: Drop Roles

-- Drop a role (fails if it still owns objects)
DROP ROLE analysts;

-- Safe drop
DROP ROLE IF EXISTS analysts;

Step 10: Monitor with System Views

helios_grants — see all active privileges

SELECT * FROM helios_grants;

table_name	grantee	privilege	grantor	column_name	is_grantable
orders	dba	SELECT	admin		true
orders	dba	INSERT	admin		false
orders	app_user	SELECT	admin		false
orders	app_user	UPDATE	admin	amount	false

helios_roles — see all defined roles

SELECT * FROM helios_roles;

role_name	options	can_login	is_superuser
dba	SUPERUSER,LOGIN	true	true
app_user	LOGIN	true	false
senior_analyst	LOGIN	true	false

Common Pitfalls

Forgetting LOGIN — Roles without LOGIN cannot authenticate. Use CREATE ROLE name WITH LOGIN for user accounts.
Column grants don’t override table grants — A table-level SELECT already covers all columns. Column-level grants are useful when you have NOT granted the table-level privilege.
Role hierarchy is additive — Granting role A to role B gives B all of A’s privileges. There is no way to subtract inherited privileges; use separate roles instead.
REVOKE does not cascade by default — If user X granted a privilege to user Y via WITH GRANT OPTION, revoking from X does not automatically revoke from Y. Revoke from Y explicitly.
Plan cache invalidation — DDL changes (including GRANT/REVOKE) automatically invalidate the plan cache, so subsequent queries reflect the new privileges immediately.