Row Level Security - Sistema Financiero

Overview

Row Level Security (RLS) is a PostgreSQL feature that controls which rows users can access in database tables. Sistema Financiero uses RLS to ensure users only see their own financial transactions.

RLS is enforced at the database level, making it impossible to bypass even if application-level security is compromised.

Why Row Level Security?

Without RLS

-- ❌ All users can see ALL transactions
SELECT * FROM transacciones;
-- Returns: All transactions from all users

With RLS

-- ✅ Users only see their own transactions
SELECT * FROM transacciones;
-- Returns: Only transactions where usuario_id = auth.uid()

Database Schema

Transacciones Table

The main table storing all financial transactions:

CREATE TABLE transacciones (
  -- Primary Key
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- Core Transaction Fields
  fecha TIMESTAMP NOT NULL DEFAULT NOW(),
  tipo TEXT CHECK (tipo IN ('ingreso', 'gasto')) NOT NULL,
  monto NUMERIC(10, 2) NOT NULL CHECK (monto > 0),
  categoria TEXT NOT NULL,

  -- Optional Details
  concepto TEXT DEFAULT 'Transacción manual',
  descripcion TEXT,
  metodo_pago TEXT CHECK (metodo_pago IN ('Efectivo', 'Tarjeta', 'Transferencia')),
  registrado_por TEXT,
  foto_url TEXT,

  -- User Association (Critical for RLS)
  usuario_id UUID REFERENCES auth.users(id),

  -- Metadata
  created_at TIMESTAMP DEFAULT NOW()
);

usuario_id

UUID

Foreign key linking transactions to Supabase Auth users. This field is critical for RLS policies to work correctly.

RLS Configuration

Step 1: Enable RLS

ALTER TABLE transacciones ENABLE ROW LEVEL SECURITY;

Once RLS is enabled, all queries will return zero rows until policies are created. This is a security feature.

Step 2: Create Policies

Select Policy (Read Access)

CREATE POLICY "Users can view own transactions"
  ON transacciones
  FOR SELECT
  USING (auth.uid() = usuario_id);

How it works:

auth.uid() returns the currently authenticated user’s ID
usuario_id is the foreign key in the table
Only rows where these match are visible

Insert Policy (Create Access)

CREATE POLICY "Users can insert own transactions"
  ON transacciones
  FOR INSERT
  WITH CHECK (auth.uid() = usuario_id);

How it works:

Checks the usuario_id of the row being inserted
Only allows insert if it matches the authenticated user’s ID
Prevents users from creating transactions for other users

Complete RLS Setup

Here’s the complete SQL to set up RLS for Sistema Financiero:

-- Enable RLS
ALTER TABLE transacciones ENABLE ROW LEVEL SECURITY;

-- Policy 1: Users can view their own transactions
CREATE POLICY "Users can view own transactions"
  ON transacciones FOR SELECT
  USING (auth.uid() = usuario_id);

-- Policy 2: Users can insert their own transactions
CREATE POLICY "Users can insert own transactions"
  ON transacciones FOR INSERT
  WITH CHECK (auth.uid() = usuario_id);

-- Policy 3: Users can update their own transactions
CREATE POLICY "Users can update own transactions"
  ON transacciones FOR UPDATE
  USING (auth.uid() = usuario_id)
  WITH CHECK (auth.uid() = usuario_id);

-- Policy 4: Users can delete their own transactions
CREATE POLICY "Users can delete own transactions"
  ON transacciones FOR DELETE
  USING (auth.uid() = usuario_id);

How RLS Works in Practice

Scenario 1: User Queries Their Transactions

// Frontend/API code
const { data, error } = await supabase
  .from('transacciones')
  .select('*')
  .order('fecha', { ascending: false });

What happens behind the scenes:

-- PostgreSQL automatically rewrites the query to:
SELECT *
FROM transacciones
WHERE usuario_id = auth.uid()  -- ✅ RLS policy applied
ORDER BY fecha DESC;

You don’t need to add WHERE usuario_id = auth.uid() in your application code. RLS handles this automatically!

Scenario 2: User Tries to Access Another User’s Data

