Secure your app with RLS

Sample project

Neon Data API + Neon Auth

Related docs

Row-Level security in Drizzle

In this tutorial, you'll clone and modify up a sample React.js note-taking app to demonstrate how Postgres Row-Level Security (RLS) provides an additional security layer beyond application logic. The app integrates with a Neon database via the Neon Data API.

For authentication, Neon Auth issues a unique userId in a JSON Web Token (JWT) for each user. This userId is passed to Postgres, where RLS policies enforce access control directly at the database level. This setup ensures each user can only interact with their own notes, even if application-side logic fails. While this example uses Neon Auth, any JWT-issuing provider like Auth0 or Clerk can be used.

Prerequisites

To get started, you'll need:

Neon account: Sign up at Neon and create your first project in AWS (note: Azure regions are not currently supported).
Neon Data API + Neon Auth example application: Clone the sample Neon Data API + Neon Auth repository:
```
git clone https://github.com/neondatabase-labs/neon-data-api-neon-auth.git
```
Follow the instructions in the README to set up Neon Data API with Neon Auth, configure environment variables, and run database migrations.

When enabling Neon Data API, ensure you select Neon Auth with Neon Data API.

Create test users
Start the sample application:
```
npm run dev
```
Open the app in your browser using localhost:5173.
Now, let's create the two users we'll use to show how RLS policies can prevent data leaks between users, and what can go wrong if you don't. The sample app supports Google and Github logins, so let's create one of each. For this guide, we'll call our two users Alice and Bob.
Create your Alice user using Google. Then, using a private browser session, create your Bob user account using Github or other Google account.
Side by side, here's the empty state for both users:
When each user creates a note, it's securely linked to their ownerId in the database schema. Here's the structure of the notes table:
```
{
    id: uuid("id").defaultRandom().primaryKey(),
    ownerId: text("owner_id")
      .notNull()
      .default(sql`auth.user_id()`),
    title: text("title").notNull().default("untitled note"),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow(),
    shared: boolean("shared").default(false),
}
```
The ownerId column is populated directly from the authenticated (auth.user_id()) in the JWT, ensuring that each note is tied to the correct user.
Create notes
Let's create some sample notes for both Alice and Bob.
The paragraphs in the notes are:

The notes act as a top level container for paragraphs. Each paragraph is stored in paragraphs table, linked to the parent note by noteId.

Notes are isolated
In this sample app, isolation of Notes to each user is handled both in the application logic and using Row-level Security (RLS) policies defined in our application's schema file.
Let's take a look at the useNotes function in the src/routes/index.tsx file:
```
function useNotes() {
  const postgrest = usePostgrest();
  const user = useUser({ or: 'redirect' });
  return useQuery({
    queryKey: ['notes'],
    queryFn: async (): Promise<Array<Note>> => {
      // `eq` filter is optional because of RLS. But we send it anyway for
      // performance reasons.
      const { data, error } = await postgrest
        .from('notes')
        .select('id, title, created_at, owner_id, shared')
        .eq('owner_id', user.id)
        .order('created_at', { ascending: false });

      if (error) {
        throw error;
      }

      return data;
    },
  });
}
```
The eq clause is technically enough to make sure data is properly isolated. Neon gets user.id from the Neon Auth JWT and matches that to the owner_id column in the notes tables, so each user can only see their own notes.
Even though isolation is backed by our RLS policies, we include it here for performance reasons: it helps Postgres build a better query plan and use indexes where possible.
RLS policy for viewing notes
In the application's schema.ts file, you can find the RLS policies written in Drizzle that provide access control at the database level. Here is a look at one of those policies:
```
crudPolicy({
  role: authenticatedRole,
  read: authUid(table.ownerId),
  modify: authUid(table.ownerId),
});
```
authUid is a helper function that evaluates to
```
sql`(select auth.user_id() = owner_id)`
```
which is a SQL expression that checks if the owner_id of a row matches the auth.user_id() from the JWT.
This policy ensures that read (SELECT) queries only returns rows where the owner_id matches the auth.user_id() derived from the authenticated user’s JWT. This means that users can only access their own notes. By enforcing this rule at the database level, the RLS policy provides an extra layer of security beyond the application layer.

Remove access control from application code

Now, let's test what happens when we remove access control from the application layer to rely solely on RLS at the database level.

In the src/routes/index.tsx file, modify the useNotes function to remove the eq clause that filters notes by owner_id:

function useNotes() {
  const postgrest = usePostgrest();
  const user = useUser({ or: 'redirect' });
  return useQuery({
    queryKey: ['notes'],
    queryFn: async (): Promise<Array<Note>> => {
      const { data, error } = await postgrest
        .from('notes')
        .select('id, title, created_at, owner_id, shared')
        // .eq("owner_id", user.id)
        .order('created_at', { ascending: false });

      if (error) {
        throw error;
      }

      return data;
    },
  });
}

Check your two open Notes users, reload the page, and see what happens:

isolated notes

Nothing happens. RLS is still in place, and isolation is maintained: no data leaks. 💪

Disable RLS

Let's see what happens when we disable RLS on our notes and paragraphs tables. Go to your project in the Neon Console and in the SQL Editor run:

ALTER TABLE public.notes DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.paragraphs DISABLE ROW LEVEL SECURITY;

data leak

Bob can see all of Alice's notes and paragraphs within them, including the 'inquisitive cyan' note where her birthday party is planned. Alice now knows about her birthday party. Disabling RLS removed all RLS policies, including the crudPolicy on read queries that helped enforce data isolation. Birthday surprise is ruined.

