The days of struggling with and debugging messy hand-crafted RLS (Row Level Security) policies are over. With Drizzle ORM 0.36.0, we now have RLS APIs. This update coincided with the release of Neon RLS, and Drizzle offers first-class support for it.

Neon Authorize provides many helpful example repositories, but it didn't include my preferred auth provider, Kinde.

There are several reasons why I consistently choose Kinde over other Auth-as-a-Service providers like Clerk, but that's a topic for another blog post.

Okay, enough yapping - how do we do this?

I’ll assume you already have an account with Kinde. If not, why not sign up and follow along anyway? They’ve got a very generous free tier, and if you sign up using my referral link you’ll get $50 credit!

For the sake of this article, I’ll be assuming you’re already familiar with the basics of Drizzle ORM.

To get started you’ll need:

• Drizzle ORM 0.36.0 or later

• Kinde

• Neon DB

• Neon Serverless (HTTP) Driver with Drizzle

Man said, “Let there be a todo app”

Naturally, we’ll be starting with a Todo app. I’m not going to bore you with making your umpteenth one, so I’ve made it for you.

https://github.com/Yoshify/Kinde-Neon-Drizzle-RLS

Clone the repo and read along!

Configuring Kinde Application

1. In the Kinde dashboard, click Settings > Applications > Add Application

2. Select Back-end web and give it a name - like Todo app!

3. Take note of the following App Keys

   

4. Replace the environment variables in the project with the ones provided by your Kinde application.

5. Configure your Callback URLs

   

6. Click Tokens then click customize under ID Token. Enable Organizations (array) and click save. We’ll need this for the applications Organization Switcher component.

Configuring Neon RLS

1. Pop into your Neon console, head to the Settings tab and scroll down to the RLS section. Click "Set up authentication provider”

   

2. Plug in the URL for your Kinde domain’s JWKS URL. It should look like so:
   https://<your_subdomain>.kinde.com/.well-known/jwks (ref: Kinde documentation)

   💡

   If you’re using a custom domain in Kinde, you can use that here too!

3. Neon should prompt you to go through a few steps - we only care about steps 1 and 4 here (setting up the extension and our environment variables)

   

4. Replace the environment variables in the repo with the ones provided to you by Neon here.

5. This is a step I believe to be missing in Neons guided setup - granting access to Sequences. To do so, in your Neon console, open the SQL Editor and run the following:

    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO authenticated;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO anonymous;
   

What it looks like in Drizzle

Lets look at our schema in src/server/db/schema.ts:

import { authenticatedRole, authUid, crudPolicy } from "drizzle-orm/neon";
import { boolean, pgTable, serial, varchar } from "drizzle-orm/pg-core";

export const todos = pgTable("todos", {
    id: integer().primaryKey().generatedByDefaultAsIdentity(),
    task: text().notNull(),
    completed: boolean().notNull().default(false),
    userId: text().notNull().default(sql`(auth.user_id())`),
}, (table) => [
    // This is where the magic happens
    crudPolicy({
        // This tells RLS the policy applies to the authenticated role.
        // By default, Neon comes with authenticated and anonymous
        // For more complex use cases, you can define your own!
        role: authenticatedRole,
        // The below defines the "USING" and "WITH CHECK" part of our RLS policies
        // It means that the authenticated user with the matching userId in a row can:
        // - Read that row (read)
        // - Update that row (modify)
        // - Delete that row (modify)
        // - Insert into this table, with a check that the authenticated user ID matches the newly inserted ID (modify)
        read: authUid(table.userId),
        modify: authUid(table.userId),
    })
])

Note the inclusion of crudPolicy. This is part of Drizzle ORMs new RLS API. Under the hood this is essentially creating the following PG policies:

ALTER TABLE "todos" ENABLE ROW LEVEL SECURITY;
CREATE POLICY "crud-authenticated-policy-select" ON "todos" AS PERMISSIVE FOR SELECT TO "authenticated" USING ((select auth.user_id() = "todos"."user_id"));
CREATE POLICY "crud-authenticated-policy-insert" ON "todos" AS PERMISSIVE FOR INSERT TO "authenticated" WITH CHECK ((select auth.user_id() = "todos"."user_id"));
CREATE POLICY "crud-authenticated-policy-update" ON "todos" AS PERMISSIVE FOR UPDATE TO "authenticated" USING ((select auth.user_id() = "todos"."user_id")) WITH CHECK ((select auth.user_id() = "todos"."user_id"));
CREATE POLICY "crud-authenticated-policy-delete" ON "todos" AS PERMISSIVE FOR DELETE TO "authenticated" USING ((select auth.user_id() = "todos"."user_id"));

Isn’t that so much easier than typing these out by hand?

Also note how we’re using (auth.user_id()) as a default for our user_id column in the table - how convenient is having it pre-filled!?

How do we pass Kinde auth to Neon?

Lets turn our attention to src/server/db/index.ts

import 'server-only'

import * as schema from './schema'
import { neon } from '@neondatabase/serverless';
import { NeonHttpDatabase } from 'drizzle-orm/neon-http';
import { drizzle } from 'drizzle-orm/neon-http';
import { getKindeServerSession } from '@kinde-oss/kinde-auth-nextjs/server';
import { KindeUser } from '@kinde-oss/kinde-auth-nextjs/types';


