Learning Objectives

Understand Row Level Security concepts
Create RLS policies for different access patterns
Implement user-specific data isolation
Test and debug RLS policies
Apply RLS best practices for bill payment platform

1. What is Row Level Security?

Row Level Security (RLS) is a PostgreSQL feature that restricts which rows users can access in database tables.

Why RLS is Essential

Without RLS	With RLS
App code must filter data	Database enforces access
Bugs can expose data	Defense in depth
Complex authorization logic	Simple, declarative policies
Easy to forget checks	Always enforced

How RLS Works

This diagram illustrates the flow of a user request through Supabase's RLS system, showing how the database checks policies before returning data:

User Request → Supabase API → PostgreSQL
                               ↓
                         RLS Policy Check
                               ↓
                    ✓ Allow  or  ✗ Deny
                               ↓
                         Return Data

2. Enabling RLS

Enable RLS on Tables

Enabling RLS on a table activates the security layer, but by default it blocks all access until you create policies. This SQL enables RLS on all the main tables in our bill payment platform:

-- Enable RLS on all tables
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.wallets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.beneficiaries ENABLE ROW LEVEL SECURITY;

-- IMPORTANT: By default, enabling RLS blocks all access
-- You must create policies to allow access

Force RLS for Table Owners

By default, table owners bypass RLS policies. Using FORCE ROW LEVEL SECURITY ensures that even the table owner must follow the policies, which is important for consistent security:

-- Even table owners must follow RLS policies
ALTER TABLE public.users FORCE ROW LEVEL SECURITY;
ALTER TABLE public.wallets FORCE ROW LEVEL SECURITY;
ALTER TABLE public.transactions FORCE ROW LEVEL SECURITY;

3. RLS Policy Basics

Policy Syntax

The CREATE POLICY statement defines security rules for table access. The syntax allows you to specify which operations (SELECT, INSERT, UPDATE, DELETE), which roles, and what conditions must be met:

CREATE POLICY policy_name
  ON table_name
  FOR [ALL | SELECT | INSERT | UPDATE | DELETE]
  TO [role_name | PUBLIC]
  USING (condition)          -- For SELECT, UPDATE, DELETE
  WITH CHECK (condition);    -- For INSERT, UPDATE

Policy Components

Component	Purpose
`FOR`	Which operations the policy applies to
`TO`	Which roles the policy applies to
`USING`	Filters existing rows (read operations)
`WITH CHECK`	Validates new/updated rows (write operations)

Auth Functions in Supabase

Supabase provides built-in auth functions that you can use in your RLS policies. These functions extract information from the authenticated user's JWT token:

-- Get current user's UUID (from JWT)
auth.uid()

-- Get current user's role
auth.role()

-- Get JWT claims
auth.jwt()

-- Example: Check if user is authenticated
auth.uid() IS NOT NULL

4. Users Table Policies

These policies control access to the users table, ensuring each user can only view and modify their own profile. The auth_id column links our users table to Supabase Auth:

-- Users can view their own profile
CREATE POLICY "Users can view own profile"
  ON public.users
  FOR SELECT
  TO authenticated
  USING (auth_id = auth.uid());

-- Users can update their own profile
CREATE POLICY "Users can update own profile"
  ON public.users
  FOR UPDATE
  TO authenticated
  USING (auth_id = auth.uid())
  WITH CHECK (auth_id = auth.uid());

-- New users can insert their profile (during registration)
CREATE POLICY "Users can create own profile"
  ON public.users
  FOR INSERT
  TO authenticated
  WITH CHECK (auth_id = auth.uid());

-- Prevent users from deleting their profile (soft delete instead)
-- No DELETE policy = no deletion allowed

TypeScript Usage

When you query with Supabase client, RLS automatically filters results based on the authenticated user. Even if you try to access another user's data, RLS will return empty results instead of an error:

// This query automatically filters to current user
const { data: user } = await supabase.from("users").select("*").single();

// Even if you try to access another user's data, RLS blocks it
const { data, error } = await supabase
  .from("users")
  .select("*")
  .eq("id", "other-users-id"); // Returns empty, not error

// error will be null, but data will be empty

5. Wallets Table Policies

Helper Function

Since the wallets table uses user_id instead of auth_id, we create a helper function to look up the current user's internal ID from their auth ID. The SECURITY DEFINER modifier allows the function to access the users table regardless of RLS:

