Skip to main content

Mapping RBAC + ABAC into the Schema

This document explains how RBAC and ABAC attributes map to the PostgreSQL schema and how they're used for access control.

RBAC Mapping​

Users → users table​

INSERT INTO users (user_id, display_name, email, business_unit, facility, active)
VALUES ('u123', 'Jane Doe', 'jane.doe@hospital.org', 'Compliance', 'Hospital-A', TRUE);

Roles → roles table​

INSERT INTO roles (role_id, description) VALUES
  ('clinician', 'Clinical staff with patient access'),
  ('privacy_officer', 'HIPAA Privacy Officer'),
  ('compliance_officer', 'Compliance team member'),
  ('trader', 'Trading desk staff'),
  ('finra_compliance', 'FINRA compliance team'),
  ('records_manager', 'Records and retention management'),
  ('admin', 'System administrator');

Permissions → permissions table​

INSERT INTO permissions (permission_id, description) VALUES
  ('doc.read', 'Read document content'),
  ('doc.write', 'Create or modify documents'),
  ('doc.delete', 'Delete documents'),
  ('doc.approve', 'Approve document workflow'),
  ('doc.view_phi', 'View PHI-classified documents'),
  ('doc.view_financial', 'View financial records'),
  ('doc.manage_retention', 'Modify retention settings'),
  ('doc.place_hold', 'Apply legal holds'),
  ('audit.view', 'View audit logs'),
  ('admin.manage_users', 'Manage user accounts'),
  ('admin.manage_roles', 'Manage roles and permissions');

Role-Permission Assignments​

-- Privacy Officer permissions
INSERT INTO role_permissions (role_id, permission_id) VALUES
  ('privacy_officer', 'doc.read'),
  ('privacy_officer', 'doc.write'),
  ('privacy_officer', 'doc.approve'),
  ('privacy_officer', 'doc.view_phi'),
  ('privacy_officer', 'doc.place_hold'),
  ('privacy_officer', 'audit.view');

-- Clinician permissions
INSERT INTO role_permissions (role_id, permission_id) VALUES
  ('clinician', 'doc.read'),
  ('clinician', 'doc.view_phi');

-- Trader permissions
INSERT INTO role_permissions (role_id, permission_id) VALUES
  ('trader', 'doc.read'),
  ('trader', 'doc.write'),
  ('trader', 'doc.view_financial');

ABAC Mapping​

ABAC conditions use attributes from both the user and the document.

Document Attributes (from document_metadata)​

AttributeFieldExample
Domaindomain"security-privacy", "clinical", "finance"
Typedocument_type"policy", "procedure", "record"
Classificationsecurity_class"confidential", "restricted"
Contains PHIcontains_phitrue/false
Contains Financialcontains_financialtrue/false
Business Unitbusiness_unit"Compliance", "Oncology"
Deskdesk"Institutional Sales"
Facilityfacility"Hospital-A"
Regulationsregulations["HIPAA-164.316", "FINRA-4511"]

User Attributes (from users)​

AttributeFieldExample
Business Unitbusiness_unit"Compliance"
Facilityfacility"Hospital-A"
Deskdesk"Institutional Sales"

ABAC Policy Hints (from document_access_attributes)​

-- Document requiring specific roles
INSERT INTO document_access_attributes (doc_id, required_roles, allowed_facilities)
VALUES ('550e8400-...', ARRAY['privacy_officer'], ARRAY['Hospital-A', 'Hospital-B']);

-- Document with desk restriction
INSERT INTO document_access_attributes (doc_id, allowed_desks)
VALUES ('550e8400-...', ARRAY['Institutional Sales', 'Equities']);

Authorization Flow​

At Request Time​

  1. Decode JWT → get user_id, tenant
  2. Load user attributes from users
  3. Load user roles from user_roles
  4. Load role permissions from role_permissions
  5. Build subject claims:
{
  "subject": {
    "user_id": "u123",
    "roles": ["privacy_officer", "clinician"],
    "permissions": ["doc.read", "doc.write", "doc.view_phi", ...],
    "business_unit": "Compliance",
    "facility": "Hospital-A"
  }
}

For Each Document​

  1. Load document metadata from document_metadata
  2. Load ABAC hints from document_access_attributes
  3. Build resource claims:
{
  "resource": {
    "doc_id": "d0001",
    "domain": "security-privacy",
    "document_type": "policy",
    "jurisdiction": ["US"],
    "regulations": ["HIPAA-164.316"],
    "security_class": "confidential",
    "contains_phi": true,
    "contains_financial": false,
    "business_unit": "Compliance",
    "facility": "Hospital-A"
  }
}
  1. Call PDP with (subject, action, resource)

Example Policy Rules (Rego/OPA)​

package docaccess

default allow = false

# PHI access requires permission and facility match
allow {
    input.action == "doc.read"
    input.resource.contains_phi == true
    input.subject.permissions[_] == "doc.view_phi"
    input.subject.facility == input.resource.facility
}

# Trading docs require desk match
allow {
    input.action == "doc.read"
    input.resource.domain == "finance"
    input.subject.roles[_] == "trader"
    input.subject.desk in input.resource.allowed_desks
}

# Compliance officers can read all
allow {
    input.action == "doc.read"
    input.subject.roles[_] == "compliance_officer"
}

# Privacy officer can approve security-privacy docs
allow {
    input.action == "doc.approve"
    input.resource.domain == "security-privacy"
    input.subject.roles[_] == "privacy_officer"
}

For list/search APIs, generate filter expressions:

def build_search_filter(user):
    """Build Meilisearch filter from user attributes."""
    filters = []

    # If user can view PHI, allow PHI docs in their facility
    if 'doc.view_phi' in user['permissions']:
        filters.append(f'(contains_phi = false OR facility = "{user["facility"]}")')
    else:
        filters.append('contains_phi = false')

    # If user is trader, filter to their desk
    if 'trader' in user['roles']:
        desks = user.get('allowed_desks', [user['desk']])
        desk_filter = ' OR '.join([f'desk = "{d}"' for d in desks])
        filters.append(f'({desk_filter})')

    return ' AND '.join(filters)

References​