// Attempting to access transaction with specific ID
const { data, error } = await supabase
  .from('transacciones')
  .select('*')
  .eq('id', 'some-other-users-transaction-id');

Result:

If transaction belongs to current user: Returns data ✅
If transaction belongs to another user: Returns empty array ✅
No error is thrown, just filtered out silently

Scenario 3: Inserting a Transaction

// API route - creating a transaction
const { data, error } = await supabase
  .from('transacciones')
  .insert({
    fecha: new Date().toISOString(),
    tipo: 'gasto',
    categoria: 'Alimentación',
    monto: 250.00,
    usuario_id: userId  // Must match auth.uid()
  });

RLS Validation:

-- PostgreSQL checks:
INSERT INTO transacciones (...)
VALUES (...)
WHERE auth.uid() = NEW.usuario_id;  -- ✅ Must pass

If usuario_id doesn’t match auth.uid(), the insert will fail with a policy violation error.

Current Implementation Status

Active Protection

The current implementation has RLS configured in the schema (README.md lines 86-97) but uses the anonymous key for all operations.

// Current implementation in app/api/transacciones/route.ts
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!  // Anonymous key
)

export async function GET(request: Request) {
  // No user authentication - returns all transactions
  const { data, error } = await supabase
    .from('transacciones')
    .select('*')
    .order('fecha', { ascending: false });

  return NextResponse.json({ data });
}

Behavior

Without Authenticated Users:

RLS policies exist but auth.uid() returns NULL
Queries return empty results (no transactions visible)
Inserts fail unless usuario_id is explicitly set

With Service Role Key (Bypasses RLS):

// Using service role key (NOT recommended for client-facing APIs)
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!  // ⚠️ Bypasses RLS
)

Never use the service role key in client-side code or public API routes. It bypasses all RLS policies!

Data Isolation Examples

Multi-Tenant Scenario

-- User A's transactions
INSERT INTO transacciones (usuario_id, tipo, monto, categoria)
VALUES ('user-a-uuid', 'gasto', 100, 'Alimentación');

-- User B's transactions
INSERT INTO transacciones (usuario_id, tipo, monto, categoria)
VALUES ('user-b-uuid', 'ingreso', 5000, 'Salario');

When User A queries:

SELECT * FROM transacciones;
-- Returns ONLY:
-- | usuario_id   | tipo  | monto | categoria    |
-- | user-a-uuid  | gasto | 100   | Alimentación |

When User B queries:

SELECT * FROM transacciones;
-- Returns ONLY:
-- | usuario_id   | tipo    | monto | categoria |
-- | user-b-uuid  | ingreso | 5000  | Salario   |

Aggregate Queries

RLS also protects aggregate functions:

// Calculate user's total expenses
const { data, error } = await supabase
  .from('transacciones')
  .select('monto')
  .eq('tipo', 'gasto');

const total = data?.reduce((sum, t) => sum + t.monto, 0);

RLS ensures:

Only current user’s expenses are summed
No leakage of other users’ financial data
Works for COUNT, SUM, AVG, etc.

Security Benefits

1. Defense in Depth

// Even if application code is compromised:
const { data } = await supabase
  .from('transacciones')
  .select('*');  // Still only returns current user's data ✅

2. Protection Against SQL Injection

RLS policies are evaluated after query parsing, protecting against injection:

-- Malicious attempt
SELECT * FROM transacciones WHERE id = '1' OR '1'='1';

-- Still filtered by RLS:
SELECT * FROM transacciones
WHERE (id = '1' OR '1'='1')
  AND usuario_id = auth.uid();  -- ✅ RLS adds this

3. API Key Compromise Protection

If the anonymous API key is exposed:

Attackers can only access their own data
Cannot query or modify other users’ transactions
RLS enforced at database level (can’t be disabled via API)

Testing RLS Policies

Using Supabase Dashboard

Go to Authentication > Users
Create test users
Go to Table Editor > transacciones
Switch between users using the dropdown
Verify you only see that user’s transactions

Using SQL Editor

-- Test as specific user
SET request.jwt.claim.sub = 'user-uuid-here';

SELECT * FROM transacciones;
-- Should only show that user's transactions