-- First, create a helper function to get user's ID from auth_id
CREATE OR REPLACE FUNCTION public.get_user_id()
RETURNS UUID AS $$
  SELECT id FROM public.users WHERE auth_id = auth.uid()
$$ LANGUAGE SQL SECURITY DEFINER;

These policies use the helper function to ensure users can only view and update their own wallets. Note that we don't provide INSERT or DELETE policies, as wallets should be created by the system:

-- Users can view their own wallets
CREATE POLICY "Users can view own wallets"
  ON public.wallets
  FOR SELECT
  TO authenticated
  USING (user_id = get_user_id());

-- Users can update their own wallet (for settings, not balance)
CREATE POLICY "Users can update own wallet settings"
  ON public.wallets
  FOR UPDATE
  TO authenticated
  USING (user_id = get_user_id())
  WITH CHECK (user_id = get_user_id());

-- System creates wallets (via trigger or admin)
-- No INSERT policy for regular users

-- Prevent wallet deletion
-- No DELETE policy

Secure Balance Updates

Direct balance updates should never be allowed through regular RLS policies. Instead, we create a secure function that validates ownership, checks sufficient balance, and performs the update atomically:

-- Create a function for secure balance updates (only callable by system)
CREATE OR REPLACE FUNCTION public.update_wallet_balance(
  p_wallet_id UUID,
  p_amount DECIMAL,
  p_type TEXT
)
RETURNS DECIMAL AS $$
DECLARE
  v_new_balance DECIMAL;
BEGIN
  -- Update balance based on type
  UPDATE wallets
  SET balance = CASE
    WHEN p_type = 'credit' THEN balance + p_amount
    WHEN p_type = 'debit' THEN balance - p_amount
    ELSE balance
  END,
  updated_at = NOW()
  WHERE id = p_wallet_id
    AND user_id = get_user_id()  -- Ensure user owns wallet
    AND (p_type = 'credit' OR balance >= p_amount)  -- Check sufficient balance for debit
  RETURNING balance INTO v_new_balance;

  IF v_new_balance IS NULL THEN
    RAISE EXCEPTION 'Insufficient balance or wallet not found';
  END IF;

  RETURN v_new_balance;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

6. Transactions Table Policies

Transactions are an immutable audit trail, so users can view and create their own transactions but cannot update or delete them. This ensures data integrity:

-- Users can view their own transactions
CREATE POLICY "Users can view own transactions"
  ON public.transactions
  FOR SELECT
  TO authenticated
  USING (user_id = get_user_id());

-- Users can create transactions (through proper channels)
CREATE POLICY "Users can create own transactions"
  ON public.transactions
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = get_user_id());

-- Users cannot update transactions (immutable record)
-- No UPDATE policy

-- Users cannot delete transactions (audit trail)
-- No DELETE policy

Transaction Creation Function

This comprehensive function handles the entire transaction creation process securely. It validates the user, checks the balance, generates a reference number, deducts from the wallet, and creates the transaction record all in one atomic operation:

