RLS not working when connecting using Database connection string with Drizzle ORM

I have this connection.ts

const connectionString = serverEnv.SUPABASE_DATABASE_URL;

const pool = postgres(connectionString, { max: 1 });
export const connection = pool;
export const db: PostgresJsDatabase<typeof schema> = drizzle(pool, { schema });

(async () => {
  try {
    const result = await pool`
      SELECT current_user, session_user, inet_client_addr(), rolname, rolsuper, rolreplication, rolbypassrls 
      FROM pg_roles 
      WHERE rolname = current_user;
    `;
    console.log("DB connection user info:", result[0]);
  } catch (err) {
    console.error("Failed to query user info:", err);
  }
})();

const connectionString = serverEnv.SUPABASE_DATABASE_URL;

const pool = postgres(connectionString, { max: 1 });
export const connection = pool;
export const db: PostgresJsDatabase<typeof schema> = drizzle(pool, { schema });

(async () => {
  try {
    const result = await pool`
      SELECT current_user, session_user, inet_client_addr(), rolname, rolsuper, rolreplication, rolbypassrls 
      FROM pg_roles 
      WHERE rolname = current_user;
    `;
    console.log("DB connection user info:", result[0]);
  } catch (err) {
    console.error("Failed to query user info:", err);
  }
})();

It outputs the user details that is used to connect to the database. It has current_user: 'postgres', rolname: 'postgres', rolbypassrls: true. How can i connect using some other user that respects RLS policies?

5 Replies

garyaustin•3h ago

https://orm.drizzle.team/docs/rls

Drizzle ORM - Row-Level Security (RLS)

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

Deprecated DevOP•3h ago

When I try to query a table that has a SELECT RLS policy setup using Drizzle ORM like

const projects = await withRLS(db, userId, async (tx) => {
            return await tx.select().from(project);
        });

const projects = await withRLS(db, userId, async (tx) => {
            return await tx.select().from(project);
        });

It returns all the rows and not just the ones it should. BTW this is what my rls helper function looks like

export async function withRLS<T>(
    db: PostgresJsDatabase<typeof schema>,
    userId: string,
    callback: (tx: PostgresJsDatabase<typeof schema>) => Promise<T>,
): Promise<T> {
    return db.transaction(async (tx) => {
        // Set the user ID in the session context
        await tx.execute(
            sql`SELECT set_config('app.current_user_id', ${userId}, true)`,
        );

        try {
            // Execute the callback with the transaction
            return await callback(tx);
        } finally {
            // Clear the user ID from the session context
            await tx.execute(
                sql`SELECT set_config('app.current_user_id', NULL, true)`,
            );
        }
    });
}

export async function withRLS<T>(
    db: PostgresJsDatabase<typeof schema>,
    userId: string,
    callback: (tx: PostgresJsDatabase<typeof schema>) => Promise<T>,
): Promise<T> {
    return db.transaction(async (tx) => {
        // Set the user ID in the session context
        await tx.execute(
            sql`SELECT set_config('app.current_user_id', ${userId}, true)`,
        );

        try {
            // Execute the callback with the transaction
            return await callback(tx);
        } finally {
            // Clear the user ID from the session context
            await tx.execute(
                sql`SELECT set_config('app.current_user_id', NULL, true)`,
            );
        }
    });
}

garyaustin•3h ago

Did you use the Drizzle doc for roles I linked? You seem to be running as Postgres role which will not obey RLS. If you are not using the Supabase approach in the docs... https://orm.drizzle.team/docs/rls#using-with-supabase that sets your user role as part of the transaction, then you will need to have another role created to signin from drizzle. That role would need password and not have bypass RLS set. You seem to be setting your own user_id in app.current_user_id and I assume are checking that setting in RLS NOT using auth.uid() or auth.jwt(). If so then you do need a user role connecting to the database that does no bypass RLS.

Drizzle ORM - Row-Level Security (RLS)

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

Deprecated DevOP•3h ago

I am setting app.current_user_id = user_id because I am using Better-Auth (not Supabase Auth) for user authentication. I am getting user_id like this

const session = await auth.api.getSession({ headers: req.headers });
const userId = session?.user?.id;

const session = await auth.api.getSession({ headers: req.headers });
const userId = session?.user?.id;

The project table scheme with RLS policy looks like this.

export const project = pgTable(
    "project",
    {
        id: uuid("id").defaultRandom().primaryKey(),
        userId: text("user_id")
            .notNull()
            .references(() => user.id, { onDelete: "cascade" }),
        name: text("name").notNull(),
        description: text("description"),
        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at")
            .defaultNow()
            .$onUpdate(() => /* @__PURE__ */ new Date())
            .notNull(),
    },
    (_t) => [
        // ✅ Only authenticated users can read their own projects
        pgPolicy("select_own_projects", {
            for: "select",
            using: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
        }),

        // ✅ Only logged-in users can create a project and must own the row
        pgPolicy("insert_authenticated_owns_row", {
            for: "insert",
            withCheck: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
        }),

        // ✅ A user can update their own project, and must keep ownership
        pgPolicy("update_own_project", {
            for: "update",
            using: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
            withCheck: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
        }),

        // ✅ A user can delete their own project
        pgPolicy("delete_own_project", {
            for: "delete",
            using: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
        }),
    ],
);

export const project = pgTable(
    "project",
    {
        id: uuid("id").defaultRandom().primaryKey(),
        userId: text("user_id")
            .notNull()
            .references(() => user.id, { onDelete: "cascade" }),
        name: text("name").notNull(),
        description: text("description"),
        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at")
            .defaultNow()
            .$onUpdate(() => /* @__PURE__ */ new Date())
            .notNull(),
    },
    (_t) => [
        // ✅ Only authenticated users can read their own projects
        pgPolicy("select_own_projects", {
            for: "select",
            using: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
        }),

        // ✅ Only logged-in users can create a project and must own the row
        pgPolicy("insert_authenticated_owns_row", {
            for: "insert",
            withCheck: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
        }),

        // ✅ A user can update their own project, and must keep ownership
        pgPolicy("update_own_project", {
            for: "update",
            using: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
            withCheck: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
        }),

        // ✅ A user can delete their own project
        pgPolicy("delete_own_project", {
            for: "delete",
            using: sql`
        user_id = current_setting('app.current_user_id', true)::text
      `,
        }),
    ],
);

garyaustin•3h ago

The policies look good at quick glance for using that setting. You need to come up with a login user (other than postgres) with RLS bypass off. Not postgres role. OR in the guide I linked it shows how to change the role in the transaction from postgres role to another role like authenticated (stealing a SB one) or one you create. I don't use Drizzle though so really don't know the syntax to do what you need to do. But in your first post you said you are postgres user/role which won't work with RLS.

Gaming

Programming

RLS not working when connecting using Database connection string with Drizzle ORM

Did you find this page helpful?