# Security Rbac

> Database Security, RBAC, and Access Control policies Trigger: When implementing role-based access control.

- Skill: `odjaramillo/security-rbac` (Agent Skill)
- Install (CLI): `npx skillmds@latest add odjaramillo/security-rbac`
- Raw SKILL.md: https://api.skillmd.com/api/skills/odjaramillo/security-rbac/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- License: Apache-2.0
- Author: odjaramillo (https://skillmd.com/u/odjaramillo)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/odjaramillo/security-rbac

---


## Decision Tree

```
Need access control?       → Define ROLE hierarchy
Need row isolation?        → Enable RLS with policies
Need sensitive data?       → Encrypt with pgcrypto
Need audit compliance?     → Create AUDIT_LOG table
Need secure connections?   → Enforce SSL/TLS
```

---

# Database Security & RBAC Standards

Security must be implemented at the database layer (Defense in Depth), ensuring that even if the application layer is compromised, the data remains protected by strict access controls.

## 1. Role-Based Access Control (RBAC)

### 1.1 Standard Roles

Implement a hierarchy of roles to categorize users. Avoid assigning permissions to individual users; assign them to Roles.

- **`ROLE_ADMIN`:** Full DDL/DML access. Capable of altering schema.
- **`ROLE_APP_BACKEND`:** The role used by the API. Can `SELECT`, `INSERT`, `UPDATE`, `DELETE` on operational tables but cannot alter schema.
- **`ROLE_READ_ONLY`:** For reporting/analytics tools. `SELECT` only.
- **`ROLE_GUEST` / `ROLE_ANON`:** For unauthenticated public access (if applicable).

### 1.2 Granting Permissions

- **Least Privilege:** Start with **no** permissions. Grant only what is absolutely necessary.
- **Grant Statements:**
  ```sql
  GRANT SELECT, INSERT, UPDATE ON TABLE users TO ROLE_APP_BACKEND;
  GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO ROLE_APP_BACKEND;
  ```
- **Revoke:** Explicitly `REVOKE ALL` from `PUBLIC` on sensitive tables to prevent accidental default access.

## 2. Row Level Security (RLS)

Use RLS to strictly enforce data isolation at the row level based on the current user context.

### 2.1 Implementing RLS

1.  **Enable RLS:**
    ```sql
    ALTER TABLE sensitive_documents ENABLE ROW LEVEL SECURITY;
    ```
2.  **Create Policy:**
    ```sql
    CREATE POLICY user_access_policy ON sensitive_documents
    FOR ALL
    USING (owner_id = current_setting('app.current_user_id')::INT);
    ```

### 2.2 Application Context

Ensure the application sets the context variable (e.g., `app.current_user_id`) at the start of every transaction to allow RLS to function correctly.

## 3. Data Protection

### 3.1 Sensitive Data

- **Passwords:** NEVER store plain-text passwords. Use `bcrypt` or `argon2` hashes.
- **PII:** Identify Personally Identifiable Information (Emails, Phones, IDs).
  - Consider separate schemas or tables for PII with stricter access controls.
  - Encrypt highly sensitive columns (e.g., Credit Card tokens) at rest if the database supports it (e.g., `pgcrypto`).

### 3.2 SQL Injection Prevention

- **Prepared Statements:** ALL application code must use Parameterized Queries/Prepared Statements.
- **Dynamic SQL:** In PL/pgSQL, use `EXECUTE ... USING ...` to safely bind parameters in dynamic strings. Avoid simple string concatenation.

## 4. Audit & Compliance

### 4.1 Audit Logging Tables

Track sensitive operations for compliance (GDPR, SOC2, etc.).

- Log: WHO changed WHAT, WHEN, and the OLD/NEW values.
- Store in a separate `audit` schema with restricted access.

### 4.2 Immutable Logs

- Make audit tables append-only: revoke `UPDATE` and `DELETE` from all application roles.
- Consider log shipping or write-ahead log archiving for tamper-proofing.

## 5. Connection Security

### 5.1 Connection Pooling

- Application connections should use a pool (e.g., PgBouncer).
- Each pool should connect using the `ROLE_APP_BACKEND` role, not a superuser.

### 5.2 SSL/TLS

- Enforce encrypted connections: `sslmode=require` or `verify-full`.
- Ensure certificates are properly managed and rotated.