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

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.