# RLS Infinite Recursion Fix Documentation

**Date**: 2025-10-04
**Issue**: PostgreSQL Error 42P17 - Infinite Recursion in RLS Policies
**Status**: ✅ RESOLVED

---

## Problem Description

The vault-service experienced infinite recursion errors when querying the `vault_entries` and `sharing_permissions` tables due to circular dependencies in Row-Level Security (RLS) policies.

### Error Details
- **PostgreSQL Error Code**: 42P17
- **Error Message**: "infinite recursion detected in policy for relation"
- **Affected Tables**: `vault_entries`, `sharing_permissions`

### Root Cause
The circular dependency was created by:

1. **vault_entries RLS policies** query `sharing_permissions` table to check if user has shared access:
   ```sql
   -- Example from vault_entries SELECT policy
   EXISTS (
     SELECT 1 FROM sharing_permissions sp
     WHERE sp.entry_id = vault_entries.id
       AND sp.shared_with_user_id = auth.uid()
   )
   ```

2. **sharing_permissions RLS policy** "Users with admin permission can manage sharing" queries `vault_entries` table:
   ```sql
   -- Original problematic policy
   EXISTS (
     SELECT 1 FROM vault_entries ve
     WHERE ve.id = sharing_permissions.entry_id
       AND ve.user_id = auth.uid()
   )
   ```

3. **Infinite Loop**: When a query on `vault_entries` triggers its RLS policy, which queries `sharing_permissions`, which triggers its RLS policy, which queries `vault_entries` again, creating infinite recursion.

---

## Solution: SECURITY DEFINER Functions

We broke the circular dependency by creating helper functions with `SECURITY DEFINER` that bypass RLS when checking ownership and permissions.

### Step 1: Create Helper Functions

#### Function 1: `user_owns_vault_entry`
Checks if a user owns a vault entry, bypassing RLS.

```sql
CREATE OR REPLACE FUNCTION public.user_owns_vault_entry(entry_uuid uuid, user_uuid uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER  -- Bypasses RLS
SET search_path = public
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM vault_entries
    WHERE id = entry_uuid AND user_id = user_uuid
  );
$$;

GRANT EXECUTE ON FUNCTION public.user_owns_vault_entry(uuid, uuid) TO authenticated;
```

**Key Points**:
- `SECURITY DEFINER`: Function runs with privileges of the function creator (bypasses RLS)
- `STABLE`: Function does not modify database, can be optimized by PostgreSQL
- `SET search_path = public`: Security best practice to prevent search path attacks

#### Function 2: `user_has_admin_permission`
Checks if a user has admin permission on an entry, bypassing RLS.

```sql
CREATE OR REPLACE FUNCTION public.user_has_admin_permission(entry_uuid uuid, user_uuid uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER  -- Bypasses RLS
SET search_path = public
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM sharing_permissions
    WHERE entry_id = entry_uuid
      AND shared_with_user_id = user_uuid
      AND permission_level = 'admin'
      AND (expires_at IS NULL OR expires_at > now())
  );
$$;

GRANT EXECUTE ON FUNCTION public.user_has_admin_permission(uuid, uuid) TO authenticated;
```

### Step 2: Update RLS Policy

Updated the `sharing_permissions` policy to use the helper functions instead of directly querying `vault_entries`.

```sql
DROP POLICY IF EXISTS "Users with admin permission can manage sharing" ON sharing_permissions;

CREATE POLICY "Users with admin permission can manage sharing"
ON sharing_permissions
FOR ALL
TO public
USING (
  -- User owns the vault entry (use SECURITY DEFINER function to bypass RLS)
  public.user_owns_vault_entry(entry_id, auth.uid())
  OR
  -- User has admin permission (use SECURITY DEFINER function to bypass RLS)
  public.user_has_admin_permission(entry_id, auth.uid())
)
WITH CHECK (
  public.user_owns_vault_entry(entry_id, auth.uid())
  OR
  public.user_has_admin_permission(entry_id, auth.uid())
);
```

**Key Changes**:
- ❌ **Before**: Policy directly queried `vault_entries` table (triggered RLS recursion)
- ✅ **After**: Policy calls `user_owns_vault_entry()` function (bypasses RLS, no recursion)

---

## How It Works

### Before Fix (Circular Dependency)
```
User queries vault_entries
  ↓
vault_entries RLS policy checks sharing_permissions
  ↓
sharing_permissions RLS policy checks vault_entries
  ↓
vault_entries RLS policy checks sharing_permissions
  ↓
∞ INFINITE RECURSION ∞
```

