Overview Phase 1 — Schema & Load Phase 2 — Diagnostics Phase 3 — Optimization Phase 4 — Operations Phase 5 — Security
Phase 05 — Security & Compliance

Securing the Database
at Every Layer

Given that this project uses CMS Medicare data — the same category of data that falls under HIPAA in production environments — I treated security as a first-class concern. I implemented role-based access control, row-level security, and trigger-based audit logging covering authentication, authorization, data visibility, and audit trail.

RBAC Least Privilege Row-Level Security Audit Logging Security Assessment
Role-Based Access Control

Four Roles, Least Privilege

The Design Principle

Every role has exactly the privileges it needs — nothing more. I created four group roles representing distinct job functions, then four login users assigned to those roles. No application user has superuser privileges. No user can escalate their own access.

Screenshot — Role inventory
DataGrip showing all eight roles — four group roles and four login users with their privilege flags
Eight roles confirmed — four group roles (no login) and four login users. No unexpected superuser or create-role privileges.
dba_admin
→ naseer_dba
Full control over the healthcare_dba database, schema, tables, and sequences. Not a superuser — cannot modify system-level PostgreSQL configuration.
analyst
→ jane_analyst
SELECT on all cms tables. Read-only access for reporting and analytics — cannot modify any data.
data_engineer
→ etl_engineer
Full DML on staging_raw only. Cannot modify providers or provider_services directly — must go through the controlled ETL process.
app_readonly
→ app_user
SELECT on providers and provider_services only. No access to staging_raw. Further restricted by Row-Level Security policies.
Row-Level Security

95% Data Restriction,
Zero Query Changes

How RLS Works Here

I enabled Row-Level Security on cms.providers and created a user_state_access table that maps users to the states they are permitted to see. The app_user account is restricted to Virginia and Maryland only. This restriction is enforced transparently by the database engine — the user cannot bypass it by changing how their query is written.

Superuser sees
1,175,281
All providers nationwide
app_user sees
54,351
Virginia & Maryland only
Screenshot — RLS test results
Query results showing 1,175,281 total providers vs 54,351 visible to app_user
Same query, same table — superuser sees 1,175,281 rows, app_user sees 54,351. The restriction is enforced by the database, not the application.

RLS Policies

PolicyRoleRule
app_state_accessapp_readonlyRows where state_abbr matches user's assigned states
analyst_full_accessanalystAll rows visible
dba_full_accessdba_adminAll rows, all commands
Audit Logging

Every Change, Captured

Trigger-Based Audit Trail

I created a dedicated audit schema with a data_access_log table that captures every INSERT, UPDATE, and DELETE on sensitive tables. The trigger function uses SECURITY DEFINER so it runs with the privileges of the function owner regardless of which user triggers it — meaning no user can bypass the audit log by manipulating their own permissions.

Each audit record captures: timestamp to microsecond precision, database user, client IP address, application name, schema and table affected, operation type, and full before/after data stored as JSONB.

Screenshot — Audit log entries
DataGrip showing audit log entries with timestamps, user, table, operation, and before/after data
Two UPDATE operations captured in the audit log — who performed them, when, which NPI was affected, and what changed

Audit Log Structure

ColumnTypePurpose
log_timestampTIMESTAMPTZMicrosecond-precision event time
db_userVARCHARPostgreSQL user who made the change
client_addrINETIP address of the connection
applicationVARCHARApplication name from connection string
operationVARCHARINSERT, UPDATE, or DELETE
old_dataJSONBComplete before-state for UPDATE/DELETE
new_dataJSONBComplete after-state for INSERT/UPDATE
Security Summary

Four Layers of Protection

Layer 1
Authentication
Separate login users with strong passwords, no shared credentials, role-based assignment. Four distinct login users, each assigned to a specific role. No application user has superuser or create-role privileges.
Layer 2
Authorization
Least-privilege role design ensuring each user can only perform the operations their job requires. data_engineer can only write to staging_raw. app_readonly cannot touch staging_raw at all. Every privilege grant is intentional and documented.
Layer 3
Data Visibility
Row-Level Security restricting what rows application users can see, enforced transparently by the database engine. app_user sees 54,351 of 1,175,281 providers — a 95% restriction enforced regardless of query structure.
Layer 4
Audit Trail
Trigger-based logging capturing every data modification with full context. Who, when, what changed — stored in a dedicated audit schema with indexes for efficient compliance querying. In a production environment this is the foundation for HIPAA audit reporting.
Project Complete

Five phases. Schema design, performance baselining, optimization, backup and recovery, and security. Every decision documented, every result measured, every script versioned. The full source code and documentation are on GitHub.

View on GitHub Project Overview
Phase 4 Project Overview