Neon•9mo ago

correct-apricot

RLS PostgreSQL with Next Auth?

Can I use Next Auth instead of the providers listed to use RLS?

24 Replies

rare-sapphire•9mo ago

Better Auth + JWT plugin

fair-rose•7mo ago

@A13u Have you implemented the RLS? I am using better auth and Drizzle ORM with Neon. I am stuck on setting up RLS and how to do it?

correct-apricotOP•7mo ago

What stage are you in? Have you set everything up but RLS isn't working?

fair-rose•7mo ago

everything is ready my app is working. all set. just want to set rls for my tables

correct-apricotOP•7mo ago

Did you set this up?

fair-rose•7mo ago

I am following this guide here https://orm.drizzle.team/docs/rls#using-with-neon but not able to figure out

Drizzle ORM - Row-Level Security (RLS)

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

correct-apricotOP•7mo ago

Oh yeah, are you using drizzle's RLS or Neon?

fair-rose•7mo ago

yes with better auth

correct-apricotOP•7mo ago

you used this for the schema right? how do you push changes to neon db

fair-rose•7mo ago

one of the table i have is this:

const websites = pgTable("websites", {
  id: text("id")
    .primaryKey()
    .$default(() => uuidv4()),
  name: text("name").notNull(),
  domain: text("domain").notNull().unique(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
  userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
});

const websites = pgTable("websites", {
  id: text("id")
    .primaryKey()
    .$default(() => uuidv4()),
  name: text("name").notNull(),
  domain: text("domain").notNull().unique(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
  userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
});

to push i use - drizzle-kit push

correct-apricotOP•7mo ago

I remember this, push for some reason does not push the policy properly. use

drizzle-kit migrate

drizzle-kit migrate

instead

fair-rose•7mo ago

you mean generate first then migrate

correct-apricotOP•7mo ago

yep. try it out, it may fix your issue.

fair-rose•7mo ago

but I am at stage where I need to first add RLS policies for my tables

correct-apricotOP•7mo ago

use this to add it to your schema

fair-rose•7mo ago

I just don't understand how to add auth id to verify with userid to confirm that yes it is the right user to modify this table

correct-apricotOP•7mo ago

and then rls should be enabled. do you not have an DATABASE_AUTHENTICATED_URL set up

export const routes = pgTable(
  "routes",
  {
    // Integer primary key
    id: serial("id").primaryKey(),

    // Public-facing UUID
    uuid: uuid("uuid").default(sql`gen_random_uuid()`).unique().notNull(),

    // Timestamps
    created_at: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    updated_at: timestamp("updated_at", { withTimezone: true })
      .defaultNow()
      .notNull(),

    // Fields
    operator_id: integer("operator_id").notNull().references(() => operators.id),
    origin_id: integer("origin_id").notNull().references(() => islands.id),
    destination_id: integer("destination_id").notNull().references(() => islands.id),
    transport_type_id: integer("transport_type_id")
      .notNull()
      .references(() => transport_types.id),
    duration_minutes: integer("duration_minutes").notNull(),
    distance_km: decimal("distance_km", { precision: 10, scale: 2 }),
  },
  (t) => [
    crudPolicy({
      role: authenticatedRole,
      read: sql`
        (
          (auth.session()->>'operator_id')::uuid =
            (
              SELECT "uuid"
              FROM "operators"
              WHERE "id" = ${t.operator_id}
            )
        )
      `,
      modify: sql`
        (
          (auth.session()->>'operator_id')::uuid =
            (
              SELECT "uuid"
              FROM "operators"
              WHERE "id" = ${t.operator_id}
            )
        )
      `,
    }),
  ]
);

export const routes = pgTable(
  "routes",
  {
    // Integer primary key
    id: serial("id").primaryKey(),

    // Public-facing UUID
    uuid: uuid("uuid").default(sql`gen_random_uuid()`).unique().notNull(),

    // Timestamps
    created_at: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    updated_at: timestamp("updated_at", { withTimezone: true })
      .defaultNow()
      .notNull(),

    // Fields
    operator_id: integer("operator_id").notNull().references(() => operators.id),
    origin_id: integer("origin_id").notNull().references(() => islands.id),
    destination_id: integer("destination_id").notNull().references(() => islands.id),
    transport_type_id: integer("transport_type_id")
      .notNull()
      .references(() => transport_types.id),
    duration_minutes: integer("duration_minutes").notNull(),
    distance_km: decimal("distance_km", { precision: 10, scale: 2 }),
  },
  (t) => [
    crudPolicy({
      role: authenticatedRole,
      read: sql`
        (
          (auth.session()->>'operator_id')::uuid =
            (
              SELECT "uuid"
              FROM "operators"
              WHERE "id" = ${t.operator_id}
            )
        )
      `,
      modify: sql`
        (
          (auth.session()->>'operator_id')::uuid =
            (
              SELECT "uuid"
              FROM "operators"
              WHERE "id" = ${t.operator_id}
            )
        )
      `,
    }),
  ]
);

fair-rose•7mo ago

I remember last time I did something like this in supabase but that was in the supabase itself:

const websites = pgTable(
  "websites",
  {
    id: text("id")
      .primaryKey()
      .$default(() => uuidv4()),
    name: text("name").notNull(),
    domain: text("domain").notNull().unique(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
  },
  (table) => [
    pgPolicy("select_own_websites", {
      for: "insert",
      to: "session_user", // authenticated
      withCheck: sql`(( SELECT auth.uid() AS uid) = ${table.userId})`,
    }),
  ],
);

const websites = pgTable(
  "websites",
  {
    id: text("id")
      .primaryKey()
      .$default(() => uuidv4()),
    name: text("name").notNull(),
    domain: text("domain").notNull().unique(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
  },
  (table) => [
    pgPolicy("select_own_websites", {
      for: "insert",
      to: "session_user", // authenticated
      withCheck: sql`(( SELECT auth.uid() AS uid) = ${table.userId})`,
    }),
  ],
);

now i don't understand how to do it here that's the problem like I want only authenticated users can only select, insert, update, delete the websites whose userid match with the auth id

correct-apricotOP•7mo ago

https://neon.tech/docs/guides/neon-rls-tutorial

Neon

Neon RLS tutorial - Neon Docs

Clerk + Neon RLS About Neon RLS Row Level security in Drizzle In this tutorial, you'll set up a sample todos application to learn how Postgres Row Level Security (RLS) policies can protect user data, ...

correct-apricotOP•7mo ago

This shows how the policies can be managed.

correct-apricotOP•7mo ago

I think you may need to implement the custom jwt approach too.

correct-apricotOP•7mo ago

https://github.com/neondatabase-labs/rls-demo-custom-jwt

GitHub

GitHub - neondatabase-labs/rls-demo-custom-jwt: A demo of Neon RLS ...

A demo of Neon RLS with custom generated JWTs. Contribute to neondatabase-labs/rls-demo-custom-jwt development by creating an account on GitHub.

correct-apricotOP•7mo ago

you may still need to look if BetterAuth is already configured with JWT. I made my own with AuthJS

fair-rose•7mo ago

can we test this neon RLS thing in the development ??

Gaming

Programming

RLS PostgreSQL with Next Auth?

Did you find this page helpful?