-- Secure transaction creation with balance update
CREATE OR REPLACE FUNCTION public.create_transaction(
  p_product_id UUID,
  p_amount DECIMAL,
  p_bill_account TEXT,
  p_description TEXT DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
  v_user_id UUID;
  v_wallet_id UUID;
  v_balance_before DECIMAL;
  v_balance_after DECIMAL;
  v_transaction_id UUID;
  v_reference TEXT;
BEGIN
  -- Get user and wallet
  SELECT u.id, w.id, w.balance
  INTO v_user_id, v_wallet_id, v_balance_before
  FROM users u
  JOIN wallets w ON w.user_id = u.id
  WHERE u.auth_id = auth.uid()
    AND w.is_active = true;

  IF v_wallet_id IS NULL THEN
    RAISE EXCEPTION 'Wallet not found';
  END IF;

  IF v_balance_before < p_amount THEN
    RAISE EXCEPTION 'Insufficient balance';
  END IF;

  -- Generate reference
  v_reference := 'TXN' || TO_CHAR(NOW(), 'YYYYMMDD') ||
                 UPPER(SUBSTRING(gen_random_uuid()::text, 1, 8));

  -- Deduct from wallet
  UPDATE wallets
  SET balance = balance - p_amount, updated_at = NOW()
  WHERE id = v_wallet_id
  RETURNING balance INTO v_balance_after;

  -- Create transaction record
  INSERT INTO transactions (
    reference, wallet_id, user_id, type, status,
    amount, fee, total_amount,
    balance_before, balance_after,
    product_id, bill_account, description
  ) VALUES (
    v_reference, v_wallet_id, v_user_id, 'debit', 'pending',
    p_amount, 0, p_amount,
    v_balance_before, v_balance_after,
    p_product_id, p_bill_account, p_description
  )
  RETURNING id INTO v_transaction_id;

  RETURN v_transaction_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

7. Products Table Policies

Products are public information that anyone (including unauthenticated users) can view, but only admins can modify. This is achieved by using TO PUBLIC for SELECT and having no write policies for regular users:

-- Products are publicly readable (no auth required)
CREATE POLICY "Products are viewable by everyone"
  ON public.products
  FOR SELECT
  TO PUBLIC  -- Includes anonymous users
  USING (status = 'active');

-- Only admins can modify products
-- We'll use service role for admin operations
-- No INSERT/UPDATE/DELETE policies for authenticated users

Admin-Only Access Pattern

For admin functionality, we create a helper function that checks if the current user has an admin role. This function can then be used in policies to restrict access to admin users only:

-- Create admin check function
CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM users
    WHERE auth_id = auth.uid()
      AND role = 'admin'
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Admin-only policy example
CREATE POLICY "Admins can manage products"
  ON public.products
  FOR ALL
  TO authenticated
  USING (is_admin())
  WITH CHECK (is_admin());

8. Beneficiaries Table Policies

Beneficiaries (saved bill payment recipients) are fully user-owned data. Users have complete CRUD (Create, Read, Update, Delete) access to their own beneficiaries but cannot access other users' saved recipients:

-- Users can view their own beneficiaries
CREATE POLICY "Users can view own beneficiaries"
  ON public.beneficiaries
  FOR SELECT
  TO authenticated
  USING (user_id = get_user_id());

-- Users can create beneficiaries
CREATE POLICY "Users can create own beneficiaries"
  ON public.beneficiaries
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = get_user_id());

-- Users can update their beneficiaries
CREATE POLICY "Users can update own beneficiaries"
  ON public.beneficiaries
  FOR UPDATE
  TO authenticated
  USING (user_id = get_user_id())
  WITH CHECK (user_id = get_user_id());

-- Users can delete their beneficiaries
CREATE POLICY "Users can delete own beneficiaries"
  ON public.beneficiaries
  FOR DELETE
  TO authenticated
  USING (user_id = get_user_id());

9. Testing RLS Policies

Test as Authenticated User

You can test RLS policies in the Supabase SQL Editor by temporarily assuming the role of an authenticated user. This allows you to verify that policies work correctly before deploying:

-- In Supabase SQL Editor, set the role
SET ROLE authenticated;

-- Set the user's JWT claim
SET request.jwt.claim.sub = 'user-uuid-here';

-- Test queries
SELECT * FROM users;  -- Should only see own data
SELECT * FROM wallets;  -- Should only see own wallets
SELECT * FROM products;  -- Should see all active products

-- Reset role
RESET ROLE;

Test in TypeScript

Testing RLS in your actual application code ensures that the policies work correctly with your Supabase client configuration. This example shows how to verify that users can access their own data but not other users' data:

// Test RLS in your application
import { createClient } from "@/lib/supabase/server";

async function testRLS() {
  const supabase = await createClient();

  // Get current user
  const {
    data: { user },
  } = await supabase.auth.getUser();
  console.log("Current user:", user?.id);

  // Try to access own data
  const { data: ownWallet, error: ownError } = await supabase
    .from("wallets")
    .select("*")
    .single();

  console.log("Own wallet:", ownWallet);
  console.log("Error:", ownError);

  // Try to access another user's data
  const { data: otherWallet, error: otherError } = await supabase
    .from("wallets")
    .select("*")
    .eq("user_id", "other-user-uuid"); // Different user

  console.log("Other wallet:", otherWallet); // Should be null/empty
  console.log("Error:", otherError); // No error, just empty result
}

Debug RLS Issues

When RLS policies aren't working as expected, these diagnostic queries help you inspect what policies exist and whether RLS is properly enabled on your tables:

-- Check existing policies
SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE schemaname = 'public';

-- Check if RLS is enabled
SELECT
  relname as table_name,
  relrowsecurity as rls_enabled,
  relforcerowsecurity as rls_forced
FROM pg_class
WHERE relnamespace = 'public'::regnamespace
  AND relkind = 'r';

10. Complete RLS Migration

This migration file brings together all the RLS policies for the entire bill payment platform. It enables RLS on all tables, creates the helper function, and defines comprehensive policies for each table:

