Neon•2mo ago

genetic-orange

Clerk + Neon RLS configuration

Hello, I have setup Clerk with Neon/Drizzle but am getting RLS errors in production when I enable RLS on the Users table:

  query: 'select "id", "clerkId", "firstName", "lastName", "email", "role", "analyticsEnabled", "errorMonitoringEnabled" from "User" where "User"."clerkId" = $1',
  params: [Array],
  [cause]: Error [NeonDbError]: Server error (HTTP status 500): "{\"message\":\"could not set up the JWT authorization database extension\",\"code\":\"\",\"detail\":null,\"hint\":null,\"position\":null,\"internalPosition\":null,\"internalQuery\":null,\"severity\":\"\",\"where\":null,\"table\":null,\"column\":null,\"schema\":null,\"dataType\":null,\"constraint\":null,\"file\":null,\"line\":null,\"routine\":null}"
      at bv.execute (.next/server/chunks/6880.js:30:3757)
      at async (.next/server/chunks/6880.js:2:12144)
      at async p.queryWithCache (.next/server/chunks/6880.js:34:10323)
      at async p.execute (.next/server/chunks/6880.js:2:12101)
      at async (.next/server/app/api/trpc/[trpc]/route.js:8:70264)
      at async bI.middlewares (.next/server/app/api/trpc/[trpc]/route.js:5:7696)
      at async bM (.next/server/app/api/trpc/[trpc]/route.js:8:69)
      at async b (.next/server/app/api/trpc/[trpc]/route.js:5:7932)
      at async (.next/server/app/api/trpc/[trpc]/route.js:1:36558) {
    severity: undefined,
    code: undefined,
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: undefined,
    line: undefined,
    routine: undefined,
    sourceError: undefined
  }
}

  query: 'select "id", "clerkId", "firstName", "lastName", "email", "role", "analyticsEnabled", "errorMonitoringEnabled" from "User" where "User"."clerkId" = $1',
  params: [Array],
  [cause]: Error [NeonDbError]: Server error (HTTP status 500): "{\"message\":\"could not set up the JWT authorization database extension\",\"code\":\"\",\"detail\":null,\"hint\":null,\"position\":null,\"internalPosition\":null,\"internalQuery\":null,\"severity\":\"\",\"where\":null,\"table\":null,\"column\":null,\"schema\":null,\"dataType\":null,\"constraint\":null,\"file\":null,\"line\":null,\"routine\":null}"
      at bv.execute (.next/server/chunks/6880.js:30:3757)
      at async (.next/server/chunks/6880.js:2:12144)
      at async p.queryWithCache (.next/server/chunks/6880.js:34:10323)
      at async p.execute (.next/server/chunks/6880.js:2:12101)
      at async (.next/server/app/api/trpc/[trpc]/route.js:8:70264)
      at async bI.middlewares (.next/server/app/api/trpc/[trpc]/route.js:5:7696)
      at async bM (.next/server/app/api/trpc/[trpc]/route.js:8:69)
      at async b (.next/server/app/api/trpc/[trpc]/route.js:5:7932)
      at async (.next/server/app/api/trpc/[trpc]/route.js:1:36558) {
    severity: undefined,
    code: undefined,
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: undefined,
    line: undefined,
    routine: undefined,
    sourceError: undefined
  }
}

Can someone please advise how I can resolve this issue? Thank you!

54 Replies

rare-sapphire•2mo ago

Hey! Have you added the pg_session_session extension? And have you set up your JWKS URL in the Neon Console? This doc might be handy to you for setting up RLS with Neon Auth : https://neon.com/docs/guides/neon-rls-clerk

Neon

Secure your data with Clerk and Neon RLS - Neon Docs

Clerk + Neon RLS Neon RLS Tutorial Manual JWT verification Simplify RLS with Drizzle Use Clerk with Neon RLS to add secure, database level authorization to your application. This guide assumes you alr...

genetic-orangeOP•2mo ago

Hi Sam, yes I've added the JWKS URL from Clerk (generated using their JWT template for Neon) Let me double check regarding the pg_session_session extension I have these 2 configured on the Neon branch:

Available JWT/session extensions:
  - pg_session_jwt v0.3.1: pg_session_jwt: manage authentication sessions using JWTs
  - pgjwt v0.2.0: JSON Web Token API for Postgresql

Available JWT/session extensions:
  - pg_session_jwt v0.3.1: pg_session_jwt: manage authentication sessions using JWTs
  - pgjwt v0.2.0: JSON Web Token API for Postgresql

