insforge-cli/references/database/access-control.md

Database Access Control for InsForge

Overview

Row Level Security (RLS) provides defense-in-depth for data isolation. When implemented correctly, it prevents data leaks even if application code misses a filter. When implemented incorrectly, it creates false security confidence while data bleeds between users or tenants.

Core principle: RLS is your last line of defense, not your only one. Get it wrong and you have a data breach.

---

InsForge RLS Basics

InsForge uses three built-in PostgreSQL roles:

Role	Description	When active
anon	Unauthenticated users	No valid session token
authenticated	Logged-in users	Valid session token present
project_admin	Project admin	CLI db query, migrations, API-key/admin tasks

The current user's ID is available via auth.uid(). All user foreign keys should reference auth.users(id).

Raw SQL from db query and migration files runs as project_admin. This role can manage and own objects in public; access to InsForge-managed schemas is restricted.

Schema Scope and Managed Modules

For generic application database work, create and modify app-owned objects in the public schema.

• Create, alter, drop, grant, revoke, index, trigger, function, view, and policy changes on public application objects.
• Do not create custom schemas or write to InsForge-managed/system schemas such as auth, storage, realtime, payments, graphql, extensions, pg_catalog, information_schema, or system, unless you are working on that specific feature module and its docs explicitly allow the operation.
• It is allowed to reference built-in objects such as auth.users(id) and auth.uid() from public tables or public RLS policies; do not modify those built-in objects.
• Put RLS helper functions in public, schema-qualify references such as public.team_members and auth.uid(), and pin SECURITY DEFINER helpers to SET search_path = pg_catalog, public, pg_temp.
• InsForge migrations already run against public; schema-qualified references keep helper functions explicit.

Managed table RLS belongs to the corresponding storage, realtime, or payments feature context. Use those feature docs when the task is specifically about those modules.

Minimal RLS Setup

-- 1. Create table
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- 3. Create policies
CREATE POLICY "anyone can read" ON posts
  FOR SELECT TO anon, authenticated
  USING (true);

CREATE POLICY "owners can insert" ON posts
  FOR INSERT TO authenticated
  WITH CHECK (user_id = (SELECT auth.uid()));

CREATE POLICY "owners can update" ON posts
  FOR UPDATE TO authenticated
  USING (user_id = (SELECT auth.uid()))
  WITH CHECK (user_id = (SELECT auth.uid()));

CREATE POLICY "owners can delete" ON posts
  FOR DELETE TO authenticated
  USING (user_id = (SELECT auth.uid()));

-- 4. Grant SQL privileges to the roles that should pass through the policies
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT SELECT ON posts TO anon, authenticated;
GRANT INSERT, UPDATE, DELETE ON posts TO authenticated;

-- 5. Auto-update updated_at
CREATE TRIGGER posts_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION system.update_updated_at();

Policies decide which rows a role may access after PostgreSQL has allowed the SQL operation. They do not grant SELECT, INSERT, UPDATE, or DELETE privileges.

InsForge grants broad DML privileges on public tables to anon and authenticated by default so RLS policies can decide row-level access. When the goal is narrower than the default operation or column surface, explicitly revoke the broad privilege before granting the exact access you want:

REVOKE UPDATE ON public.posts FROM anon, authenticated;
GRANT UPDATE (title) ON public.posts TO authenticated;

If you revoke a privilege, a matching policy is no longer enough by itself; the role still needs the operation or column grant to reach the policy.

Design the Operation Surface First

Before writing policies, decide what each runtime role may do at the SQL operation level. RLS answers "which rows"; privileges and guards answer "which operations and columns".

For each table, list:

Operation	Typical access-control question
SELECT	Who may see full rows, and who only sees a projection?
INSERT	Which user identity or tenant must new rows belong to?
UPDATE	Which rows may be edited, and which fields must remain immutable?
DELETE	Is deletion allowed, or should lifecycle state/soft delete be modeled?

If an operation or field is narrower than InsForge's broad public-table runtime privileges, revoke the broad privilege first, then grant back the exact surface.

Guard Protected Fields Outside RLS Predicates

RLS policies filter candidate rows and validate the final row with WITH CHECK. They do not compare old and new column values. PostgreSQL policy expressions do not have OLD or NEW.

For protected fields such as owner_id, tenant_id, role columns, immutable foreign keys, billing fields, or status fields, use column privileges and/or a BEFORE UPDATE trigger guard. This keeps invariants true even if a future policy or grant becomes broader.

REVOKE UPDATE ON public.documents FROM anon, authenticated;
GRANT UPDATE (title, body) ON public.documents TO authenticated;

CREATE OR REPLACE FUNCTION public.prevent_document_owner_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.owner_id IS DISTINCT FROM OLD.owner_id THEN
    RAISE EXCEPTION 'owner_id cannot be changed';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER prevent_document_owner_change
BEFORE UPDATE ON public.documents
FOR EACH ROW EXECUTE FUNCTION public.prevent_document_owner_change();

