Implementing Row-Level Security in Supabase with Better Auth: A Comprehensive Guide

Overview

User authentication and secure data access are critical components of modern web applications. Better Auth is an open source authentication framework for TypeScript that simplifies the process by handling user authentication, managing sessions, and offering robust user management features.

When combined with Supabase’s powerful database management and PostgreSQL’s Row-Level Security (RLS) policies, you can create a secure, scalable application that ensures user-specific data access.

In this guide, we’ll walk through integrating Better-Auth and Supabase to build a personalized and secure application using Next.js 15 as the application framework.

Step 1: Set up a Supabase project

1. Sign in to your Supabase dashboard.

2. Select new project.

   • Enter a project name.

   • Set a secure password for your database.

   • Select a database region that best suits your application.

   • Select Create new project.

3. In the Project settings, go to Configuration > Data API. Copy the following:

   • Project URL.

   • Project API Keys anon public

4. Scroll to the JWT Settings section and copy the JWT Secret.

5. Still in Supabase, navigate to SQL Editor and paste the following SQL code into the command window and select Run:

    -- Create the table
    CREATE TABLE todos (
      id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
      task TEXT NOT NULL,
      user_id TEXT NOT NULL,
      completed_state BOOLEAN DEFAULT FALSE
    );
   
    -- Enable row-level security
    ALTER TABLE todos ENABLE ROW LEVEL SECURITY;
   

   This command creates a todos table and also inserts four sample todo items. We will need this later.

Step 2: Setup a Next.js Application with Better-Auth

1. Run the following command in your terminal window to start a new project with Next.js and Supabase:

    npx create-next-app -e with-supabase
   

2. Set a name for your project (e.g: better-auth-with-supabase)

3. Go into the project directory:

    cd better-auth-with-supabase
   

4. Install the Better Auth, pg and jsonwebtoken dependencies with this command:

    npm install better-auth pg jsonwebtoken server-only
    npm install --save-dev @types/pg @types/jsonwebtoken
   

5. Create better-auth server and client instances in this path /lib:

    touch lib/auth.ts lib/auth-client.ts
   

6. Open the auth.ts file, enter the following code, and save the file:

    import { betterAuth } from "better-auth";
    import { nextCookies } from "better-auth/next-js";
    import { Pool } from "pg";
   
    export const auth = betterAuth({
      database: new Pool({
        connectionString: process.env.DATABASE_URL as string,
      }),
      emailAndPassword: {
        enabled: true,
        minPasswordLength: 8,
        maxPasswordLength: 15,
      },
      emailVerification: {
        sendOnSignUp: process.env.NODE_ENV === "production",
        autoSignInAfterVerification: true,
      },
      socialProviders: {
        google: {
          clientId: process.env.GOOGLE_CLIENT_ID as string,
          clientSecret: process.env.GOOGLE_CLIENT_SECRET as string,
        },
      },
      user: {
        deleteUser: {
          enabled: true,
        },
      },
      plugins: [nextCookies()], // make sure this is the last plugin in the array
      secret: process.env.BETTER_AUTH_SECRET as string,
    });
   
    export type Session = typeof auth.$Infer.Session;
   

7. Open the auth-client.ts file, enter the following code, and save the file:

    import { createAuthClient } from "better-auth/react";
   
    export const authClient = createAuthClient({
      baseURL: "http://localhost:3000",
    });
   

8. Create the Better Auth endpoint in this path app/api/auth/[…all]:

    mkdir -p app/api/auth/[...all]
    touch app/api/auth/[...all]/route.ts
   

9. Open the newly created route.ts file, enter the following code, and save the file:

    import { auth } from "@/lib/auth";
    import { toNextJsHandler } from "better-auth/next-js";
   
    export const { GET, POST } = toNextJsHandler(auth.handler);
   

10. Create an environment variables file .env.local by typing:

    touch .env.local
    

11. Add the following to your .env.local file:

    • The Supabase Project URL, Anon Public Key and JWT Secret. You can get these from
      Supabase > Project Settings > Data API.

    • Better Auth Secret, which you can generate using openssl rand -base64 32 in your terminal.

        NEXT_PUBLIC_SUPABASE_URL=https://<supabase_project_id>.supabase.co
        NEXT_PUBLIC_SUPABASE_ANON_KEY=<supabase_anon_public_key>
        NEXT_PUBLIC_SUPABASE_JWT_SECRET=<supabase_jwt_secret>
        BETTER_AUTH_SECRET=<better_auth_secret>
      

12. Create a session file in this path lib:

    touch lib/session.ts
    

13. Enter the following code in session.ts and save the file:

    import "server-only";
    import { headers } from "next/headers";
    import { auth, Session } from "./auth";
    
    export async function getSession(): Promise<Session> {
      const session = await auth.api.getSession({
        headers: await headers(),
      });
      if (!session) {
        throw new Error("Session not found");
      }
      return session;
    }
    