I see it says: "Neon RLS is available only in private preview. Contact Support if you'd like to use it." in the docs, do I need to take any additional steps to turn RLS on in my database? I've raised a support ticket but not sure what the status of it is

rare-sapphire•2mo ago

No additional steps are needed, support should get back to you soon 🙂

genetic-orangeOP•2mo ago

Hi @Sam , I got a reply back from Support but the RLS queries are still failing. Do I need to take any other actions? Same error as before:

  query: 'select "analyticsEnabled", "errorMonitoringEnabled" from "User" where "User"."clerkId" = $1',
  params: [Array],
  [cause]: Error [NeonDbError]: Server error (HTTP status 500): "{\"message\":\"could not set up the JWT authorization database extension\",\"code\":\"\",\"detail\":null,\"hint\":null,\"position\":null,\"internalPosition\":null,\"internalQuery\":null,\"severity\":\"\",\"where\":null,\"table\":null,\"column\":null,\"schema\":null,\"dataType\":null,\"constraint\":null,\"file\":null,\"line\":null,\"routine\":null}"

  query: 'select "analyticsEnabled", "errorMonitoringEnabled" from "User" where "User"."clerkId" = $1',
  params: [Array],
  [cause]: Error [NeonDbError]: Server error (HTTP status 500): "{\"message\":\"could not set up the JWT authorization database extension\",\"code\":\"\",\"detail\":null,\"hint\":null,\"position\":null,\"internalPosition\":null,\"internalQuery\":null,\"severity\":\"\",\"where\":null,\"table\":null,\"column\":null,\"schema\":null,\"dataType\":null,\"constraint\":null,\"file\":null,\"line\":null,\"routine\":null}"

It is not clear from their email if they enabled RLS on the project, seems like they are suggesting me to switch to the Neon Data API which is not realistic for me at this time.

rare-sapphire•2mo ago

Let me ping internally What was your ticket number?

genetic-orangeOP•2mo ago

where can I find the ticket number? ah sorry. Ticket # 7950 - Requesting RLS Access hi @Sam , sorry to ping you again - have you heard anything from support? I am trying to figure out how I can unblock my production release. thanks

rare-sapphire•2mo ago

I haven’t heard back yet. Looks like we are sunsetting Neon RLS, but we should be able to get you set up for now. Sorry for this delay!

genetic-orangeOP•2mo ago

No problem, thanks for letting me know. Just to understand for the future, would the alternative be migrating to the Neon Data API? how substantial of a task would this be?

rare-sapphire•2mo ago

Yes, the Neon Data API is the successor to the Neon RLS. If you enable the Neon Data API, and run normal queries with auth.user_id(), does that work for you? or are you only able to use that through connections coming from the postgrest endpoint?

genetic-orangeOP•2mo ago

Let me try enabling and see if the queries are successful. I am using Drizzle ORM, if it makes any difference

rare-sapphire•2mo ago

Nope, it should all work the same

genetic-orangeOP•2mo ago

Hi @Sam , this is the error I get when I try to enable Neon data API with the attached Clerk JWKS

rare-sapphire•2mo ago

That's most likely because Neon RLS had been enabled, if you try with afresh db/project?

genetic-orangeOP•2mo ago

Hi @Sam , thanks for the response - I tried to enable the Neon Data API on 2 other projects and got the same error. The original queries on the main project still return the same error, so I don't think RLS has been enabled:

prisma:error Server error (HTTP
 status 500): "{\"message\":\"could not set up the JWT authorization database extension\",\"code\":\"\",\"detail\":null,\"hint\":null,\"position\":null,\"internalPosition\":null,\"internalQuery\":null,\"severity\":\"\",\"where\":null,\"table\":null,\"column\":null,\"schema\":null,\"dataType\":null,\"constraint\":null,\"file\":null,\"line\":null,\"routine\":null}"

prisma:error Server error (HTTP
 status 500): "{\"message\":\"could not set up the JWT authorization database extension\",\"code\":\"\",\"detail\":null,\"hint\":null,\"position\":null,\"internalPosition\":null,\"internalQuery\":null,\"severity\":\"\",\"where\":null,\"table\":null,\"column\":null,\"schema\":null,\"dataType\":null,\"constraint\":null,\"file\":null,\"line\":null,\"routine\":null}"

rare-sapphire•2mo ago

