Access Policy Designer Protocol

This skill enforces Zero Trust at the database layer. Instead of relying purely on the application backend to filter WHERE tenant_id = ?, it pushes security down to the database engine to prevent data leaks.

Core assumption: Application code has bugs. Database security policies (RLS/Views) are the final, unbreakable safety net against SQL Injection or logic flaws.

---

0. Context Intake

Before designing, make sure you have the inputs below. Read the previous workflow step's artifact first if it exists. Ask only for what is missing, in a single message, and state any assumption you make instead of blocking.

• Database engine and platform (PostgreSQL, Supabase, MySQL) and how the app connects (per-user role or shared role with session settings).
• Business access rules in plain language (who may read or change which rows or columns).
• Schema or schema-architect-output.json, including tenant and ownership columns.

1. Requirement Translation

Convert business rules into technical access models. Business Rule: "Doctors can only see their own patients' records." Translation: We need Row-Level Security (RLS) on the patients table where primary_doctor_id = current_user_id().

2. Policy Paradigms

Row-Level Security (RLS) - PostgreSQL / Supabase

• Define policies for SELECT, INSERT, UPDATE, and DELETE.
• Distinguish between Who you are (Current Role/User) and What you belong to (Tenant/Organization ID).

Column-Level Security (CLS)

• Restricting SELECT on specific columns.
• E.g., The Customer Support role can see the users table but cannot SELECT ssn or salary.

3. Output Generation

Generate platform-specific DDL for security policies.

Outputs. In file mode — the user wants artifacts, or this skill runs as a step of an ecosystem workflow — write both files below to docs/database-report/. In inline mode — a quick question — answer in the chat and end with the JSON below as a Handoff block instead of creating files.

1. Human-Readable Markdown (docs/database-report/access-policy-report.md)

### 🔒 Security Design: Patient Records

**Business Rule:** Doctors only access their assigned patients.

**SQL Implementation (PostgreSQL RLS):**

```sql
-- 1. Enable RLS on the table
ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

-- 2. Create the SELECT policy
CREATE POLICY doctor_select_own_patients 
ON patients FOR SELECT 
TO qualified_doctors
USING (primary_doctor_id = current_setting('app.current_user_id')::uuid);

-- 3. Create the UPDATE policy (Must belong to them and remain assigned to them)
CREATE POLICY doctor_update_own_patients 
ON patients FOR UPDATE 
TO qualified_doctors
USING (primary_doctor_id = current_setting('app.current_user_id')::uuid)
WITH CHECK (primary_doctor_id = current_setting('app.current_user_id')::uuid);
```

### ⚠️ Security Checklist (Audit)
- [x] Does the policy account for "Superadmins" who need to see everything?
- [x] Is the `app.current_user_id` setting securely injected by the backend connection?

2. Machine-Readable JSON (docs/database-report/access-policy-output.json)

{
  "skill": "access-policy-designer",
  "target_table": "patients",
  "dialect": "PostgreSQL",
  "policies": [
    {"name": "doctor_select_own_patients", "action": "SELECT", "role": "qualified_doctors"},
    {"name": "doctor_update_own_patients", "action": "UPDATE", "role": "qualified_doctors"}
  ],
  "rls_enabled": true
}

---

When to Skip

• Access is enforced only in a single trusted service with no direct database access by users, and the user did not ask for defense in depth.

Guardrails

• Infinite Recursion: If a policy on users queries the users table to check a role, it will infinite-loop. Restrict policy lookups or use a separate user_roles mapping table.
• Performance Tax: Complex RLS policies (e.g., USING (EXISTS (SELECT 1 FROM ...))) execute on every row read. Warn the user if a policy will cause a sequential scan.
• Bypass Clauses: Ensure the policy explicitly handles BypassRLS roles (like system migrations or background workers).

🔗 Next Steps & Handoffs

<!-- BEGIN GENERATED: handoffs (generated from the ecosystem workflow map by build_ecosystems.py — do not edit by hand) -->

Ecosystem: @ecosystem-database — Database Domain.

Workflows:

• New Schema Design Flow (db-new-schema, step 3 of 4): next → @seed-data-generator.

Handoff contract: pass results to the next skill through docs/database-report/access-policy-output.json with the fields skill, workflow, created_at, inputs, summary, and next (the handoff contract of @ecosystem-database). If a next skill is not installed, continue with its step from the ecosystem map, or install it with npx skills add fatih-developer/fth-skills --skill <name> after the user agrees.

<!-- END GENERATED: handoffs -->