N
Neon•7mo ago
stormy-gold

Custom JWT with RLS Authorize

I am passing a jwt token to neon with a payload of 
{
  "operator_id": "1",
  "sub": "1",
  "exp": 1740683759,
  "iat": 1740680159
}
{
  "operator_id": "1",
  "sub": "1",
  "exp": 1740683759,
  "iat": 1740680159
}
I have enabled RLS and heres a snippit of my schema written in drizzle 
export const employee = pgTable(
  "employee",
  {
    employeeId: serial("employeeId").primaryKey(),
    userId: integer("userId")
      .notNull()
      .references(() => users.id),
    positionId: integer("positionId")
      .notNull()
      .references(() => position.positionId),
    operatorId: integer("operatorId")
      .notNull()
      .references(() => operator.operatorId),
  },
  (t) => ({
    employeeSelectPolicy: pgPolicy("employee_select", {
      for: "select",
      to: "authenticated",
      using: sql`(select auth.operator_id() = operatorId)`,
    }),
    employeeInsertPolicy: pgPolicy("employee_insert", {
      for: "insert",
      to: "authenticated",
      withCheck: sql`(select auth.operator_id() = operatorId)`,
    }),
    employeeUpdatePolicy: pgPolicy("employee_update", {
      for: "update",
      to: "authenticated",
      using: sql`(select auth.operator_id() = operatorId)`,
    }),
    employeeDeletePolicy: pgPolicy("employee_delete", {
      for: "delete",
      to: "authenticated",
      using: sql`(select auth.operator_id() = operatorId)`,
    }),
  })
);
export const employee = pgTable(
  "employee",
  {
    employeeId: serial("employeeId").primaryKey(),
    userId: integer("userId")
      .notNull()
      .references(() => users.id),
    positionId: integer("positionId")
      .notNull()
      .references(() => position.positionId),
    operatorId: integer("operatorId")
      .notNull()
      .references(() => operator.operatorId),
  },
  (t) => ({
    employeeSelectPolicy: pgPolicy("employee_select", {
      for: "select",
      to: "authenticated",
      using: sql`(select auth.operator_id() = operatorId)`,
    }),
    employeeInsertPolicy: pgPolicy("employee_insert", {
      for: "insert",
      to: "authenticated",
      withCheck: sql`(select auth.operator_id() = operatorId)`,
    }),
    employeeUpdatePolicy: pgPolicy("employee_update", {
      for: "update",
      to: "authenticated",
      using: sql`(select auth.operator_id() = operatorId)`,
    }),
    employeeDeletePolicy: pgPolicy("employee_delete", {
      for: "delete",
      to: "authenticated",
      using: sql`(select auth.operator_id() = operatorId)`,
    }),
  })
);
When I try to query for all the records I get nothing. as soon as I change to neondb_owner, everything works. Could anyone help me fix the issue
54 Replies
plain-purple
plain-purple•7mo ago
I think auth.operator_id() is the issue. The auth.user_id() pulls from the sub field which in your example is the same as the operator_id Can you try replacing auto.operator_id() with auth.user_id()?
plain-purple
plain-purple•7mo ago
docs for the above ☝️ https://neon.tech/docs/guides/neon-rls-authorize#how-the-pgsessionjwt-extension-works If you needed to, I believe you could use auth.session() -> 'operator_id' to retrieve that field instead. https://github.com/neondatabase/pg_session_jwt?tab=readme-ov-file#3-authsession--jsonb
Neon
About Neon RLS Authorize - Neon Docs
JSON Web Tokens (JWT) Row level Security (RLS) How Neon RLS Authorize works Neon RLS Authorize Tutorial Postgres Row Level Security tutorial Simplify RLS with Drizzle Neon RLS Authorize integrates wit...
GitHub
GitHub - neondatabase/pg_session_jwt: Postgres Extension for JWT Se...
Postgres Extension for JWT Sessions. Contribute to neondatabase/pg_session_jwt development by creating an account on GitHub.
stormy-gold
stormy-goldOP•7mo ago
Hey bryan, I updated the policy but still no luck 
export const employee = pgTable(
  "employee",
  {
    employeeId: serial("employeeId").primaryKey(),
    userId: integer("userId")
      .notNull()
      .references(() => users.id),
    positionId: integer("positionId")
      .notNull()
      .references(() => position.positionId),
    operatorId: integer("operatorId")
      .notNull()
      .references(() => operator.operatorId),
  },
  (t) => ({
    employeeSelectPolicy: pgPolicy("employee_select", {
      for: "select",
      to: "authenticated",
      using: sql`(select (auth.session() -> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeInsertPolicy: pgPolicy("employee_insert", {
      for: "insert",
      to: "authenticated",
      withCheck: sql`(select (auth.session() -> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeUpdatePolicy: pgPolicy("employee_update", {
      for: "update",
      to: "authenticated",
      using: sql`(select (auth.session() -> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeDeletePolicy: pgPolicy("employee_delete", {
      for: "delete",
      to: "authenticated",
      using: sql`(select (auth.session() -> 'operator_id')::int = ${t.operatorId})`,
    }),
  })
);
export const employee = pgTable(
  "employee",
  {
    employeeId: serial("employeeId").primaryKey(),
    userId: integer("userId")
      .notNull()
      .references(() => users.id),
    positionId: integer("positionId")
      .notNull()
      .references(() => position.positionId),
    operatorId: integer("operatorId")
      .notNull()
      .references(() => operator.operatorId),
  },
  (t) => ({
    employeeSelectPolicy: pgPolicy("employee_select", {
      for: "select",
      to: "authenticated",
      using: sql`(select (auth.session() -> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeInsertPolicy: pgPolicy("employee_insert", {
      for: "insert",
      to: "authenticated",
      withCheck: sql`(select (auth.session() -> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeUpdatePolicy: pgPolicy("employee_update", {
      for: "update",
      to: "authenticated",
      using: sql`(select (auth.session() -> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeDeletePolicy: pgPolicy("employee_delete", {
      for: "delete",
      to: "authenticated",
      using: sql`(select (auth.session() -> 'operator_id')::int = ${t.operatorId})`,
    }),
  })
);
plain-purple
plain-purple•7mo ago
oh, crap it might be the ->> operator
plain-purple
plain-purple•7mo ago
https://neon.tech/postgresql/postgresql-json-functions/postgresql-jsonb-operators
Neon
PostgreSQL JSONB Operators
In this tutorial, you will learn about the most commonly used PostgreSQL JSONB operators to process JSONB data effectively.
plain-purple
plain-purple•7mo ago
ex here: https://neon.tech/postgresql/postgresql-json-functions/postgresql-jsonb-operators#2-operator-greatergreater
Neon
PostgreSQL JSONB Operators
In this tutorial, you will learn about the most commonly used PostgreSQL JSONB operators to process JSONB data effectively.
stormy-gold
stormy-goldOP•7mo ago
That's odd, it's still not working. To ensure there was no type error, I made both of the operator id int. 
export const employee = pgTable(
  "employee",
  {
    employeeId: serial("employeeId").primaryKey(),
    userId: integer("userId")
      .notNull()
      .references(() => users.id),
    positionId: integer("positionId")
      .notNull()
      .references(() => position.positionId),
    operatorId: integer("operatorId")
      .notNull()
      .references(() => operator.operatorId),
  },
  (t) => ({
    employeeSelectPolicy: pgPolicy("employee_select", {
      for: "select",
      to: "authenticated",
      using: sql`(select (auth.session() ->> 'operator_id')::int = (${t.operatorId})::int)`,
    }),
    employeeInsertPolicy: pgPolicy("employee_insert", {
      for: "insert",
      to: "authenticated",
      withCheck: sql`(select (auth.session() ->> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeUpdatePolicy: pgPolicy("employee_update", {
      for: "update",
      to: "authenticated",
      using: sql`(select (auth.session() ->> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeDeletePolicy: pgPolicy("employee_delete", {
      for: "delete",
      to: "authenticated",
      using: sql`(select (auth.session() ->> 'operator_id')::int = ${t.operatorId})`,
    }),
  })
);
export const employee = pgTable(
  "employee",
  {
    employeeId: serial("employeeId").primaryKey(),
    userId: integer("userId")
      .notNull()
      .references(() => users.id),
    positionId: integer("positionId")
      .notNull()
      .references(() => position.positionId),
    operatorId: integer("operatorId")
      .notNull()
      .references(() => operator.operatorId),
  },
  (t) => ({
    employeeSelectPolicy: pgPolicy("employee_select", {
      for: "select",
      to: "authenticated",
      using: sql`(select (auth.session() ->> 'operator_id')::int = (${t.operatorId})::int)`,
    }),
    employeeInsertPolicy: pgPolicy("employee_insert", {
      for: "insert",
      to: "authenticated",
      withCheck: sql`(select (auth.session() ->> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeUpdatePolicy: pgPolicy("employee_update", {
      for: "update",
      to: "authenticated",
      using: sql`(select (auth.session() ->> 'operator_id')::int = ${t.operatorId})`,
    }),
    employeeDeletePolicy: pgPolicy("employee_delete", {
      for: "delete",
      to: "authenticated",
      using: sql`(select (auth.session() ->> 'operator_id')::int = ${t.operatorId})`,
    }),
  })
);
My database connection 
"use server";

import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import { auth } from "@/auth"; // Ensure this returns your session

export interface SessionUserType {
  access_token?: string;
  id: string;
  name: string;
  email: string;
}

export async function getDb() {
  const sqlClient = neon(process.env.DATABASE_AUTHENTICATED_URL!, {
    authToken: async () => {
      // Retrieve the session from your auth provider
      const session = await auth();


      // If there's no session, use a fallback token (for development only)
      if (!session) {
        console.warn("No session found; using fallback token");
        return process.env.TEST_ACCESS_TOKEN!;
      }
      
      const user = session.user as SessionUserType;
      const token = user?.access_token ? `${user.access_token}` : '';
      console.log("Retrieved token:", token);
      if (!token) {
        throw new Error("No token");
      }
      return token;
    },
  });
  return drizzle({client:sqlClient});
}
"use server";

import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import { auth } from "@/auth"; // Ensure this returns your session

export interface SessionUserType {
  access_token?: string;
  id: string;
  name: string;
  email: string;
}

export async function getDb() {
  const sqlClient = neon(process.env.DATABASE_AUTHENTICATED_URL!, {
    authToken: async () => {
      // Retrieve the session from your auth provider
      const session = await auth();


      // If there's no session, use a fallback token (for development only)
      if (!session) {
        console.warn("No session found; using fallback token");
        return process.env.TEST_ACCESS_TOKEN!;
      }
      
      const user = session.user as SessionUserType;
      const token = user?.access_token ? `${user.access_token}` : '';
      console.log("Retrieved token:", token);
      if (!token) {
        throw new Error("No token");
      }
      return token;
    },
  });
  return drizzle({client:sqlClient});
}
Retrieved token: eyJhbGciOiJSUzI1NiIsImtpZCI6Im15LWtleS1pZCJ9.eyJvcGVyYXRvcl9pZCI6IjEiLCJzdWIiOiIxIiwiZXhwIjoxNzQwNjk1MjUzLCJpYXQiOjE3NDA2OTE2NTN9.FTlJ86GmFUOIja904tLhjx_mLILdQ4WFvZKDFzv9W7JsXh7sE7SD2Ym439aEl19g1Q-LP0AIPSKugISHEiYxrQlsvITCv-5yYzqqg2AGePDUsTPsbi9Gw-NIx-2nxQ6T1CWWT769FeaIXoCGIwmi2eOqPT_Bgl6HnTU7MuNq-iWsOeSVNez6XRRlCQaODyuuZranL-Aed3fDpJqRXBvJYmvrjB4m1Jj9I-gEbhmWS992XfWEafJGhJfCuf7IrqHKa-0galK3095W-VwzaiBIPeodV00r-vqi9xt3FYeZeywYxrAQUQuqwMQ5HQPonNTdw-iY7j6NVeei4xHtKUXJeg

Which decodes to

{
  "operator_id": "1",
  "sub": "1",
  "exp": 1740695253,
  "iat": 1740691653
}
Retrieved token: eyJhbGciOiJSUzI1NiIsImtpZCI6Im15LWtleS1pZCJ9.eyJvcGVyYXRvcl9pZCI6IjEiLCJzdWIiOiIxIiwiZXhwIjoxNzQwNjk1MjUzLCJpYXQiOjE3NDA2OTE2NTN9.FTlJ86GmFUOIja904tLhjx_mLILdQ4WFvZKDFzv9W7JsXh7sE7SD2Ym439aEl19g1Q-LP0AIPSKugISHEiYxrQlsvITCv-5yYzqqg2AGePDUsTPsbi9Gw-NIx-2nxQ6T1CWWT769FeaIXoCGIwmi2eOqPT_Bgl6HnTU7MuNq-iWsOeSVNez6XRRlCQaODyuuZranL-Aed3fDpJqRXBvJYmvrjB4m1Jj9I-gEbhmWS992XfWEafJGhJfCuf7IrqHKa-0galK3095W-VwzaiBIPeodV00r-vqi9xt3FYeZeywYxrAQUQuqwMQ5HQPonNTdw-iY7j6NVeei4xHtKUXJeg

Which decodes to

{
  "operator_id": "1",
  "sub": "1",
  "exp": 1740695253,
  "iat": 1740691653
}
plain-purple
plain-purple•7mo ago
i'm stumped, lemme get some others to help out
adverse-sapphire
adverse-sapphire•7mo ago
Is there any error? Or just no results?
eastern-cyan
eastern-cyan•7mo ago
also, would it be possible if you connect directly to your neon database and run 
\d+ employee
\d+ employee
and paste the result, esp. the Policies: section?
eastern-cyan
eastern-cyan•7mo ago
I think you would need to use https://www.postgresql.org/docs/current/app-psql.html client
PostgreSQL Documentation
psql
psql psql — PostgreSQL interactive terminal Synopsis psql [option...] [dbname [username]] Description psql is a terminal-based front-end to PostgreSQL. It …
adverse-sapphire
adverse-sapphire•7mo ago
(I believe you can use psql commands in our console sql editor)
stormy-gold
stormy-goldOP•7mo ago
just no result. https://github.com/neondatabase-labs/rls-authorize-demo-custom-jwt I've followed this example and is almost identical to what I've done.
GitHub
GitHub - neondatabase-labs/rls-authorize-demo-custom-jwt: A demo of...
A demo of Neon RLS Authorize with custom generated JWTs - neondatabase-labs/rls-authorize-demo-custom-jwt
adverse-sapphire
adverse-sapphire•7mo ago
Interesting
stormy-gold
stormy-goldOP•7mo ago
No description
adverse-sapphire
adverse-sapphire•7mo ago
One thing that could have happened is that the policy didn't get updated, as they're usually "create if not exists"
stormy-gold
stormy-goldOP•7mo ago
if I run this, the database starts sending data to auth users but no rls 
ALTER TABLE "employee" DISABLE ROW LEVEL SECURITY;
SELECT * FROM "employee";

CREATE POLICY no_filter
  ON "employee"
  FOR SELECT
  TO "authenticated"
  USING (true);
ALTER TABLE "employee" DISABLE ROW LEVEL SECURITY;
SELECT * FROM "employee";

CREATE POLICY no_filter
  ON "employee"
  FOR SELECT
  TO "authenticated"
  USING (true);
I've wiped my entire project, then created a new one to check if that could fix the issue. it still didn't.
eastern-cyan
eastern-cyan•7mo ago
can you make sure that there are no conflicting polices? lets consider only select for now. You should see sth like: 
Policies:
    POLICY "no_filter" FOR SELECT
      TO authenticated
      USING (true)
Policies:
    POLICY "no_filter" FOR SELECT
      TO authenticated
      USING (true)
rather than 
Policies:
    POLICY "no_filter" FOR SELECT
      TO authenticated
      USING (true)
    POLICY "crud-authenticated-policy-select" FOR SELECT
      TO authenticated
Policies:
    POLICY "no_filter" FOR SELECT
      TO authenticated
      USING (true)
    POLICY "crud-authenticated-policy-select" FOR SELECT
      TO authenticated
notice that crud-authenticated-policy is missing the USING (true)
stormy-gold
stormy-goldOP•7mo ago
I can recreate that if you need, I had recreated a new project after trying the no_filter policy. 
export const employee = pgTable(
  "employee",
  {
    employeeId: text("employeeId").primaryKey(),
    userId: text("userId")
      .notNull()
      .references(() => users.id),
    positionId: text("positionId")
      .notNull()
      .references(() => position.positionId),
    operatorId: text("operatorId")
      .notNull()
      .references(() => operator.operatorId),
  },
  () => [
    crudPolicy({
      role: authenticatedRole,
      read: true,
      modify: true,
    }),
  ]
);
export const employee = pgTable(
  "employee",
  {
    employeeId: text("employeeId").primaryKey(),
    userId: text("userId")
      .notNull()
      .references(() => users.id),
    positionId: text("positionId")
      .notNull()
      .references(() => position.positionId),
    operatorId: text("operatorId")
      .notNull()
      .references(() => operator.operatorId),
  },
  () => [
    crudPolicy({
      role: authenticatedRole,
      read: true,
      modify: true,
    }),
  ]
);
even with read and modify being true, it returns nothing
stormy-gold
stormy-goldOP•7mo ago
No description
eastern-cyan
eastern-cyan•7mo ago
again policies are missing the USING () statement - I think this might be an issue
stormy-gold
stormy-goldOP•7mo ago
I was following drizzle's documentation to create the policy. https://orm.drizzle.team/docs/rls#using-with-neon What should I change to add the missing USING ()
Drizzle ORM - Row-Level Security (RLS)
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
stormy-gold
stormy-goldOP•7mo ago
CREATE POLICY "crud-authenticated-policy-select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated" USING (true);
CREATE POLICY "crud-authenticated-policy-select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated" USING (true);
This exists in my migration
eastern-cyan
eastern-cyan•7mo ago
hmm, let's sneak peak at policeis differently: 
select tablename, policyname, cmd, qual, with_check from pg_policies where tablename = 'employee';
select tablename, policyname, cmd, qual, with_check from pg_policies where tablename = 'employee';
could you paste the result of this query?
stormy-gold
stormy-goldOP•7mo ago
No description
eastern-cyan
eastern-cyan•7mo ago
yeah, for select one qual should be true sth is off with migrations that drizzle does -- let's exclude drizzle migrations out of the picture for a second let's modify the select policy directly in postgres and check if you're getting your rows later let me prep the statement. gimme sec
stormy-gold
stormy-goldOP•7mo ago
Is my drizzle config wrong? 
import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";

config({ path: ".env" });
export default defineConfig({
  schema: "./db/schema.ts",
  out: "./migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  }
});
import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";

config({ path: ".env" });
export default defineConfig({
  schema: "./db/schema.ts",
  out: "./migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  }
});
thanks
eastern-cyan
eastern-cyan•7mo ago
let's divide and conquer 🙂 let's fix manually just the db to confirm my hipothesis so that later we know what can (or can't) be fixed in drizzle impl 
alter policy crud-authenticated-policy-select ON employee using (true);
alter policy crud-authenticated-policy-select ON employee using (true);
and later just to double check pls again run 
select tablename, policyname, cmd, qual, with_check from pg_policies where tablename = 'employee';
select tablename, policyname, cmd, qual, with_check from pg_policies where tablename = 'employee';
stormy-gold
stormy-goldOP•7mo ago
No description
eastern-cyan
eastern-cyan•7mo ago
ok, cool - your app at runtime should now see some rows returned from db, right?
stormy-gold
stormy-goldOP•7mo ago
yep
eastern-cyan
eastern-cyan•7mo ago
❤️
stormy-gold
stormy-goldOP•7mo ago
now I need to try out auth.session() out what was the issue btw
eastern-cyan
eastern-cyan•7mo ago
policy was missing the USING() part so you might have some error in your drizzle code
stormy-gold
stormy-goldOP•7mo ago
I've been following documentations, I couldn't wrap my head around the issue I'm new to rls, is select the only one that uses USING() ?
eastern-cyan
eastern-cyan•7mo ago
please follow the example from https://github.com/neondatabase-labs/rls-authorize-demo-custom-jwt/blob/4d8140ba9ba4286653b9f717f15f546f8c9be3cf/src/db/schema.ts#L13 if you just want true then 
-    read: sql`(select auth.session()->>'tenant_id' = ${table.id}::text)`,
+    read: sql`true`,
-    read: sql`(select auth.session()->>'tenant_id' = ${table.id}::text)`,
+    read: sql`true`,
eastern-cyan
eastern-cyan•7mo ago
um, it actually depends whether policy is for SELECT / UPDATE / INSERT / DELETE https://www.postgresql.org/docs/current/sql-createpolicy.html#SQL-CREATEPOLICY-SELECT
PostgreSQL Documentation
CREATE POLICY
CREATE POLICY CREATE POLICY — define a new row-level security policy for a table Synopsis CREATE POLICY name ON table_name …
eastern-cyan
eastern-cyan•7mo ago
for insert there is WITH CHECK
stormy-gold
stormy-goldOP•7mo ago
I changed it to true to test it, I will retrieve data from token and let you know how it goes!
eastern-cyan
eastern-cyan•7mo ago
using vs withCheck is also visible in the docs that you previousely shared -> https://orm.drizzle.team/docs/rls#using-with-neon but yeah, step by step, little by little -- one change at a time to verify if you're still on the right track 🙂 let us know how it went 🤞 !
stormy-gold
stormy-goldOP•7mo ago
When I run this in the console, it works as intended. 
ALTER POLICY employee_select ON employee USING (auth.session() ->> 'operator_id' = employee."operatorId")
ALTER POLICY employee_select ON employee USING (auth.session() ->> 'operator_id' = employee."operatorId")
but my migration file has 
CREATE POLICY "employee_select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated" USING ((select auth.session()->>'operator_id' = "employee"."operatorId"::text));--> statement-breakpoint
CREATE POLICY "employee_select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated" USING ((select auth.session()->>'operator_id' = "employee"."operatorId"::text));--> statement-breakpoint
which does not apply the using clause Is there a fix for this? it would be really difficult to manually setup each policy for each table.
eastern-cyan
eastern-cyan•7mo ago
hmm, I just manually run this: 
create table employee ("operatorId" int);

CREATE POLICY "employee_select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated" USING ((select auth.session()->>'operator_id' = "employee"."operatorId"::text));

select tablename, policyname, cmd, qual, with_check from pg_policies where tablename = 'employee';
create table employee ("operatorId" int);

CREATE POLICY "employee_select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated" USING ((select auth.session()->>'operator_id' = "employee"."operatorId"::text));

select tablename, policyname, cmd, qual, with_check from pg_policies where tablename = 'employee';
and qual has value: 
 tablename |   policyname    |  cmd   |                                         qual                                         | with_check 
-----------+-----------------+--------+--------------------------------------------------------------------------------------+------------
 employee  | employee_select | SELECT | ( SELECT ((auth.session() ->> 'operator_id'::text) = (employee."operatorId")::text)) | 
(1 row)
 tablename |   policyname    |  cmd   |                                         qual                                         | with_check 
-----------+-----------------+--------+--------------------------------------------------------------------------------------+------------
 employee  | employee_select | SELECT | ( SELECT ((auth.session() ->> 'operator_id'::text) = (employee."operatorId")::text)) | 
(1 row)
sth is off with drizzle migration then? 1. how are you running your migrations? 2. which version of drizzle do you use? oh... there might be one more issue actually pg_session_jwt extention might not be loaded when the migrations are applied could you make sure to run 
create extension pg_session_jwt;
create extension pg_session_jwt;
before you run your rls drizzle migrations?
stormy-gold
stormy-goldOP•7mo ago
oh, I did create the extension after the migration. let me retry with the extension first.
eastern-cyan
eastern-cyan•7mo ago
this is more safe operation 
create extension if not exists pg_session_jwt;
create extension if not exists pg_session_jwt;
stormy-gold
stormy-goldOP•7mo ago
still the same let me try this too
eastern-cyan
eastern-cyan•7mo ago
ok, so let me try to summarize. whenever you run 
bun run db:generate
bun run db:migrate
bun run db:generate
bun run db:migrate
and then 
select tablename, policyname, cmd, qual, with_check from pg_policies where tablename = 'employee';
select tablename, policyname, cmd, qual, with_check from pg_policies where tablename = 'employee';
you actually see RLS policy but the qual is empty? but if you would run your drizzle generated migrations manually instead of bun run db:migrate step then everything works?
stormy-gold
stormy-goldOP•7mo ago
no, I have the rls policy in my schema which I migrate - qual is empty If I paste the SQL equivalent in the console to alter the policy, it works
eastern-cyan
eastern-cyan•7mo ago
interesting, seems like some strange bug related to drizzle-kit migrate then which version of drizzle-kit do you use?
stormy-gold
stormy-goldOP•7mo ago
0.30.4
eastern-cyan
eastern-cyan•7mo ago
tbh I'm running out of ideas at this point. From what I understand migrations are applied but for some reason USING is nullified by drizzle-kit migrate I'd consider reporting this to https://github.com/drizzle-team/drizzle-orm/issues that being said - I was reproducing only at the postgres level before we reached this conclusion. I was not debugging your code nor running drizzle-kit commands on my end...
GitHub
Issues · drizzle-team/drizzle-orm
Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅 - Issues · drizzle-team/drizzle-orm
eastern-cyan
eastern-cyan•7mo ago
for some reason USING is nullified by drizzle-kit migrate
what I mean by that is drizzle generates 
CREATE POLICY "employee_select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated" USING ((select auth.session()->>'operator_id' = "employee"."operatorId"::text));
CREATE POLICY "employee_select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated" USING ((select auth.session()->>'operator_id' = "employee"."operatorId"::text));
but the result of running drizzle-kit migrate results in a policy that would be generated as 
CREATE POLICY "employee_select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated";
CREATE POLICY "employee_select" ON "employee" AS PERMISSIVE FOR SELECT TO "authenticated";
at least for what I understood from your messages
stormy-gold
stormy-goldOP•7mo ago
yeah, that's what it is doing any recommendation for me on how to write the polices in raw sql and migrate them after migrating the drizzle schema?
stormy-gold
stormy-goldOP•7mo ago
@mrl5 I found someone with a similar bug https://github.com/drizzle-team/drizzle-orm/issues/3504 I was using drizzle-kit push instead of drizzle-kit migrate. Apparently drizzle-kit push doesn't apply the RLS policies properly. drizzle-kit migrate fixed everything. Thanks again for your help : ) Couldn't have found it without you !
GitHub
[BUG]: RLS Policies not applied with push but applied with `migra...
Report hasn't been filed before. I have verified that the bug I'm about to report hasn't been filed before. What version of drizzle-orm are you using? 0.36.0 What version of drizzle-kit...
absent-sapphire
absent-sapphire•4mo ago
guys, thanks so much for this. I spent so much time searching for this solution

Did you find this page helpful?