And if you access the tables from the PostgREST endpoint does it work? I think it would be great if the Data API still let users use it as they would Neon RLS. I’ll try some things on my end and forward this internally. Thank you for trying so many things!

genetic-orangeOP•2mo ago

where can I find the PostgREST endpoint URL in the Neon console?

rare-sapphire•2mo ago

It's in the Data API tab

genetic-orangeOP•2mo ago

ah yeah, I am unable to enable the Data API

genetic-orangeOP•2mo ago

rare-sapphire•2mo ago

Is that happening even on a fresh project?

genetic-orangeOP•2mo ago

yes just tried a fresh project, with the same Clerk JWKS URL, and got the same error

rare-sapphire•2mo ago

That's unfortunate, thank you for trying. I'll make sure to try things on my end and forward this.

genetic-orangeOP•2mo ago

do you think it could be an issue with Clerk? I created a fresh Clerk project yesterday to re-create the JWKS URL, and the project uses the standard JWT template that Clerk provides for Neon. I am not sure if this is helpful but this is how I'm initializing the RLS usage:

// Development client (no RLS)
export function createDrizzleClient() {
  if (!process.env.DATABASE_URL) {
    throw new Error('DATABASE_URL environment variable is required');
  }

  return createNeonClient(process.env.DATABASE_URL);
}

// Production client with RLS support using neon-http for JWT authentication
export function createDrizzleClientWithRLS(jwt?: string) {
  if (!process.env.DATABASE_AUTHENTICATED_URL) {
    throw new Error(
      'DATABASE_AUTHENTICATED_URL environment variable is required for RLS',
    );
  }

  console.log('🔑 Creating RLS client with Neon HTTP driver for JWT authentication');

  // Use neon-http driver with JWT authorization for RLS
  const sql = neon(process.env.DATABASE_AUTHENTICATED_URL, {
    authToken: jwt,
  });

  return drizzleHttp(sql, { schema });
}

// Development client (no RLS)
export function createDrizzleClient() {
  if (!process.env.DATABASE_URL) {
    throw new Error('DATABASE_URL environment variable is required');
  }

  return createNeonClient(process.env.DATABASE_URL);
}

// Production client with RLS support using neon-http for JWT authentication
export function createDrizzleClientWithRLS(jwt?: string) {
  if (!process.env.DATABASE_AUTHENTICATED_URL) {
    throw new Error(
      'DATABASE_AUTHENTICATED_URL environment variable is required for RLS',
    );
  }

  console.log('🔑 Creating RLS client with Neon HTTP driver for JWT authentication');

  // Use neon-http driver with JWT authorization for RLS
  const sql = neon(process.env.DATABASE_AUTHENTICATED_URL, {
    authToken: jwt,
  });

  return drizzleHttp(sql, { schema });
}

rare-sapphire•2mo ago

I doubt it since its at the role creation for the Data API. I'll try and reproduce

genetic-orangeOP•2mo ago

thank you! I am going to log off for now but will be back in a few hours. hi @Sam did you have any luck reproducing the issue?

funny-blue•2mo ago

Hi @llobanov do you want to use Neon RLS or the Data API? I see some messages related for the Data API for the code snippet you provider is for Neon RLS

genetic-orangeOP•2mo ago

hi Luis, ideally I wanted to use Neon RLS but Sam suggested to try enabling the Neon Data API, which threw an error when I tried to enable it. Ideally I would like to get RLS working as it is blocking my production release

rare-sapphire•2mo ago

For context, it's looking like we are sunsetting Neon RLS, so I'm looking for a way to use the Data API in the same way that one might with Neon RLS. I.e. without touching the exposed postgREST compatible API

funny-blue•2mo ago

Hi @llobanov indeed we are sunsetting Neon RLS. In order to migrate to the Data API you will need to delete the authenticator and authenticated roles of your database and then enable the Data API. If you want to still use Neon RLS you'll need to use the API (https://api-docs.neon.tech/reference/getting-started-with-neon-api) and call the "Add JWKs" endpoint with role_names set to authenticated and anonymous

Neon

Neon API Reference

This page will help you get started with the Neon API

genetic-orangeOP•2mo ago

I see. In that case why did I get the above error when trying to enable Data API on a completely new project with no pre-existing configuration?

funny-blue•2mo ago

you got this on a new project?

foreign-sapphire•2mo ago

@llobanov as for an example how to use drizzle for the schema and rls management + the new data api, you can take a look at this template: https://github.com/neondatabase-labs/neon-data-api-neon-auth . It uses neon auth, but it shouldn't be that hard to adapt to clerk.