For field-level update masks such as "members may edit content, managers may edit status, finance may edit billing code", use a BEFORE UPDATE trigger to compare OLD and NEW; use RLS to decide whether the caller can reach the row.

Model ACLs as Positive Capabilities

For owner/editor/viewer/member sharing systems, avoid one broad FOR ALL policy. Write separate policies for each operation and express the positive capability needed for that operation.

• SELECT: owner or active read/edit share.
• UPDATE: owner or active edit share, with protected owner/tenant fields guarded separately.
• DELETE: usually owner/admin only.
• Share mutation: usually owner/admin only; viewers and editors should not reshare, revoke, or escalate themselves unless that is explicitly intended.

Cross-table ACL lookups commonly touch RLS-enabled tables. Put those lookups in SECURITY DEFINER helpers, pin their search_path, and schema-qualify referenced objects.

Separate Private Base Tables from Public Projections

When a table contains private JSON, billing fields, internal notes, or other sensitive columns, keep full-row base-table access narrow. Expose public data through a view or function that projects only safe fields.

Do not make the base table readable by everyone just to make a public view work. That exposes the full row through direct table reads. If callers need a public projection, design the projection explicitly and grant access to the projection, not the private base table.

WITH (security_invoker = true) makes a PostgreSQL 15+ view respect the caller's RLS on the base tables. Use it when the base-table RLS already allows the rows the view should expose. If the public projection intentionally exposes a subset of fields from rows whose full base rows are private, use a carefully projected view/function and keep direct base-table privileges and policies narrow.

---

Critical Vulnerabilities

1. Infinite Recursive RLS (CRITICAL — Causes OOM Crash)

This is the most dangerous RLS bug. When RLS policies on table A call a function that queries table B, and table B's RLS calls a function that queries table A (or itself), PostgreSQL enters infinite recursion until the server runs out of memory and is killed by the OS.

Real-world example:

companies → is_company_member() → queries company_memberships
                                     → RLS on company_memberships
                                     → is_company_consultant_or_admin()
                                     → company_role()
                                     → queries company_memberships (LOOP!)
                                     → OOM → SIGKILL

How to detect:

• Database connection hangs, then the server crashes
• PostgreSQL logs show SIGKILL or out-of-memory errors
• EXPLAIN on the query runs forever

The fix — use SECURITY DEFINER:

-- DANGEROUS: This function runs as the calling role, so RLS is enforced
-- on every table it touches — creating recursion risk
CREATE OR REPLACE FUNCTION is_company_member(company_uuid UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM company_memberships
    WHERE company_id = company_uuid AND user_id = auth.uid()
  );
$$ LANGUAGE sql STABLE;

-- SAFE: SECURITY DEFINER runs as the function owner (postgres),
-- bypassing RLS on queried tables and breaking the recursion
CREATE OR REPLACE FUNCTION is_company_member(company_uuid UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.company_memberships
    WHERE company_id = company_uuid AND user_id = auth.uid()
  );
$$ LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = pg_catalog, public, pg_temp;

Rule: Any helper function called from an RLS policy should be SECURITY DEFINER when it queries RLS-enabled tables. This includes same-table lookups, parent/ancestor lookups, membership tables, ACL/share tables, and helper chains that would otherwise re-enter RLS. Keep helpers in public, use explicit schema-qualified references, and pin the function search_path to pg_catalog, public, pg_temp.

Checklist:

• Map all RLS policy → function → table dependencies
• Every policy helper that queries RLS-enabled tables, including same-table lookups, is SECURITY DEFINER
• Every SECURITY DEFINER helper sets search_path to pg_catalog, public, pg_temp
• Helper functions and policies schema-qualify app tables/functions with public. and built-ins with their managed schema, such as auth.uid()
• No circular chains: table A RLS → table B RLS → table A RLS
• If recursion or bad plans are suspected, use targeted EXPLAIN

2. Missing USING or WITH CHECK (HIGH)

USING filters reads; WITH CHECK validates writes. Missing WITH CHECK allows inserting rows you can't read back.

-- INCOMPLETE: User can INSERT rows for other users
CREATE POLICY "owner access" ON posts
  FOR ALL USING (user_id = auth.uid());

-- COMPLETE: Both read and write protected
CREATE POLICY "owner access" ON posts
  FOR ALL
  USING (user_id = (SELECT auth.uid()))
  WITH CHECK (user_id = (SELECT auth.uid()));

Checklist:

• INSERT/UPDATE policies always include WITH CHECK
• FOR ALL policies include both USING and WITH CHECK

3. Overly Permissive Policies (HIGH)

Multiple policies on the same table are combined with OR. One overly broad policy defeats all others.

-- DANGEROUS: This single policy overrides all restrictions
CREATE POLICY "allow all reads" ON orders
  FOR SELECT USING (true);

CREATE POLICY "tenant isolation" ON orders
  FOR SELECT USING (tenant_id = (SELECT auth.uid()));