14. Replace the utils/supabase/server.ts with the following code:

    import { createServerClient } from "@supabase/ssr";
    import { cookies } from "next/headers";
    import jwt from "jsonwebtoken";
    import { getSession } from "@/lib/session";
    
    export async function createClient() {
      const cookieStore = await cookies();
      const { session } = await getSession();
      const token = jwt.sign(
        { uid: session.userId },
        process.env.NEXT_PUBLIC_SUPABASE_JWT_SECRET!
      );
      return createServerClient(
        process.env.NEXT_PUBLIC_SUPABASE_URL!,
        process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
        {
          global: {
            headers: {
              Authorization: `Bearer ${token}`,
            },
          },
          cookies: {
            getAll() {
              return cookieStore.getAll();
            },
            setAll(cookiesToSet) {
              try {
                cookiesToSet.forEach(({ name, value, options }) =>
                  cookieStore.set(name, value, options)
                );
              } catch {
                // The `setAll` method was called from a Server Component.
                // This can be ignored if you have middleware refreshing
                // user sessions.
              }
            },
          },
        }
      );
    }
    

15. Run the following command to generate and migrate the better-auth migrations:

    npx @better-auth/cli@latest generate
    npx @better-auth/cli@latest migrate
    

Step 3: Define database policies and functions

1. In Supabase, go to the SQL Editor.

2. Select the plus “+” icon, and then Create a new snippet.

3. Paste the following SQL code into the command window and select Run:

    create or replace function get_user_id()
    returns text
    language sql stable
    as $$
      select nullif(current_setting('request.jwt.claims', true)::json->>'uid', '')::text;
    $$;
   

   This function extracts the uid field from the JSON Web Token that contains the user ID, which will allow filtering items specific to each user.

4. Create another new snippet and run the following SQL statement to enable the row-level policy on the todos table.

    create policy "users can read only their todos"
    on public.todos
    for select to public
    using (get_user_id() = user_id);
   

5. Select Save policy.

Your database policy is now set up to ensure that only the authenticated user’s to-do items are displayed.

Step 4: Build a simple to-do app

1. Create the sign-up, sign-in and todos pages.

    mkdir app/sign-up/page.tsx
    mkdir app/sign-in/page.tsx
   

2. Add the following code to the sign-up page:

    import React, { useState } from "react";
    import {authClient} from "@/lib/auth-client";
   
    export default function SignUp() {
      const [name, setName] = useState("");
      const [email, setEmail] = useState("");
      const [password, setPassword] = useState("");
   
      const handleSubmit = (e: React.FormEvent) => {
        e.preventDefault();
        console.log("Sign Up:", { name, email, password });
      };
   
      return (
        <div
          style={{
            width: "300px",
            margin: "100px auto",
            padding: "20px",
            border: "1px solid #ccc",
            borderRadius: "10px",
            textAlign: "center",
          }}
        >
          <h2 style={{ marginBottom: "20px" }}>Sign Up</h2>
          <form
            onSubmit={handleSubmit}
            style={{
              display: "flex",
              flexDirection: "column",
              gap: "10px",
            }}
          >
            <input
              type="text"
              placeholder="Full Name"
              required
              value={name}
              onChange={(e) => setName(e.target.value)}
              style={{
                padding: "10px",
                fontSize: "16px",
              }}
            />
            <input
              type="email"
              placeholder="Email"
              required
              value={email}
              onChange={(e) => setEmail(e.target.value)}
              style={{
                padding: "10px",
                fontSize: "16px",
              }}
            />
            <input
              type="password"
              placeholder="Password"
              required
              value={password}
              onChange={(e) => setPassword(e.target.value)}
              style={{
                padding: "10px",
                fontSize: "16px",
              }}
            />
            <button
              type="submit"
              style={{
                padding: "10px",
                fontSize: "16px",
                backgroundColor: "#28a745",
                color: "white",
                border: "none",
                cursor: "pointer",
                borderRadius: "5px",
              }}
            >
              Sign Up
            </button>
          </form>
        </div>
      );
    }
   

3. Add the following code to the sign-in page:

    import React, { useState } from "react";
   
    export default function SignIn() {
      const [email, setEmail] = useState("");
      const [password, setPassword] = useState("");
   
      const handleSubmit = (e: React.FormEvent) => {
        e.preventDefault();
        console.log("Sign In:", { email, password });
      };
   
      return (
        <div
          style={{
            width: "300px",
            margin: "100px auto",
            padding: "20px",
            border: "1px solid #ccc",
            borderRadius: "10px",
            textAlign: "center",
          }}
        >
          <h2 style={{ marginBottom: "20px" }}>Sign In</h2>
          <form
            onSubmit={handleSubmit}
            style={{
              display: "flex",
              flexDirection: "column",
              gap: "10px",
            }}
          >
            <input
              type="email"
              placeholder="Email"
              required
              value={email}
              onChange={(e) => setEmail(e.target.value)}
              style={{
                padding: "10px",
                fontSize: "16px",
              }}
            />
            <input
              type="password"
              placeholder="Password"
              required
              value={password}
              onChange={(e) => setPassword(e.target.value)}
              style={{
                padding: "10px",
                fontSize: "16px",
              }}
            />
            <button
              type="submit"
              style={{
                padding: "10px",
                fontSize: "16px",
                backgroundColor: "#0070f3",
                color: "white",
                border: "none",
                cursor: "pointer",
                borderRadius: "5px",
              }}
            >
              Sign In
            </button>
          </form>
        </div>
      );
    }
   