GitHub

GitHub - neondatabase-labs/neon-data-api-neon-auth: A note taking a...

A note taking app powered by Neon Data API and Neon Auth - neondatabase-labs/neon-data-api-neon-auth

genetic-orangeOP•2mo ago

yes, sorry I am away from the computer ATM so am a bit slow to respond thank you! I will have a look

funny-blue•2mo ago

Could you please try again and check the network tab? Also, confirm that the authenticator role does not exist

genetic-orangeOP•2mo ago

Hi @luís @Luis @Sam , this is the error I get when I try to delete the authenticated and authenticator roles as instructuted. I have already: - Dropped all RLS policies that used these roles - Disabled RLS on all tables - Confirmed no objects depend on these roles Do you know why I might be getting this error? Thanks

funny-blue•2mo ago

Can you delete them through the UI?

genetic-orangeOP•2mo ago

unable to delete through UI as well

funny-blue•2mo ago

There’s a dedicated roles page in the branch page

genetic-orangeOP•2mo ago

hi @luís , thank you that worked - I was able to enable the Data API. is there a reason why the authenticated role cannot login?

  query: 'select "id", "clerkId", "firstName", "lastName", "email", "role", "analyticsEnabled", "errorMonitoringEnabled" from "User" where "User"."clerkId" = $1',
  params: [Array],
  [cause]: Error [NeonDbError]: Server error (HTTP status 500): "{\"message\":\"role \\\"authenticated\\\" is not permitted to log in\",\"code\":\"28000\",\"detail\":null,\"hint\":null,\"position\":null,\"internalPosition\":null,\"internalQuery\":null,\"severity\":\"FATAL\",\"where\":null,\"table\":null,\"column\":null,\"schema\":null,\"dataType\":null,\"constraint\":null,\"file\":\"miscinit.c\",\"line\":\"851\",\"routine\":\"InitializeSessionUserId\"}"
      at async (db/drizzle/routers/user.ts:37:24)
    35 |         // Try with authenticated client first
    36 |         const authClient = getAuthenticatedClient(ctx.drizzle, ctx.jwt);
  > 37 |         existingUser = await authClient

  query: 'select "id", "clerkId", "firstName", "lastName", "email", "role", "analyticsEnabled", "errorMonitoringEnabled" from "User" where "User"."clerkId" = $1',
  params: [Array],
  [cause]: Error [NeonDbError]: Server error (HTTP status 500): "{\"message\":\"role \\\"authenticated\\\" is not permitted to log in\",\"code\":\"28000\",\"detail\":null,\"hint\":null,\"position\":null,\"internalPosition\":null,\"internalQuery\":null,\"severity\":\"FATAL\",\"where\":null,\"table\":null,\"column\":null,\"schema\":null,\"dataType\":null,\"constraint\":null,\"file\":\"miscinit.c\",\"line\":\"851\",\"routine\":\"InitializeSessionUserId\"}"
      at async (db/drizzle/routers/user.ts:37:24)
    35 |         // Try with authenticated client first
    36 |         const authClient = getAuthenticatedClient(ctx.drizzle, ctx.jwt);
  > 37 |         existingUser = await authClient

funny-blue•2mo ago

Are you still using drizzle? With the Data API you can’t use Drizzle with the neon driver directly You must use the PostgREST sdk

foreign-sapphire•2mo ago

You can use drizzle for the schema/migrations/rls and the PostgREST sdk for queries

genetic-orangeOP•2mo ago

I see... I am not sure if that will work for me. Will experiment with the PostgREST SDK now.

foreign-sapphire•2mo ago

You can use this converter to convert from sql to the PostgREST syntax https://supabase.com/docs/guides/api/sql-to-rest Just to understand a bit more the use case: are you using Neon RLS to do queries directly from the frontend ?

SQL to REST API Translator | Supabase Docs

Translate SQL queries to HTTP requests and Supabase client code

genetic-orangeOP•2mo ago

@Luis I have a Next.js front-end and back-end server - currently all Drizzle queries were going through API endpoints in the back-end server. Queries were working with the neondb_owner role, however were failing in staging/production when RLS was enabled using Clerk auth as the JWT provider. I've now enabled the Data API, trying to switch some of the queries to PostgREST to test, however it seems like I cannot easily query my tables which have Drizzle schema with enums...