-- migrations/002_row_level_security.sql

-- Enable RLS on all tables
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.wallets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.beneficiaries ENABLE ROW LEVEL SECURITY;

-- Helper function
CREATE OR REPLACE FUNCTION public.get_user_id()
RETURNS UUID AS $$
  SELECT id FROM public.users WHERE auth_id = auth.uid()
$$ LANGUAGE SQL SECURITY DEFINER STABLE;

-- ==========================================
-- USERS POLICIES
-- ==========================================
CREATE POLICY "Users can view own profile"
  ON public.users FOR SELECT
  TO authenticated
  USING (auth_id = auth.uid());

CREATE POLICY "Users can update own profile"
  ON public.users FOR UPDATE
  TO authenticated
  USING (auth_id = auth.uid())
  WITH CHECK (auth_id = auth.uid());

CREATE POLICY "Users can create own profile"
  ON public.users FOR INSERT
  TO authenticated
  WITH CHECK (auth_id = auth.uid());

-- ==========================================
-- WALLETS POLICIES
-- ==========================================
CREATE POLICY "Users can view own wallets"
  ON public.wallets FOR SELECT
  TO authenticated
  USING (user_id = get_user_id());

CREATE POLICY "Users can update own wallet settings"
  ON public.wallets FOR UPDATE
  TO authenticated
  USING (user_id = get_user_id())
  WITH CHECK (user_id = get_user_id());

-- ==========================================
-- TRANSACTIONS POLICIES
-- ==========================================
CREATE POLICY "Users can view own transactions"
  ON public.transactions FOR SELECT
  TO authenticated
  USING (user_id = get_user_id());

CREATE POLICY "Users can create own transactions"
  ON public.transactions FOR INSERT
  TO authenticated
  WITH CHECK (user_id = get_user_id());

-- ==========================================
-- PRODUCTS POLICIES
-- ==========================================
CREATE POLICY "Products are publicly viewable"
  ON public.products FOR SELECT
  TO PUBLIC
  USING (status = 'active');

-- ==========================================
-- BENEFICIARIES POLICIES
-- ==========================================
CREATE POLICY "Users can view own beneficiaries"
  ON public.beneficiaries FOR SELECT
  TO authenticated
  USING (user_id = get_user_id());

CREATE POLICY "Users can create own beneficiaries"
  ON public.beneficiaries FOR INSERT
  TO authenticated
  WITH CHECK (user_id = get_user_id());

CREATE POLICY "Users can update own beneficiaries"
  ON public.beneficiaries FOR UPDATE
  TO authenticated
  USING (user_id = get_user_id())
  WITH CHECK (user_id = get_user_id());

CREATE POLICY "Users can delete own beneficiaries"
  ON public.beneficiaries FOR DELETE
  TO authenticated
  USING (user_id = get_user_id());

11. RLS Best Practices

1. Use Helper Functions

Creating reusable helper functions for common checks keeps your policies DRY (Don't Repeat Yourself) and makes them easier to maintain:

-- Reusable function for user identification
CREATE OR REPLACE FUNCTION get_user_id()
RETURNS UUID AS $$
  SELECT id FROM users WHERE auth_id = auth.uid()
$$ LANGUAGE SQL SECURITY DEFINER STABLE;

-- Use in policies
USING (user_id = get_user_id())

2. Use SECURITY DEFINER for Functions

The SECURITY DEFINER modifier allows a function to run with the permissions of its creator rather than the caller. This is useful when the function needs to access data the caller cannot directly access:

-- Function runs with creator's permissions
CREATE FUNCTION my_function()
RETURNS void AS $$
  -- Can access data the caller can't
$$ LANGUAGE plpgsql SECURITY DEFINER;

3. Combine Policies with Indexes

RLS policies add conditions to every query, so you should create indexes on the columns used in your policies to maintain good performance:

-- Policy uses user_id
USING (user_id = get_user_id())

-- Index supports the policy
CREATE INDEX idx_transactions_user_id ON transactions(user_id);

4. Test All Scenarios

Always test your RLS policies thoroughly with different scenarios to ensure they work correctly. This array of test cases covers the most common access patterns:

// Test unauthorized access
const tests = [
  { name: "Own data access", expect: "success" },
  { name: "Other user data", expect: "empty" },
  { name: "Unauthenticated access", expect: "error" },
  { name: "Update own data", expect: "success" },
  { name: "Update other data", expect: "error" },
];

Additional Resources

Next Lesson: Database Relationships & Complex Queries