4. Add the following code to app/page.tsx which is a basic to-do list table:

    "use client";
   
    import React, { useState, useEffect } from "react";
    import { createClient } from "@/utils/supabase/client";
   
    export default function TodoPage() {
      const supabase = createClient();
      const [task, setTask] = useState("");
      const [completed, setCompleted] = useState(false);
      const [todos, setTodos] = useState<any[]>([]);
   
      const fetchTodos = async () => {
        const { data } = await supabase.from("todos").select();
        if (data) setTodos(data);
      };
   
      const handleSubmit = async (e: React.FormEvent) => {
        e.preventDefault();
        await supabase.from("todos").insert({
          task,
          completed_state: completed,
        });
        setTask("");
        setCompleted(false);
        fetchTodos();
      };
   
      useEffect(() => {
        fetchTodos();
      }, []);
   
      return (
        <div style={{ maxWidth: "800px", margin: "50px auto" }}>
          {/* Todo Form */}
          <div
            style={{
              padding: "20px",
              border: "1px solid #ccc",
              borderRadius: "10px",
              textAlign: "center",
              marginBottom: "30px",
            }}
          >
            <h3 style={{ marginBottom: "20px" }}>Add Todo</h3>
            <form
              onSubmit={handleSubmit}
              style={{
                display: "flex",
                flexDirection: "column",
                gap: "10px",
              }}
            >
              <input
                type="text"
                placeholder="Task"
                value={task}
                required
                onChange={(e) => setTask(e.target.value)}
                style={{
                  padding: "10px",
                  fontSize: "16px",
                }}
              />
              <label
                style={{
                  fontSize: "14px",
                  display: "flex",
                  alignItems: "center",
                  gap: "8px",
                }}
              >
                <input
                  type="checkbox"
                  checked={completed}
                  onChange={(e) => setCompleted(e.target.checked)}
                />
                Mark as complete
              </label>
              <button
                type="submit"
                style={{
                  padding: "10px",
                  fontSize: "16px",
                  backgroundColor: "#0070f3",
                  color: "white",
                  border: "none",
                  cursor: "pointer",
                  borderRadius: "5px",
                }}
              >
                Add Todo
              </button>
            </form>
          </div>
   
          {/* Todo List Table */}
          <div
            style={{ display: "flex", justifyContent: "center", marginTop: "50px" }}
          >
            <table style={{ width: "100%", borderCollapse: "collapse" }}>
              <thead>
                <tr>
                  <th
                    style={{
                      border: "1px solid #ddd",
                      padding: "8px",
                      textAlign: "left",
                      backgroundColor: "#f2f2f2",
                    }}
                  >
                    ID
                  </th>
                  <th
                    style={{
                      border: "1px solid #ddd",
                      padding: "8px",
                      textAlign: "left",
                      backgroundColor: "#f2f2f2",
                    }}
                  >
                    Task
                  </th>
                  <th
                    style={{
                      border: "1px solid #ddd",
                      padding: "8px",
                      textAlign: "left",
                      backgroundColor: "#f2f2f2",
                    }}
                  >
                    Is Complete
                  </th>
                </tr>
              </thead>
              <tbody>
                {todos?.map((row) => (
                  <tr key={row.id}>
                    <td style={{ border: "1px solid #ddd", padding: "8px" }}>
                      {row.id}
                    </td>
                    <td style={{ border: "1px solid #ddd", padding: "8px" }}>
                      {row.task}
                    </td>
                    <td style={{ border: "1px solid #ddd", padding: "8px" }}>
                      {String(row.completed_state)}
                    </td>
                  </tr>
                ))}
              </tbody>
            </table>
          </div>
        </div>
      );
    }
   

5. Go to http://localhost:3000 to preview the page. You won’t see any to-do items because we haven’t added the todos with their respective user id.

6. Go to the http://localhost:3000/sign-up and sign up. You will automatically be signed in after successful sign up.

7. Go to your Supabase dashboard and copy the user id from the users table.

8. Go to the todos table and add the user id in the user_id field of a todo and save it.

9. Come back to the http://localhost:3000 and refresh the page. You will be able to see the todo assigned to the signed in user.

Well Done!

You’ve successfully built a secure and personalized to-do app using Better Auth for authentication and Supabase for database management.

With this foundation, your app is now equipped to manage user authentication and personalized data securely. You can extend this project further by adding features like task creation, deletion, updates, or even real-time collaboration.