[FEATURE]: Support PostgreSQL's Row Level Security (RLS)

[feliche93]

Describe want to want

Supabase is really nicely using Row Level Secruity for granular authorization rules.

🔗 Here's the link to their docs: https://supabase.com/docs/guides/auth/row-level-security

I'd love to switch from Supabase JS SDK to drizzle based on all the features, but one limitation right now is that it seems it does not support row level secruity.

Would love if you would consider adding this feature if also other users would find it helpful!

[RichiCoder1]

There doesn't seem to be a clean escape hatch in either schema or migrations to enable this too, making it even more important.

[akarabach]

Related issue in prisma repo prisma/prisma#12735, look like there is already a way how to do it, maybe there are some tips to go forward.

[RichiCoder1]

After doing some work w/ drizzle for a while, you can indeed emulate RLS (and by extension Supabase) via manually hand editing Migration files & using transactions to set contextual data. It's not great, but it does work.

[akarabach]

After doing some work w/ drizzle for a while, you can indeed emulate RLS (and by extension Supabase) via manually hand editing Migration files & using transactions to set contextual data. It's not great, but it does work.

do you have an example ?

[Stefandasbach]

+1 for this

[luisfontes]

This is how I'm currently doing it (I'm using Supabase):

rls.sql

DO $$
DECLARE t text;
BEGIN
    FOR t IN
        SELECT table_name
        FROM information_schema.columns
        WHERE table_schema = 'public'
    LOOP
        EXECUTE format('
            ALTER TABLE %I ENABLE ROW LEVEL SECURITY;
        ', t, t);
    END loop;
END;
$$ LANGUAGE plpgsql;

generate_migrations.sh

pnpm run db:generate-schema
file_name=$(pnpm run db:generate-custom | grep -oP '(?<=\b)\S+\.sql\b') && cat ./src/custom-sql/rls.sql >> "$file_name"

package.json

"scripts": {
    "db:generate-custom": "drizzle-kit generate:pg --custom",
    "db:generate-schema": "drizzle-kit generate:pg",
    "db:generate-all": "sh ./scripts/generate_migrations.sh",
    "db:generate-clean": "rm -rf ./migrations && pnpm run db:generate-all",
}

[jhonsfran1165]

For those who has this problem, for now I am using this function helper so I can emulate RLS for supabase.

import { type Session } from "@supabase/supabase-js"
import { sql } from "drizzle-orm"
import { PostgresJsDatabase, drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"

const queryConnection = postgres(process.env.DATABASE_URL!, {
  connection: {},
})

export const db = drizzle(queryConnection)

export function authDB<T>(
  session: Session,
  cb: (sql: PostgresJsDatabase) => T | Promise<T>
): Promise<T> {
  // You can add a validation here for the accessToken - we rely on supabase for now
  const jwtClaim = decodeJwt(session.access_token)
  const role = JSON.parse(jwtClaim).role

  return db.transaction(async (tx) => {
    // Set JWT to enable RLS. supabase adds the role and the userId (sub) to the jwt claims
    await tx.execute(
      sql`SELECT set_config('request.jwt.claims', '${sql.raw(jwtClaim)}', TRUE)`
    )

    // do not use postgres because it will bypass the RLS, set role to authenticated
    await tx.execute(sql`set role '${sql.raw(role)}'`)

    return cb(tx)
  }) as Promise<T>
}

Then you can use that helper for querying your db:

  const {
    data: { session },
  } = await supabaseClient.auth.getSession()

  if (session) {
    const data = await authDB(session, (tx) => {
      // This will be executed in an authenticated context.
      return tx.select().from(organization_profiles)
    })
  }

Please let me know if you catch any problem. This comment was very useful porsager/postgres#559 (comment) thanks!

[wojtekKrol]

I understand the desire for native RLS support in Drizzle for a more streamlined and unified approach. However, as an interim solution, couldn't we leverage the strengths of various tools to build a multi-layered security model?

In my own setup, I have several layers of security checks:

1. I manually enabled and configured Row-Level Security in the Supabase dashboard for granular data access.
2. I'm using tRPC for remote procedure calls.
3. Supabase functions are employed to check the roles of users, determining if they have the necessary privileges to invoke specific API endpoints.
4. Lastly, I utilize Next.js middleware to ensure that users have access to specific routes, adding another layer of security.

By doing so, we can manage Row-Level Security via Supabase, while Drizzle can focus on other CRUD operations. This multi-tiered security approach isn't a direct answer to the request for native RLS in Drizzle, but it serves as a practical, if not ideal, workaround for those who need advanced security features now and can't wait for Drizzle to support them natively.

[rphlmr]

Hey Supabase lovers, can you look at the draft PR from Angelelz?

#1481

[rphlmr]

For those who has this problem, for now I am using this function helper so I can emulate RLS for supabase.

import { type Session } from "@supabase/supabase-js"
import { sql } from "drizzle-orm"
import { PostgresJsDatabase, drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"

const queryConnection = postgres(process.env.DATABASE_URL!, {
  connection: {},
})

export const db = drizzle(queryConnection)

export function authDB<T>(
  session: Session,
  cb: (sql: PostgresJsDatabase) => T | Promise<T>
): Promise<T> {
  // You can add a validation here for the accessToken - we rely on supabase for now
  const jwtClaim = decodeJwt(session.access_token)
  const role = JSON.parse(jwtClaim).role

  return db.transaction(async (tx) => {
    // Set JWT to enable RLS. supabase adds the role and the userId (sub) to the jwt claims
    await tx.execute(
      sql`SELECT set_config('request.jwt.claims', '${sql.raw(jwtClaim)}', TRUE)`
    )

    // do not use postgres because it will bypass the RLS, set role to authenticated
    await tx.execute(sql`set role '${sql.raw(role)}'`)

    return cb(tx)
  }) as Promise<T>
}

Then you can use that helper for querying your db:

  const {
    data: { session },
  } = await supabaseClient.auth.getSession()

  if (session) {
    const data = await authDB(session, (tx) => {
      // This will be executed in an authenticated context.
      return tx.select().from(organization_profiles)
    })
  }

Please let me know if you catch any problem. This comment was very useful porsager/postgres#559 (comment) thanks!

@Angelelz do you think we can add tx.setConfig(xxx).setRole(xxx) (other PR) for PgTransaction or adding an optional options object?

// Get a session from your code
const session = await getSession();

const jwtClaim = decodeJwt(session.access_token);
const role = JSON.parse(jwtClaim).role;

const data = await db.transaction(
  async (tx) => {
    // Drizzle has applied the config for you
    // You now rely on RLS

    await tx.select();
    await tx.update();

    // ...
  },
  {
    configs: [
      {
        name: "request.jwt.claims",
        value: jwtClaim,
        isLocal: true,
      },
    ],
    role,
  }
);

It could let us writing custom helpers like:

async function authenticatedOnly(){
  const session = await getSession();

  const jwtClaim = decodeJwt(session.access_token)
  const role = JSON.parse(jwtClaim).role

  return   {
    configs: [
      {
        name: "request.jwt.claims",
        value: jwtClaim,
        isLocal: true,
      },
    ],
    roles,
  } satisfies PgTransactionOptions
}

const data = await db.transaction(
  async (tx) => {
   // Drizzle has applied the config for you
   // You now rely on RLS

    await tx.select(...);
    await tx.update(...);

  // ...
  },
  await authenticatedOnly()
);

Of course, we will have to run set_config and set role for the dev, before calling the tx callback.

[Angelelz]

@Angelelz do you think we can add tx.setConfig(xxx).setRole(xxx) (other PR) for PgTransaction or adding an optional options object?

Since a transaction already accepts a configuration object, I would think that's the best place to put it.

[rphlmr]

So with all you share:

Supabase RLS Policy functions and Postgres transaction configuration association

source

Supabase's RLS Policy functions	PgTransaction config to set	Description
auth.uid()	set_config('request.jwt.claim.sub', <current_user_uid>, true)	<current_user_uid> comes from your own way to get the current user uid
auth.email()	set_config('request.jwt.claim.email', <current_user_email>, true)	<current_user_email> The current user email
auth.role()	set_config('request.jwt.claim.role', <current_user_role>, true)	<current_user_role> The current user role
auth.jwt()	set_config('request.jwt.claim', <current_user_jwt>, true)	<current_user_jwt> The current user jwt token. 🚨 I'm note sure about the config name, found nothing in Supabase repo

These user datas can come from Supabase auth or a user table.
What matters is that it matches when you use these functions in your RLS Policy

Use Supabase RLS with Drizzle Transaction

You have to use a transaction to isolate the user queries

Supabase RLS doc

const data = await db.transaction(
  async (tx) => {
    // You use `auth.jwt()` in your RLS policy
    await tx.execute(sql`SELECT set_config('request.jwt.claims', '${sql.raw(jwtClaim)}', TRUE)`);
    
    // You use `auth.uid()` in your RLS policy
    await tx.execute(sql`SELECT set_config('request.jwt.claim.sub', '${sql.raw(userUid)}', TRUE)`);
    
    // You use `auth.email()` in your RLS policy
    await tx.execute(sql`SELECT set_config('request.jwt.claim.email', '${sql.raw(userEmail)}', TRUE)`);
    
    // You use `auth.role()` in your RLS policy
    await tx.execute(sql`SELECT set_config('request.jwt.claim.role', '${sql.raw(userRole)}', TRUE)`)

    // do not use the default role (Drizzle uses your root user with `postgres` role) because it will bypass the RLS policy, set role to authenticated
    await tx.execute(sql`set local role authenticated`);

    // All the following will be run with the user context set with `set_config`
    await tx.select(...);
    await tx.update(...);

  // ...
  }
);

Maybe what Drizzle can do

const data = await db.transaction(
  async (tx) => {
    // Drizzle has applied the configuration for you
    
    // All the following will be run with the user context set by Drizzle with `set_config`
    await tx.select(...);
    await tx.update(...);

  // ...
  },
  {
    configs: [
      // You use `auth.jwt()` in your RLS policy
      {
        name: "request.jwt.claims",
        value: jwtClaim,
        isLocal: true,
      },
      // You use `auth.uid()` in your RLS policy
      {
        name: "request.jwt.claims.sub",
        value: userUid,
        isLocal: true,
      },
      // You use `auth.email()` in your RLS policy
      {
        name: "request.jwt.claims.email",
        value: userEmail,
        isLocal: true,
      },
      // You use `auth.role()` in your RLS policy
      {
        name: "request.jwt.claims.role",
        value: userRole,
        isLocal: true,
      },
    ],
    role: 'authenticated',
  }
);

Open for custom configuration helpers

async function authenticated(){
  // Your own way to get the current user session, depending on what framework you use
  const session = await getSession();

  const jwtClaim = decodeJwt(session.access_token);
  const role = session.user.role;
  const userUid = session.user.sub;
  const userEmail = session.user.email;
  const userRole = session.user.role;

  return   {
    configs: [
      // You use `auth.jwt()` in your RLS policy
      {
        name: "request.jwt.claims",
        value: jwtClaim,
        isLocal: true,
      },
      // You use `auth.uid()` in your RLS policy
      {
        name: "request.jwt.claims.sub",
        value: userUid,
        isLocal: true,
      },
      // You use `auth.email()` in your RLS policy
      {
        name: "request.jwt.claims.email",
        value: userEmail,
        isLocal: true,
      },
      // You use `auth.role()` in your RLS policy
      {
        name: "request.jwt.claims.role",
        value: userRole,
        isLocal: true,
      },
    ],
    role,
  } satisfies PgTransactionOptions
}

const data = await db.transaction(
  async (tx) => {
    // Drizzle has applied the configuration for you
    
    // All the following will be run with the user context set by Drizzle with `set_config`
    await tx.select(...);
    await tx.update(...);

  // ...
  },
  await authenticated()
);

[Angelelz]

@rphlmr Thank you for your research and compiling this for us!
I have a couple follow up questions mostly about the best way to implement this in Drizzle:

1. I see that you are passing an array to the config. Will Drizzle need to execute each of those in separate queries for the auth flow to work?
2. Do we need to use sql.raw for this parameter? Does this query require the parameter to be inline or can we pass it as a regular query param to possibly avoid injection attacks? (We don't know where the user is getting this data from)

await tx.execute(sql`SELECT set_config('request.jwt.claims', '${sql.raw(jwtClaim)}', TRUE)`);

3. I believe the config parameter needs to be different to avoid breaking changes. ie:

type PgTransactionOptions = {
	isolationLevel?: 'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable';
	accessMode?: 'read only' | 'read write';
	deferrable?: boolean;
        rls?: // All RLS config should go here
}

4. Do you thing for the third parameter we could accept () => Promise<PgTransactionOptions> | () => PgTransactionOptions | PgTransactionOptions so that the user can do:

const data = await db.transaction(
  async (tx) => {
    // Drizzle has applied the configuration for you
    
    // All the following will be run with the user context set by Drizzle with `set_config`
    await tx.select(...);
    await tx.update(...);

  // ...
  },
  authenticated
);

Any supabase/RLS user: any feedback will be appreciated.

[rphlmr]

@rphlmr Thank you for your research and compiling this for us! I have a couple follow up questions mostly about the best way to implement this in Drizzle:

1. I see that you are passing an array to the config. Will Drizzle need to execute each of those in separate queries for the auth flow to work?

I think Drizzle could iterate over the configs array and apply a set_config for each item.

2. Do we need to use sql.raw for this parameter? Does this query require the parameter to be inline or can we pass it as a regular query param to possibly avoid injection attacks? (We don't know where the user is getting this data from)

await tx.execute(sql`SELECT set_config('request.jwt.claims', '${sql.raw(jwtClaim)}', TRUE)`);

Anything that can work. set_config requires 3 params separated by a ,.
I don't think we can have a sql injection in the set_config. But we can choose the most secure way.

Can't say which is better.

const stringToken = JSON.stringify({ id: "1" });
const pgDialect = new PgDialect();

const query1 = sql`SELECT set_config('request.jwt.claims', '${sql.raw(
stringToken,
)}', TRUE)`;
const query2 = sql`SELECT set_config('request.jwt.claims', '${stringToken}', TRUE)`;

console.log(pgDialect.sqlToQuery(query1));
console.log(pgDialect.sqlToQuery(query2));

Results:

// Query 1
{
  sql: `SELECT set_config('request.jwt.claims', '{"id":"1"}', TRUE)`,
  params: []
}
// Query 2
{
  sql: "SELECT set_config('request.jwt.claims', '$1', TRUE)",
  params: [ '{"id":"1"}' ]
}

3. I believe the config parameter needs to be different to avoid breaking changes. ie:

type PgTransactionOptions = {
	isolationLevel?: 'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable';
	accessMode?: 'read only' | 'read write';
	deferrable?: boolean;
        rls?: // All RLS config should go here
}

Extending PgTransactionOptions:

 type PgTransactionOptions = {
	isolationLevel?: 'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable';
 	accessMode?: 'read only' | 'read write';
 	deferrable?: boolean;
        configs?: { name: string, value:string, isLocal: boolean }[];
        role?: string;
 }

Configs and role are not only related to RLS or Supabase.

2 possibilities here:

• Extending PgTransactionOptions and applying configs and role for the dev before running its tx callback
• Adding setConfig and setRole to PgTransaction so we can to tx.setConfig('request.jwt.claims', stringToken, true) ourself

4. Do you thing for the third parameter we could accept () => Promise<PgTransactionOptions> | () => PgTransactionOptions | PgTransactionOptions so that the user can do:

const data = await db.transaction(
  async (tx) => {
    // Drizzle has applied the configuration for you
    
    // All the following will be run with the user context set by Drizzle with `set_config`
    await tx.select(...);
    await tx.update(...);

  // ...
  },
  authenticated
);

Any supabase/RLS user: any feedback will be appreciated.

That could be cool!

[stabildev]

I would love the option to have a db query client that automatically applies a transaction for each query, similar to Prisma's client extensions.

const queryClient = postgres(process.env.DATABASE_URL)

export const db = drizzle(queryClient, {
  schema,
  forceTransactions: true, // always use transactions for this db
  beginTransaction: (tx) => tx.execute(sql`SET LOCAL app.user_id = "${sql.raw(parsedUserId)}"` // function to be called at the beginning of the transaction (optional)
  endTransaction: undefined, // function to be called at the end of the transaction (optional)
})

This would be a light convenient helper that conforms to standard postgres instead of using 3rd party (supabase) apis.

[jamesarosen]

This is my (current) favorite approach, for a few reasons:

1. it's easy to set the settings for RLS centrally in an app; centrally is key for application security when working on teams where not everyone is a security expert
2. it's easy to create a library that does Supabase/PostgREST-compatible config for Drizzle
3. it can be used for other Postgres things that we haven't imagined -- and even for things in other databases

forceTransactions can default to true if beginTransaction or endTransaction is specified. That saves some typing.

[jamesarosen]

There's also the option of using the acquire and release events from the Pg node driver, as inspired by this comment on the similar issue for typeorm.

[rphlmr]

I'm using supabase with tRPC and I was able to leverage their middleware implementation to make a wrapper around their procedure call to mutate the default db object and use the tx instead, the problem here is that I don't know how risky it is and if rollbacks actually works by default in case of errors inside the procedure calls, here's my current implementation:

const getServerAuthSession = () => {

  const token = cookies().get(cookieName)?.value;



  if (token) {

    try {

      return jwt.verify(token, env.SUPABASE_JWT_SECRET) as User;

    } catch (error) {

      return null;

    }

  }



  return null;

};



export const createTRPCContext = async (opts: { headers: Headers }) => {

  const session = getServerAuthSession();



  return {

    db: serviceDb,

    supabase: {

      baseUrl: env.SUPABASE_URL,

    },

    session,

    ...opts,

  };

};



// ..

// ..

// ..



/**

 * Admin (postgres) procedure

 */

export const adminProcedure = t.procedure;



/**

 * Public (anon | authenticated) procedure

 */

export const publicProcedure = t.procedure.use(({ ctx, next }) => {

  return serviceDb.transaction(async (tx) => {

    if (ctx.session) {

      await tx.execute(

        sql.raw(

          `SELECT set_config('request.jwt.claims', '${JSON.stringify(ctx.session)}', TRUE)`,

        ),

      );

      await tx.execute(sql.raw(`SET ROLE 'authenticated'`));

    } else {

      await tx.execute(sql.raw(`SET ROLE 'anon'`));

    }

    const res = await next({

      ctx: {

        ...ctx,

        db: tx,

      },

    });



    if (ctx.session) {

      await tx.execute(

        sql`SELECT set_config('request.jwt.claims', NULL, TRUE)`,

      );

    }

    await tx.execute(sql`RESET ROLE`);



    return res;

  });

});



/**

 * Protected (authenticated) procedure

 */

export const protectedProcedure = t.procedure.use(({ ctx, next }) => {

  return serviceDb.transaction(async (tx) => {

    if (!ctx.session) {

      throw new TRPCError({ code: "UNAUTHORIZED" });

    }



    await tx.execute(

      sql.raw(

        `SELECT set_config('request.jwt.claims', '${JSON.stringify(ctx.session)}', TRUE)`,

      ),

    );

    await tx.execute(sql.raw(`SET ROLE 'authenticated'`));



    const res = await next({

      ctx: {

        ...ctx,

        db: tx,

        session: { ...ctx.session },

      },

    });



    await tx.execute(sql`SELECT set_config('request.jwt.claims', NULL, TRUE)`);

    await tx.execute(sql`RESET ROLE`);



    return res;

  });

});

some context:

• serviceDb is the default drizzle client with the default postgres role

• cookies() is the new nextjs server side function, strangely enough it works in middlewares: https://nextjs.org/docs/app/api-reference/functions/cookies

• jwt.verify() is where I verify if the provided jwt is generated by supabase: https://www.npmjs.com/package/jsonwebtoken#jwtverifytoken-secretorpublickey-options-callback

It looks good to me.

Rollback should work, but if you want to be sure you can test by throwing after the last execute and you should see that nothing happens.

[craxrev]

@rphlmr that's nice to know, so far this seems the most logical rls implementation with tRPC, so far the tx instance has all serviceDb methods, so it can be used as a "drop-in" replacement for the real drizzle client, this if I'm not wrong ofc, still don't know if there's any hidden tradeoffs..

[itsmikesharescode]

Any update???? 🥲

[itsmikesharescode]

Up Up Up any update :/

[RyanClementsHax]

What's working really well for me is the following.

import postgres from "postgres";
import { sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/postgres-js";
import * as schema from "./drizzle/schema.ts";
import { JwtPayload } from "jwt-decode";

const connectionString = Deno.env.get("DB_URL")!;

export const getDb = (decodedJwt: JwtPayload) => {
    // Disable prefetch as it is not supported for "Transaction" pool mode
    const client = postgres(connectionString, { prepare: false });
    const db = drizzle(client, { schema });
    return new Proxy<typeof db>(db, {
        get(target, prop) {
            if (prop === "transaction") {
                return (async (transaction, ...rest) => {
                    return await target.transaction(async (tx) => {
                        // Emulates RLS
                        // https://github.com/drizzle-team/drizzle-orm/issues/594
                        await tx.execute(sql`
                            select set_config('request.jwt.claims', '${sql.raw(
                                JSON.stringify(decodedJwt)
                            )}', TRUE);
                            select set_config('request.jwt.claim.sub', '${sql.raw(
                                decodedJwt.sub ?? ""
                            )}', TRUE);
                            select set_config('request.jwt.claim.email', '${sql.raw(
                                decodedJwt.email
                            )}', TRUE);
                            select set_config('request.jwt.claim.role', '${sql.raw(
                                decodedJwt.role
                            )}', TRUE);
                            set local role ${sql.raw(decodedJwt.role)};
                        `);
                        return await transaction(tx);
                    }, ...rest);
                }) as typeof db.transaction;
            }
        },
    });
};

I took inspiration from this thread and modified it to fit my needs. I batched all of the set_config calls. I also add another layer of security by connecting to supabase with the authenticator role instead of the postgres role. This is the same role that PostgREST uses and it forces you to respect the RLS policies since authenticator cannot bypass them.

Example .env file for local development

# Intentionally the same role as used in PostgREST
# This enforces RLS to be respected
DB_URL="postgresql://authenticator:postgres@db:5432/postgres"

[njoshi22]

What's working really well for me is the following.

import postgres from "postgres";
import { sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/postgres-js";
import * as schema from "./drizzle/schema.ts";
import { JwtPayload } from "jwt-decode";

const connectionString = Deno.env.get("DB_URL")!;

export const getDb = (decodedJwt: JwtPayload) => {
    // Disable prefetch as it is not supported for "Transaction" pool mode
    const client = postgres(connectionString, { prepare: false });
    const db = drizzle(client, { schema });
    return new Proxy<typeof db>(db, {
        get(target, prop) {
            if (prop === "transaction") {
                return (async (transaction, ...rest) => {
                    return await target.transaction(async (tx) => {
                        // Emulates RLS
                        // https://github.com/drizzle-team/drizzle-orm/issues/594
                        await tx.execute(sql`
                            select set_config('request.jwt.claims', '${sql.raw(
                                JSON.stringify(decodedJwt)
                            )}', TRUE);
                            select set_config('request.jwt.claim.sub', '${sql.raw(
                                decodedJwt.sub ?? ""
                            )}', TRUE);
                            select set_config('request.jwt.claim.email', '${sql.raw(
                                decodedJwt.email
                            )}', TRUE);
                            select set_config('request.jwt.claim.role', '${sql.raw(
                                decodedJwt.role
                            )}', TRUE);
                            set local role ${sql.raw(decodedJwt.role)};
                        `);
                        return await transaction(tx);
                    }, ...rest);
                }) as typeof db.transaction;
            }
        },
    });
};

I took inspiration from this thread and modified it to fit my needs. I batched all of the set_config calls. I also add another layer of security by connecting to supabase with the authenticator role instead of the postgres role. This is the same role that PostgREST uses and it forces you to respect the RLS policies since authenticator cannot bypass them.

Example .env file for local development

# Intentionally the same role as used in PostgREST
# This enforces RLS to be respected
DB_URL="postgresql://authenticator:postgres@db:5432/postgres"

This is the right way to do it - except the login role should inherit from authenticated vs authenticator, and you should set up a special role for server logins.

[stabildev]

The Proxy API is great, thanks!

[tazmaniax]

What's working really well for me is the following.

import postgres from "postgres";
import { sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/postgres-js";
import * as schema from "./drizzle/schema.ts";
import { JwtPayload } from "jwt-decode";

const connectionString = Deno.env.get("DB_URL")!;

export const getDb = (decodedJwt: JwtPayload) => {
    // Disable prefetch as it is not supported for "Transaction" pool mode
    const client = postgres(connectionString, { prepare: false });
    const db = drizzle(client, { schema });
    return new Proxy<typeof db>(db, {
        get(target, prop) {
            if (prop === "transaction") {
                return (async (transaction, ...rest) => {
                    return await target.transaction(async (tx) => {
                        // Emulates RLS
                        // https://github.com/drizzle-team/drizzle-orm/issues/594
                        await tx.execute(sql`
                            select set_config('request.jwt.claims', '${sql.raw(
                                JSON.stringify(decodedJwt)
                            )}', TRUE);
                            select set_config('request.jwt.claim.sub', '${sql.raw(
                                decodedJwt.sub ?? ""
                            )}', TRUE);
                            select set_config('request.jwt.claim.email', '${sql.raw(
                                decodedJwt.email
                            )}', TRUE);
                            select set_config('request.jwt.claim.role', '${sql.raw(
                                decodedJwt.role
                            )}', TRUE);
                            set local role ${sql.raw(decodedJwt.role)};
                        `);
                        return await transaction(tx);
                    }, ...rest);
                }) as typeof db.transaction;
            }
        },
    });
};

I took inspiration from this thread and modified it to fit my needs. I batched all of the set_config calls. I also add another layer of security by connecting to supabase with the authenticator role instead of the postgres role. This is the same role that PostgREST uses and it forces you to respect the RLS policies since authenticator cannot bypass them.

Example .env file for local development

# Intentionally the same role as used in PostgREST
# This enforces RLS to be respected
DB_URL="postgresql://authenticator:postgres@db:5432/postgres"

Not sure what I'm doing wrong but when I use the Proxy as is, I get the following errors:

const db = getDb(decodedJwt)
const allUsers = await db.query.users.findMany();  // TypeError - Cannot read properties of undefined (reading 'users')
await this.db.insert(schema.users); // TypeError - db.insert is not a function

[tazmaniax]

I fixed my TypeError issues by returning the requested property value if the property is not a transaction. Appears to be a nice workaround until #1481 is released. @RyanClementsHax thx

get(target, prop) {
    if (prop === "transaction") {
        return (async (transaction, ...rest) => {
            return await target.transaction(async (tx) => {
                // Emulates RLS
                // https://github.com/drizzle-team/drizzle-orm/issues/594
                await tx.execute(sql`
                    select set_config('request.jwt.claims', '${sql.raw(JSON.stringify(decodedJwt))}', TRUE);
                    select set_config('request.jwt.claim.sub', '${sql.raw(decodedJwt.sub ?? "")}', TRUE);
                    select set_config('request.jwt.claim.email', '${sql.raw(decodedJwt.email as string)}', TRUE);
                    select set_config('request.jwt.claim.role', '${sql.raw(decodedJwt.role as string)}', TRUE);
                    set local role ${sql.raw(decodedJwt.role as string)};
                `);
                return await transaction(tx);
            }, ...rest);
        }) as typeof db.transaction;
    } else {
        return Reflect.get(target, prop)
    }
}

[xerosanyam]

@RyanClementsHax hasn't this introduced latency for you?
I am seeing the same query take 1.2s now which took 200ms before.

[fnimick]

This likely isn't drizzle latency but due to poor performance from RLS policies.

…

On Tue, May 14, 2024, 13:56 Sanyam Jain ***@***.***> wrote: @RyanClementsHax <https://github.com/RyanClementsHax> hasn't this introduced latency for you? I am seeing the same query take 1.2s now which took 200ms before. — Reply to this email directly, view it on GitHub <#594 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAFL6NHFPXVVGR25GAFHM7DZCJF3HAVCNFSM6AAAAAAYGNJ5CWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJQHAYDMNRRHE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[RyanClementsHax]

@RyanClementsHax hasn't this introduced latency for you? I am seeing the same query take 1.2s now which took 200ms before.

I haven't noticed it, but I have yet to deploy it in prod.

[itsmikesharescode]

i want this bad.

[tom-sherman]

Dropping in to say that implementing this in userland is fine by me, I'm quite happy with the solution using transactions and haven't had any blockers with this yet.

I'm not entirely sure if/how drizzle should add support in the library.

[tom-sherman]

@stabildev yes, of course, we have a separate login that has no grants other than to assume the authenticated or anon roles. That way there is zero access if the correct RLS transaction setup is not used with this connection.

Can you share how exactly this is done? I can't find any resources online.

[stabildev]

CREATE USER appuser WITH ENCRYPTED PASSWORD '<yourpassword>';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

DB_ADMIN_URL=postgres://defaultuser:password@host:port/dbname
DB_URL=postgres://appuser:yourpassword@host:port/dbname

use defaultuser in drizzle.config.ts for migrations with drizzle-kit
use appuser as the connection string for your db client

Do not add DB_ADMIN_URL to .env.production

That's how I do it at least

[ColeBlender]

This only affects your website that is using Drizzle correct? Even though RLS doesn't work with Drizzle it is still working to protect the database from other people correct?

[fanckush]

Correct

[davidgomes]

[davidgomes]

Here's what I think pgPolicy and PGPolicy will end up looking like:

// All fields are optional since PG supports doing just
// neondb_owner@david=> create policy user_policy on users;
// CREATE POLICY
type PGPolicy = {
  as?: 'permissive' | 'restrictive';
  for?: 'all' | 'select' | 'insert' | 'update' | 'delete';
  to?: string;
  using?: SQL;
  check?: SQL;
};

declare function pgPolicy(
  // drizzle-kit and the drizzle eslint plugin will check if there is more than one
  // policy for the same table with the same name (Postgres forbids this)
  name: string;
  options?: {
    as?: PGPolicy["as"]; // default is permissive
    for?: PGPolicy["for"]; // default is 'all'
    to?: SQL | PGRole; // default is 'PUBLIC'
    using?: SQL;
    check?: SQL;
  }
);

[danhstevens]

Easiest way I've found is to just add some raw sql in migrate.ts to find all non-RLS tables and enable RLS after each time a migration is run. Here's an example migrate.ts:

import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { sql } from 'drizzle-orm'
import { db, client } from './db';

// This will run migrations on the database, skipping the ones already applied
await migrate(db, { migrationsFolder: './db/migrations' });

// Run SQL to enable RLS on any newly created tables
const enableRls = sql`
DO $$
DECLARE
    rec RECORD;
BEGIN
    -- Loop through each table without RLS enabled
    FOR rec IN
        SELECT schemaname, tablename
        FROM pg_tables
        WHERE schemaname IN ('public')
        AND tablename NOT IN (
            SELECT c.relname
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relrowsecurity = true
        )
    LOOP
        -- Enable RLS on the table
        EXECUTE 'ALTER TABLE ' || quote_ident(rec.schemaname) || '.' || quote_ident(rec.tablename) || ' ENABLE ROW LEVEL SECURITY';
    END LOOP;
END $$;
`;
await db.execute(enableRls);

// Don't forget to close the connection, otherwise the script will hang
await client.end();

[AndriiSherman]

Update: finishing pull and push logic for policies and roles and preparing for beta release

[serhii-kucherenko]

any updates?

[RobSchilderr]

If people can't wait, this solution also works: https://gist.github.com/ikupenov/10bc89d92d92eaba8cc5569013e04069

[nicholasdly]

Any updates? 👀

[AntonioAngelino]

@nicholasdly it's here since 2 hours! 😃

https://github.com/drizzle-team/drizzle-orm/releases/tag/0.36.0

[nicholasdly]

Oops, I completely missed it—awesome, thank you!