Re-enable RLS

Now, let's re-enable RLS from Neon:

ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.paragraphs ENABLE ROW LEVEL SECURITY;

isolated notes restored

With RLS back on, there are no more data leaks, despite the lack of access control in the application code. In this case, RLS acts as a backstop, preventing unintended data exposure due to application-side mistakes.

Order is restored, thanks to RLS. Now go fix your app before you forget:

function useNotes() {
  const postgrest = usePostgrest();
  const user = useUser({ or: 'redirect' });
  return useQuery({
    queryKey: ['notes'],
    queryFn: async (): Promise<Array<Note>> => {
      const { data, error } = await postgrest
        .from('notes')
        .select('id, title, created_at, owner_id, shared')
        .eq('owner_id', user.id) 
        .order('created_at', { ascending: false });

      if (error) {
        throw error;
      }

      return data;
    },
  });
}

Appendix: Understanding RLS policies in Drizzle

In this section, we provide an overview of the Row-Level Security (RLS) policies implemented in the Notes application, found in the schema.ts file.

These policies are written in Drizzle, which now supports defining RLS policies alongside your schema in code. Writing RLS policies can be complex, so we worked with Drizzle to develop the crudPolicy function – a wrapper that works with Neon’s predefined roles (authenticated and anonymous), letting you consolidate all policies that apply to a given role into a single function. See Row-level Security in the Drizzle docs for details.

For the notes table, the crudPolicy function defines RLS policies for the authenticated role, which is assigned to users who have successfully logged in. The read and modify parameters use the authUid helper function to ensure that users can only read or modify rows where the ownerId matches their own auth.user_id() from the JWT.

// for `notes` table
crudPolicy({
  role: authenticatedRole,
  read: authUid(table.ownerId),
  modify: authUid(table.ownerId),
});

For the paragraphs table, the crudPolicy function also applies to the authenticated role. However, since paragraphs are linked to notes via the noteId, the read and modify parameters use a SQL subquery to check that the owner_id of the associated note matches the auth.user_id() from the JWT. This ensures that users can only read or modify paragraphs that belong to notes they own.

// for `paragraphs` table
crudPolicy({
  role: authenticatedRole,
  read: sql`(select notes.owner_id = auth.user_id() from notes where notes.id = ${table.noteId})`,
  modify: sql`(select notes.owner_id = auth.user_id() from notes where notes.id = ${table.noteId})`,
});

These policies together enforce strict access control at the database level, ensuring that users can only interact with their own notes and paragraphs, regardless of any application-side logic.

In the schema.ts file, you can find additional RLS policies than defined above, which support the "Share Notes" functionality in the application. This feature allows users to share specific notes with others by setting the shared column to true. The RLS policies for the notes table include a condition that permits read access to notes marked as shared, regardless of ownership. The final RLS policy for the notes and paragraphs tables looks like this:

...schema definitions...

// for `notes` table
crudPolicy({
    role: authenticatedRole,
    read: authUid(table.ownerId),
    modify: authUid(table.ownerId),
}),

pgPolicy('shared_policy', {
    for: 'select',
    to: authenticatedRole,
    using: sql`${table.shared} = true`,
})

// for `paragraphs` table
crudPolicy({
    role: authenticatedRole,
    read: sql`(select notes.owner_id = auth.user_id() from notes where notes.id = ${table.noteId})`,
    modify: sql`(select notes.owner_id = auth.user_id() from notes where notes.id = ${table.noteId})`,
}),

pgPolicy('shared_policy', {
    for: 'select',
    to: authenticatedRole,
    using: sql`(select notes.shared from notes where notes.id = ${table.noteId})`,
});

The shared_policy enables any authenticated user to read notes marked as shared (shared = true), allowing others to view shared notes even if they are not the owner. This policy applies similarly to paragraphs, checking if the linked note is shared.

Although RLS permits read access to shared notes for all authenticated users, the shared notes are not directly visible in other users' UI. Instead, sharing occurs via the "Share" button, which copies the note's URL to the clipboard. This URL includes the note's ID, enabling authenticated users to access the shared note and its paragraphs with-in in a read-only mode.

RLS policies table

To check out the RLS policies defined for the notes table in Postgres, run this query:

SELECT * FROM pg_policies WHERE tablename = 'notes';

Here is the output, showing columns policyname, cmd, qual, with_check only:

policyname                      | cmd    | qual                                        | with_check
--------------------------------+--------+---------------------------------------------+---------------------------------------
crud-authenticated-policy-select | SELECT | (SELECT (auth.user_id() = notes.owner_id)) |
crud-authenticated-policy-insert | INSERT |                                            | (SELECT (auth.user_id() = notes.owner_id))
crud-authenticated-policy-update | UPDATE | (SELECT (auth.user_id() = notes.owner_id)) | (SELECT (auth.user_id() = notes.owner_id))
crud-authenticated-policy-delete | DELETE | (SELECT (auth.user_id() = notes.owner_id)) |
shared_policy                    | SELECT | (shared = true)                            |
(5 rows)

To get an understanding of auth.user_id() and the role it plays in these policies, see this explanation.

Secure your app with RLS

Sample project

Related docs

Prerequisites

Create test users

Create notes

Notes are isolated

RLS policy for viewing notes

Remove access control from application code

Disable RLS

Re-enable RLS

Appendix: Understanding RLS policies in Drizzle

Implementing Share Notes functionality

RLS policies table

On this page