-- ^ This is useless — the first policy already allows everything

Checklist:

• Audit all policies per table — they combine with OR
• No USING (true) on sensitive tables unless intentional (e.g., public blog posts)

4. View Bypass (MEDIUM)

Views run with the creator's privileges by default.

-- DANGEROUS: View owned by superuser bypasses RLS
CREATE VIEW all_orders AS SELECT * FROM orders;

-- SAFE (PostgreSQL 15+): Respects caller's RLS
CREATE VIEW user_orders
WITH (security_invoker = true)
AS SELECT * FROM orders;

---

Performance Considerations

Index Policy Columns

Every column referenced in an RLS policy should be indexed:

CREATE INDEX idx_posts_user_id ON posts(user_id);

Wrap Functions in Subqueries

Functions called per-row are expensive. Wrap in a subquery for single evaluation:

-- SLOW: auth.uid() called per row
CREATE POLICY "owner access" ON posts
  USING (user_id = auth.uid());

-- FASTER: Evaluated once
CREATE POLICY "owner access" ON posts
  USING (user_id = (SELECT auth.uid()));

Use SECURITY DEFINER for Cross-Table Checks

Avoid RLS-on-RLS chains (see Infinite Recursive RLS above). Wrap cross-table lookups in SECURITY DEFINER functions:

CREATE OR REPLACE FUNCTION user_accessible_document_ids(uid UUID)
RETURNS SETOF UUID AS $$
  SELECT document_id FROM public.permissions WHERE user_id = uid;
$$ LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = pg_catalog, public, pg_temp;

CREATE POLICY "access check" ON documents
  USING (id IN (SELECT * FROM user_accessible_document_ids((SELECT auth.uid()))));

Denormalize for Performance

Store user_id or tenant_id directly on every table instead of relying on joins:

-- SLOW: Must join to resolve ownership
CREATE POLICY "item access" ON order_items
  USING (order_id IN (
    SELECT id FROM orders WHERE user_id = auth.uid()
  ));

-- FAST: Direct column check
ALTER TABLE order_items ADD COLUMN user_id UUID REFERENCES auth.users(id);
CREATE POLICY "item access" ON order_items
  USING (user_id = (SELECT auth.uid()));

---

Common InsForge RLS Patterns

Public Read, Owner Write

CREATE POLICY "public read" ON posts
  FOR SELECT TO anon, authenticated
  USING (true);

CREATE POLICY "owner write" ON posts
  FOR INSERT TO authenticated
  WITH CHECK (user_id = (SELECT auth.uid()));

CREATE POLICY "owner update" ON posts
  FOR UPDATE TO authenticated
  USING (user_id = (SELECT auth.uid()))
  WITH CHECK (user_id = (SELECT auth.uid()));

CREATE POLICY "owner delete" ON posts
  FOR DELETE TO authenticated
  USING (user_id = (SELECT auth.uid()));

GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT SELECT ON posts TO anon, authenticated;
GRANT INSERT, UPDATE, DELETE ON posts TO authenticated;

Role-Based Access with Helper Function

CREATE OR REPLACE FUNCTION is_org_member(org_uuid UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.org_members
    WHERE org_id = org_uuid AND user_id = auth.uid()
  );
$$ LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = pg_catalog, public, pg_temp;  -- prevents recursive RLS and pins name resolution

CREATE POLICY "org members access" ON projects
  FOR ALL TO authenticated
  USING (is_org_member(org_id))
  WITH CHECK (is_org_member(org_id));

GRANT USAGE ON SCHEMA public TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO authenticated;

Authenticated-Only Access

CREATE POLICY "authenticated users only" ON profiles
  FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) IS NOT NULL);

GRANT USAGE ON SCHEMA public TO authenticated;
GRANT SELECT ON profiles TO authenticated;

---

Checklist

Before completing an RLS implementation:

• All tables with user data have ALTER TABLE ... ENABLE ROW LEVEL SECURITY
• Matching SQL privileges are granted to anon/authenticated (GRANT USAGE ON SCHEMA ..., GRANT SELECT/INSERT/UPDATE/DELETE ON ...)
• All policies have both USING and WITH CHECK where applicable
• Protected owner, tenant, role, and identity fields are guarded with column privileges or triggers, not only RLS predicates
• No circular RLS dependencies between tables (infinite recursion risk)
• All policy helpers that query RLS-enabled tables are SECURITY DEFINER
• Helper functions pin search_path to pg_catalog, public, pg_temp
• Helper functions and policies use explicit public. and managed-schema references instead of relying on search_path
• Broad default table privileges are revoked before narrower operation or column grants
• Policy columns (user_id, tenant_id, etc.) are indexed
• (SELECT auth.uid()) used in subquery form for performance
• Public projections do not expose private base-table rows; view/function grants are separated from full table access
• No overly permissive USING (true) on sensitive tables
• Runtime behavior is not inferred from project_admin-only queries

References

• PostgreSQL RLS Documentation
• SECURITY DEFINER Functions