Security Fundamentals for Supabase

---

Security is Not Optional

Security isn't a feature you add later—it's a foundation you build on. Supabase provides powerful security tools, but using them correctly requires understanding the threat landscape.

---

The Supabase Security Model

┌─────────────────────────────────────────────────────────────┐
│                    Security Layers                          │
│                                                             │
│   1. Network Layer (HTTPS, Firewall)                       │
│      └── Managed by Supabase                               │
│                                                             │
│   2. Authentication (GoTrue)                               │
│      └── Who is making this request?                       │
│                                                             │
│   3. API Keys (anon/service_role)                         │
│      └── What level of trust?                              │
│                                                             │
│   4. Row Level Security (PostgreSQL)                       │
│      └── What data can they access?                        │
│                                                             │
│   5. Application Logic                                      │
│      └── Additional business rules                         │
│                                                             │
└─────────────────────────────────────────────────────────────┘

---

Understanding the Threat Model

What Supabase Protects

• Network encryption (TLS/HTTPS)
• Database server security
• Infrastructure management
• Authentication system integrity

What You Must Protect

• API key handling
• RLS policy correctness
• Application-level authorization
• User input validation
• Secret management

---

The Principle of Least Privilege

Grant only the minimum permissions necessary:

-- Bad: Overly permissive
CREATE POLICY "Everyone can do everything"
  ON posts FOR ALL
  USING (true)
  WITH CHECK (true);

-- Good: Specific permissions
CREATE POLICY "Users can read published posts"
  ON posts FOR SELECT
  USING (published = true);

CREATE POLICY "Users can manage own posts"
  ON posts FOR ALL
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

---

Defense in Depth

Never rely on a single security layer:

Layer 1: Frontend Validation

// Client-side (easily bypassed, but good UX)
if (!isValidEmail(email)) {
  showError('Invalid email')
  return
}

Layer 2: RLS Policies

-- Database level (cannot be bypassed from client)
CREATE POLICY "Users can only update own profile"
  ON profiles FOR UPDATE
  USING (id = auth.uid());

Layer 3: Database Constraints

-- Schema level (absolute enforcement)
CREATE TABLE profiles (
  email text NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

---

Common Security Pitfalls

Pitfall 1: Trusting the Frontend

// WRONG: Frontend sends user_id
const { error } = await supabase
  .from('posts')
  .insert({
    user_id: selectedUserId,  // Can be manipulated!
    title: 'My Post'
  })

// RIGHT: RLS ensures correct user_id
// WITH CHECK (user_id = auth.uid())

Pitfall 2: Missing RLS

-- Table created without RLS
CREATE TABLE secrets (...);
-- By default, anyone can query this table!

-- Always enable RLS
ALTER TABLE secrets ENABLE ROW LEVEL SECURITY;
-- Now no one can access until policies are created

Pitfall 3: Overly Complex Policies

-- Complex policies are hard to audit
CREATE POLICY "Complex access"
  ON data FOR SELECT
  USING (
    (type = 'public') OR
    (type = 'private' AND owner_id = auth.uid()) OR
    (type = 'shared' AND id IN (
      SELECT data_id FROM shares WHERE user_id = auth.uid()
    )) OR
    (EXISTS (SELECT 1 FROM admins WHERE user_id = auth.uid()))
  );

-- Better: Separate, clear policies
CREATE POLICY "Public data" ON data FOR SELECT USING (type = 'public');
CREATE POLICY "Own data" ON data FOR SELECT USING (owner_id = auth.uid());
CREATE POLICY "Shared data" ON data FOR SELECT USING (...);
CREATE POLICY "Admin access" ON data FOR SELECT USING (is_admin());

Pitfall 4: Exposing Service Role Key

// NEVER do this in frontend code
const supabase = createClient(url, process.env.SERVICE_ROLE_KEY)

// Service role bypasses ALL RLS!
// Only use in secure server environments

---

Secure Defaults

Enable RLS on All Tables

-- Make it a habit
CREATE TABLE new_table (...);
ALTER TABLE new_table ENABLE ROW LEVEL SECURITY;

Use NOT NULL Where Appropriate

-- Prevent null-related security issues
user_id uuid NOT NULL REFERENCES auth.users(id)

Explicit Foreign Keys

-- Foreign keys prevent orphaned/invalid references
post_id uuid REFERENCES posts(id) ON DELETE CASCADE

---

Input Validation

At the Database Level

CREATE TABLE users (
  email text NOT NULL
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  age integer
    CHECK (age >= 0 AND age < 150),
  status text
    CHECK (status IN ('active', 'suspended', 'deleted'))
);

In RLS Policies

-- Validate during insert
CREATE POLICY "Create valid posts"
  ON posts FOR INSERT
  WITH CHECK (
    user_id = auth.uid()
    AND length(title) >= 1
    AND length(title) <= 200
  );

In Edge Functions

import { z } from "https://deno.land/x/zod/mod.ts"

const schema = z.object({
  email: z.string().email(),
  amount: z.number().positive()
})

// Validate before processing
const result = schema.safeParse(body)
if (!result.success) {
  return new Response('Invalid input', { status: 400 })
}

---

Protecting Sensitive Data

Separate Sensitive Columns

-- Public profile
CREATE TABLE profiles (
  id uuid PRIMARY KEY,
  username text,
  avatar_url text
);

-- Sensitive data in separate table with stricter RLS
CREATE TABLE user_private_data (
  user_id uuid PRIMARY KEY REFERENCES auth.users(id),
  ssn_encrypted bytea,
  payment_info_encrypted bytea
);

Encrypt Sensitive Fields

-- Use pgcrypto for encryption
CREATE EXTENSION pgcrypto;

-- Encrypt sensitive data
INSERT INTO sensitive_data (encrypted_field)
VALUES (pgp_sym_encrypt('sensitive value', 'encryption_key'));

-- Decrypt when needed
SELECT pgp_sym_decrypt(encrypted_field, 'encryption_key') FROM sensitive_data;

---

Resources for Learning More

If you're new to SQL and PostgreSQL, we recommend taking our foundational courses:

• SQL Basics: Master PostgreSQL fundamentals from scratch
• Interactive SQL Practice: Hands-on SQL exercises with instant feedback

These courses will give you the SQL knowledge needed to write effective RLS policies and understand Supabase's PostgreSQL foundation.

---

Key Takeaways

1. Security is layered: Don't rely on any single mechanism
2. Enable RLS everywhere: Default deny is safer than default allow
3. Least privilege: Grant minimum necessary permissions
4. Validate at every layer: Frontend, RLS, and schema constraints
5. Keep policies simple: Complex policies hide vulnerabilities
6. Never expose service_role: Only use in secure server environments

---

Looking Ahead

With fundamentals understood, we'll dive into API key management and the differences between key types.

---

Security is a practice, not a product. The tools Supabase provides are powerful, but they're only as effective as the policies you write and the practices you follow.