export const withAuthenticatedDrizzle = async <T>(
  callback: (
    db: NeonHttpDatabase<typeof schema>,
    { user }: { user: KindeUser<Record<string, any>> },
  ) => Promise<T>,
) => {
    const { isAuthenticated, getAccessTokenRaw, getUser} = getKindeServerSession()

    // Check for auth
    const authed = await isAuthenticated()
    if(!authed) {
      throw new Error("Unauthorized")
    }

    // Check for the current user (used for context in the callback)
    const user = await getUser()
    if(!user) {
      throw new Error("Unauthorized")
    }

    const db = drizzle(
      neon(process.env.DATABASE_AUTHENTICATED_URL!, {
        authToken: async () => {
          // This is where we supply our auth token to Neon
          // It's crucial you use getAccessTokenRaw() instead of getAccessToken()
          // as Neon will be verifying and parsing the JWT themselves
          // using the JWKS we supplied during setup.
          const token = await getAccessTokenRaw()
          if(!token) {
            throw new Error("Unauthorized")
          }
          return token
        }
      }),
      {
        schema,
        casing: 'snake_case'
      }
    )

    return callback(db, {
      user
    })
}

The withAuthenticatedDrizzle helper method is what we’ll be using to query our database (instead of using the db object directly like you normally would). This helper method will attach our Kinde access token to each request so that Neon can see it.

Lets see an example in src/app/todos/_queries/get-todos.ts:

import 'server-only'

import { withAuthenticatedDrizzle } from "@/server/db"

export const getTodos = async () => {
    return await withAuthenticatedDrizzle(async (db, ctx) => {
        return await db.query.todos.findMany({
            orderBy: (todos, {asc}) => [asc(todos.id)]
        })
    })
}

And the same goes for CRUD actions - lets look at src/app/todos/_actions/add-todo.ts:

"use server"

import { withAuthenticatedDrizzle } from "@/server/db"
import { todos } from "@/server/db/schema"
import { revalidatePath } from "next/cache"


export const addTodo = async (formData: FormData) => {
    const task = formData.get("task")?.toString() ?? ''
    await withAuthenticatedDrizzle(async (db, ctx) => { 
        await db.insert(todos).values({
            task
        })
    })

    revalidatePath("/todos")
}

What exactly is this achieving?

• Each database request is now checked for authorization before-hand (middleware!)

• Each request will obey whatever RLS policies we’ve defined. Meaning, we’re essentially attaching WHERE clauses to our queries behind the scenes!

Lets see it in action

Launch the app, log in, and navigate to the /todos page - lets add a few.

Try toggling the todo, deleting and updating it - all should work as expected.

Now, lets log out and log back in - but this time, create a new user. Run the windows side by side for maximum effect.

Did you notice something? The new user doesn’t see the others Todos!

Lets try adding a few:

We’ve achieved complete data segregation without writing a single WHERE clause. How cool is that?

Every rose has its thorn, so lets talk about downsides.

Don’t rely entirely on RLS

It is always more secure to write these WHERE clauses yourself. Think of RLS as a safety net to protect your tables. Relying on RLS for your WHERE clauses can lead to bad habits—what if you add a new table and forget to apply RLS?

Writing explicit WHERE clauses also greatly improves code readability - it allows you to know at a glance whether a query is being filtered or not.

Moreover, an explicit WHERE clause will always perform better. RLS is essentially a WHERE clause that is evaluated for each row you request access to - which works fine for small reads and writes, but performance can suffer when selecting large amounts of data or joining large tables.

Bonus round: Multi-tenant RLS (Kinde organizations)

So drizzle-orm/neon provides us an authUid method for comparing the user id in the auth token against a given column - what do we do if we want to separate data for multiple organizations, like a B2B or B2B2C application?

Lets check out src/server/db/utils/auth.ts:

import { sql } from "drizzle-orm";
import type { AnyPgColumn } from "drizzle-orm/pg-core";

export const authOrg = (orgIdColumn: AnyPgColumn) => {
    return sql`(select auth.session()->>'org_code' = ${orgIdColumn}::text)`;
};

This is a helper method I’ve created that’s doing essentially the same thing the authUid helper method is - but instead of reading auth.user_id() (the sub field in our Kinde token), we’re looking at the session() object itself - which is essentially the rest of the Access token in JSON format. From there we extract the org_code, which is what our current logged in organization is, and then we can compare it against a given org_code column.

Open up src/server/db/schema.ts again, and lets take a look at the orgTodos table:

export const orgTodos = pgTable(
    "org_todos",
    {
        id: integer().primaryKey().generatedByDefaultAsIdentity(),
        task: text().notNull(),
        completed: boolean().notNull().default(false),
        // Note the use of sql`(auth.session()->>'org_code')` as our default to auto-fill our current organization code.
        orgCode: text().notNull().default(sql`(auth.session()->>'org_code')`),
    },
    (table) => [
        // This is basically the same as the original todos table,
        // but instead of using authUid(table.userId),
        // we use authOrg(table.orgCode)
        crudPolicy({
            role: authenticatedRole,
            read: authOrg(table.orgCode),
            modify: authOrg(table.orgCode),
        }),
    ],
);

Now on the /todos page, lets create a couple of todos in our current organizations todo list:

Now using the switcher in the top right, lets swap to another organization we have access to. And what do you know, it works! We can’t see the other organization’s todos.

Final thoughts

RLS doesn’t have to be scary anymore - with the latest version of Drizzle ORM with Neon Authorize, it’s as easy as it has ever been. Efficient use of RLS can ensure efficient data segregation and authorization, and should be enabled on all of your tables by default.

Although RLS provides a robust safety net, it's important to complement it with explicit WHERE clauses for enhanced security and performance. This setup not only enhances the security of your application but also improves the overall development experience by reducing the complexity of managing access controls.