-- Test insert permission
INSERT INTO transacciones (usuario_id, tipo, monto, categoria)
VALUES ('user-uuid-here', 'gasto', 100, 'Test');
-- Should succeed ✅

INSERT INTO transacciones (usuario_id, tipo, monto, categoria)
VALUES ('different-user-uuid', 'gasto', 100, 'Test');
-- Should fail ❌ (policy violation)

Automated Testing

// Example test using Jest + Supabase
import { createClient } from '@supabase/supabase-js';

describe('RLS Policies', () => {
  it('should only return user\'s own transactions', async () => {
    const supabase = createClient(url, anonKey);
    
    // Authenticate as User A
    await supabase.auth.signInWithPassword({
      email: '[email protected]',
      password: 'password'
    });
    
    const { data } = await supabase
      .from('transacciones')
      .select('*');
    
    // Verify all transactions belong to User A
    expect(data.every(t => t.usuario_id === userA.id)).toBe(true);
  });
});

Common Pitfalls

Pitfall 1: Missing usuario_id

// ❌ Will fail - no usuario_id
const { error } = await supabase
  .from('transacciones')
  .insert({
    fecha: new Date(),
    tipo: 'gasto',
    monto: 100
  });

console.log(error);
// "new row violates row-level security policy"

Solution:

// ✅ Include usuario_id
const { data: { user } } = await supabase.auth.getUser();

const { error } = await supabase
  .from('transacciones')
  .insert({
    fecha: new Date(),
    tipo: 'gasto',
    monto: 100,
    usuario_id: user.id  // ✅ Matches auth.uid()
  });

Pitfall 2: Using Service Role in Client Code

// ❌ NEVER DO THIS
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!  // ⚠️ Exposes admin access
)

Impact:

Bypasses all RLS policies
Full database access
Security vulnerability

Solution: Only use service role key in secure server-side contexts (background jobs, admin panels).

Pitfall 3: Not Enabling RLS

-- ❌ Forgot to enable RLS
CREATE TABLE transacciones (...);
CREATE POLICY "Users can view own transactions" ON transacciones ...

-- Policies exist but are not enforced!

Solution:

-- ✅ Must explicitly enable RLS
ALTER TABLE transacciones ENABLE ROW LEVEL SECURITY;

Performance Considerations

Indexing for RLS

RLS adds a WHERE clause to every query. Optimize with indexes:

-- Index on usuario_id for fast filtering
CREATE INDEX idx_transacciones_usuario ON transacciones(usuario_id);

-- Composite index for common queries
CREATE INDEX idx_transacciones_usuario_fecha 
  ON transacciones(usuario_id, fecha DESC);

Query Performance

// RLS adds filter, but with index it's fast
const { data } = await supabase
  .from('transacciones')
  .select('*')
  .order('fecha', { ascending: false })
  .limit(50);

// PostgreSQL executes:
// SELECT * FROM transacciones
// WHERE usuario_id = auth.uid()  -- ✅ Uses index
// ORDER BY fecha DESC
// LIMIT 50;

RLS overhead is minimal (typically < 1ms) when proper indexes exist.

Advanced RLS Patterns

Conditional Policies

-- Allow read-only access to transactions older than 1 year
CREATE POLICY "Users can view old transactions"
  ON transacciones FOR SELECT
  USING (
    auth.uid() = usuario_id
    AND fecha < NOW() - INTERVAL '1 year'
  );

Role-Based Policies

-- Admins can see all transactions
CREATE POLICY "Admins can view all transactions"
  ON transacciones FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM auth.users
      WHERE auth.users.id = auth.uid()
        AND auth.users.role = 'admin'
    )
  );

Team/Organization Policies

-- Users can see transactions from their organization
CREATE POLICY "Users can view team transactions"
  ON transacciones FOR SELECT
  USING (
    organizacion_id IN (
      SELECT organizacion_id FROM users
      WHERE users.id = auth.uid()
    )
  );

Troubleshooting

”Row Level Security Policy Violation”

Symptom:

{
  "error": "new row violates row-level security policy for table \"transacciones\""
}

Causes:

usuario_id doesn’t match auth.uid()
User not authenticated (auth.uid() returns NULL)
Policy conditions not met

Solution:

// Verify authentication
const { data: { user } } = await supabase.auth.getUser();
console.log('Current user:', user?.id);

// Ensure usuario_id matches
const { error } = await supabase
  .from('transacciones')
  .insert({
    usuario_id: user.id,  // ✅ Must match auth.uid()
    // ... other fields
  });

Queries Return Empty Arrays

Symptom:

const { data } = await supabase.from('transacciones').select('*');
console.log(data); // []

Causes:

RLS enabled but no matching rows
Not authenticated
Data belongs to different user

Debugging:

-- Check if RLS is enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE tablename = 'transacciones';

-- Check existing policies
SELECT * FROM pg_policies
WHERE tablename = 'transacciones';

-- Count total rows (requires service role)
SELECT COUNT(*) FROM transacciones;

Migration Guide

Adding RLS to Existing Data

If you have existing data without usuario_id:

-- Step 1: Add column (if not exists)
ALTER TABLE transacciones
ADD COLUMN IF NOT EXISTS usuario_id UUID REFERENCES auth.users(id);

-- Step 2: Assign existing data to default user
UPDATE transacciones
SET usuario_id = 'default-user-uuid'
WHERE usuario_id IS NULL;

-- Step 3: Make column required
ALTER TABLE transacciones
ALTER COLUMN usuario_id SET NOT NULL;

-- Step 4: Enable RLS
ALTER TABLE transacciones ENABLE ROW LEVEL SECURITY;

-- Step 5: Create policies (see above)

Authentication

Endpoints

​Overview

​Why Row Level Security?

​Without RLS

​With RLS

​Database Schema

​Transacciones Table

​RLS Configuration

​Step 1: Enable RLS

​Step 2: Create Policies

​Select Policy (Read Access)

​Insert Policy (Create Access)

​Complete RLS Setup

​How RLS Works in Practice

​Scenario 1: User Queries Their Transactions

​Scenario 2: User Tries to Access Another User’s Data

​Scenario 3: Inserting a Transaction

​Current Implementation Status

​Active Protection

​Behavior

​Data Isolation Examples

​Multi-Tenant Scenario

​Aggregate Queries

​Security Benefits

​1. Defense in Depth

​2. Protection Against SQL Injection

​3. API Key Compromise Protection

​Testing RLS Policies

​Using Supabase Dashboard

​Using SQL Editor

​Automated Testing

​Common Pitfalls

​Pitfall 1: Missing usuario_id

​Pitfall 2: Using Service Role in Client Code

​Pitfall 3: Not Enabling RLS

​Performance Considerations

​Indexing for RLS

​Query Performance

​Advanced RLS Patterns

​Conditional Policies

​Role-Based Policies

​Team/Organization Policies

​Troubleshooting

​”Row Level Security Policy Violation”

​Queries Return Empty Arrays

​Migration Guide

​Adding RLS to Existing Data

​Next Steps

Authentication

API Endpoints

​Resources

Build docs developers (and LLMs) love

Overview

Why Row Level Security?

Without RLS

With RLS

Database Schema

Transacciones Table

RLS Configuration

Step 1: Enable RLS

Step 2: Create Policies

Select Policy (Read Access)

Insert Policy (Create Access)

Complete RLS Setup

How RLS Works in Practice

Scenario 1: User Queries Their Transactions

Scenario 2: User Tries to Access Another User’s Data

Scenario 3: Inserting a Transaction

Current Implementation Status

Active Protection

Behavior

Data Isolation Examples

Multi-Tenant Scenario

Aggregate Queries

Security Benefits

1. Defense in Depth

2. Protection Against SQL Injection

3. API Key Compromise Protection

Testing RLS Policies

Using Supabase Dashboard

Using SQL Editor

Automated Testing

Common Pitfalls

Pitfall 1: Missing usuario_id

Pitfall 2: Using Service Role in Client Code

Pitfall 3: Not Enabling RLS

Performance Considerations

Indexing for RLS

Query Performance

Advanced RLS Patterns

Conditional Policies

Role-Based Policies

Team/Organization Policies

Troubleshooting

”Row Level Security Policy Violation”

Queries Return Empty Arrays

Migration Guide

Adding RLS to Existing Data

Next Steps

Resources