.trpc","message":"❌ tRPC failed on users.getPostHogPreferences: Failed to get PostHog preferences","payload":{"appName":"ella-app","moduleName":"backend","methodName":"fetchRequestHandler","data":[{"objectName":"error","objectValue":{"error":{"cause":{},"code":"INTERNAL_SERVER_ERROR","name":"TRPCError"}}}]}}
PostHog Error Tracking: Captured TRPCError in /api/trpc/users.getPostHogPreferences
Enum-safe query failed for table User: {
  message: 'Failed to deserialize json: invalid type: string "\\"BusinessFactorCategory\\"", expected a borrowed string at line 1 column 481'
}
Enum-safe query error for User: Error: Database query failed: Failed to deserialize json: invalid type: string "\"BusinessFactorCategory\"", expected a borrowed string at line 1 column 481

.trpc","message":"❌ tRPC failed on users.getPostHogPreferences: Failed to get PostHog preferences","payload":{"appName":"ella-app","moduleName":"backend","methodName":"fetchRequestHandler","data":[{"objectName":"error","objectValue":{"error":{"cause":{},"code":"INTERNAL_SERVER_ERROR","name":"TRPCError"}}}]}}
PostHog Error Tracking: Captured TRPCError in /api/trpc/users.getPostHogPreferences
Enum-safe query failed for table User: {
  message: 'Failed to deserialize json: invalid type: string "\\"BusinessFactorCategory\\"", expected a borrowed string at line 1 column 481'
}
Enum-safe query error for User: Error: Database query failed: Failed to deserialize json: invalid type: string "\"BusinessFactorCategory\"", expected a borrowed string at line 1 column 481

Do I need to modify my existing schema in any way? this was the error I was getting before enabling Data API, using the Drizzle SQL queries following on this message, it seems like I cannot keep my existing SQL queries with the Data API. is it correct that I need to switch everything to run through the postgrest endpoint?

funny-blue•2mo ago

Hi, let me remind you again that you can still use Neon RLS. You just need to set it up with the API endpoint And yes, postgrest does not use SQL so you would have to change every query

genetic-orangeOP•2mo ago

ok, yes I see I was able to toggle RLS on all the tables when Data API is active. currently this is blocking me with using PostgREST, it does not seem to work with enums in the Drizzle schema so just to clarify, there is no way for me to use pure SQL queries and use my Drizzle RLS policies? if that is the case I will need to switch providers.

foreign-sapphire•2mo ago

There is a way for you to inject the jwt claims before every query if you want to keep using drizzle for queries. I can get you an example later today (not in front of my laptop currently). And as @luís said you can keep using neon rls, so you are not blocked.

genetic-orangeOP•2mo ago

thanks, I've switched back to Neon RLS for now. I am still having issues making authenticated queries with Neon RLS + Clerk, so I am trying to debug that

foreign-sapphire•2mo ago

What is the error you are having? You might be missing these grants:GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO authenticated; and GRANT SELECT ON ALL TABLES IN SCHEMA public TO anonymous;. For your use case it's probably better that you keep using Neon RLS for now. Note that RLS is a postgres feature (it's not specific to neon). The only thing that Neon RLS gives you is an easier way to pass the user jwt token claims to the postgrest RLS context. We will have a better example this week how to use drizzle with native postgres rls this week.

genetic-orangeOP•2mo ago

Yes I've run the above command. I think my issue is related to Neon's auth.user_id() function as the Clerk JWT returns the Clerk ID in the sub claim, not the DB user_id. I think I managed to produce a fix locally, trying to run the migrations in my CICD now and deploy

foreign-sapphire•2mo ago

ah that might be it. Sorry for all the misunderstanding with the Data API - your use case is still fine to use Neon RLS. We have a workstream for this week to make the messaging more clear around this topic. tldr is: if you are doing queries from the frontend with jwt + rls -> we recommend using the Data API compatible with PostgREST. If you are doing it from the backend (just like your use case) you can use one of the 3 options: Data API, Neon RLS or injecting the JWT claims yourself before any executed query (there is an example for drizzle: https://orm.drizzle.team/docs/rls#using-with-supabase )

Drizzle ORM - Row-Level Security (RLS)

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

genetic-orangeOP•2mo ago

got it, thank you! I've think I've got it working now by adding an extra migration to correctly handle the clerkId in the JWT token. Will let you know if I run into any other issues.

Gaming

Programming

Clerk + Neon RLS configuration

Did you find this page helpful?