### After Fix (Broken Circular Dependency)
```
User queries vault_entries
  ↓
vault_entries RLS policy checks sharing_permissions
  ↓
sharing_permissions RLS policy calls user_owns_vault_entry()
  ↓
SECURITY DEFINER function bypasses RLS
  ↓
✓ Direct query to vault_entries (no RLS evaluation)
  ↓
✓ Returns result (no recursion)
```

---

## Verification

### Test Results
All tests passed successfully without infinite recursion errors:

✅ vault_entries COUNT queries
✅ vault_entries SELECT queries
✅ sharing_permissions COUNT queries
✅ sharing_permissions SELECT queries
✅ SECURITY DEFINER helper functions
✅ Complex JOIN queries between tables
✅ RLS policy evaluation for different users

### Test Commands
```bash
# Apply the fix
PGPASSWORD=postgres psql -h localhost -p 54322 -U postgres -d postgres \
  -f scripts/fix-rls-recursion.sql

# Verify the fix
PGPASSWORD=postgres psql -h localhost -p 54322 -U postgres -d postgres \
  -f scripts/final-rls-verification.sql
```

### Sample Test Output
```sql
-- Test 2.1: COUNT vault_entries (previously caused infinite recursion)
SELECT COUNT(*) FROM vault_entries WHERE user_id = '2ccbf465-6de0-43a4-ad73-2a7ed495fd06';
-- Result: 2 ✅ (no error)

-- Test 3.2: User with shared access sees both own and shared entries
SELECT id, title, user_id FROM vault_entries ORDER BY title;
-- Result: Returns both owned and shared entries ✅
```

---

## Security Considerations

### Why SECURITY DEFINER is Safe Here

1. **Limited Scope**: Functions only check ownership/permissions, don't expose sensitive data
2. **Read-Only**: Functions use `EXISTS` checks, don't modify data
3. **Explicit Permissions**: Functions granted only to `authenticated` role
4. **Search Path Protection**: `SET search_path = public` prevents injection attacks
5. **No Data Leakage**: Functions return only boolean true/false, not actual data

### Security Best Practices Followed

- ✅ Functions declared as `STABLE` (can't modify database)
- ✅ Explicit `search_path` set to prevent schema injection
- ✅ Minimal privileges granted (only `EXECUTE` to `authenticated`)
- ✅ No sensitive data returned (only boolean checks)
- ✅ Input parameters are strongly typed (uuid)

---

## Files Modified

1. **Created**: `/scripts/fix-rls-recursion.sql`
   - Contains SECURITY DEFINER functions
   - Updates sharing_permissions RLS policy

2. **Created**: `/scripts/final-rls-verification.sql`
   - Comprehensive test suite
   - Verifies no infinite recursion

3. **Created**: `/scripts/RLS-RECURSION-FIX-DOCUMENTATION.md` (this file)
   - Complete documentation of issue and solution

---

## Lessons Learned

### Key Takeaways

1. **RLS Circular Dependencies**: Be careful when RLS policies on different tables reference each other
2. **SECURITY DEFINER Pattern**: Use SECURITY DEFINER functions to break RLS recursion when safe
3. **Testing**: Always test RLS policies with actual data and different user contexts
4. **Documentation**: Document security-critical changes thoroughly

### When to Use SECURITY DEFINER

✅ **Good Use Cases**:
- Breaking RLS circular dependencies (like this fix)
- Centralized permission checks
- Audit logging functions
- Helper functions for complex RLS policies

❌ **Bad Use Cases**:
- Exposing sensitive data directly
- Bypassing security controls for convenience
- Functions that modify critical data

---

## Future Improvements

### Potential Optimizations

1. **Function Caching**: Consider caching results within a transaction
2. **Indexing**: Ensure proper indexes on `(entry_id, shared_with_user_id)` for `sharing_permissions`
3. **Performance Monitoring**: Track execution time of helper functions
4. **Additional Functions**: Create similar helpers for other permission levels (read, write)

### Monitoring

Monitor these metrics:
- Query execution time for vault_entries
- SECURITY DEFINER function call frequency
- RLS policy evaluation overhead

---

## References

- [PostgreSQL RLS Documentation](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
- [SECURITY DEFINER Functions](https://www.postgresql.org/docs/current/sql-createfunction.html)
- [Supabase RLS Best Practices](https://supabase.com/docs/guides/auth/row-level-security)

---

## Change Log

| Date | Change | Author |
|------|--------|--------|
| 2025-10-04 | Initial fix implemented and verified | Claude Code |

---

**Status**: ✅ Production Ready
**Impact**: High - Resolves critical infinite recursion bug
**Risk**: Low - Solution follows PostgreSQL security best practices
