[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 https://github.com/prisma/prisma/issues/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 https://github.com/porsager/postgres/issues/559#issuecomment-1556104073 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?

https://github.com/drizzle-team/drizzle-orm/pull/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.

[estill01]

Any update on merging https://github.com/drizzle-team/drizzle-orm/pull/1481, or is additional work needed?

[Angelelz]

I think that the PR is ready but I haven't used this feature myself so I was hoping to get some more feedback from the community. You can take a look at the tests I implemented and what the API looks like. I'm not sure if I'm missing anything.

[estill01]

@Angelelz I just took a fairly detailed read through the PR - I haven't tested it out yet (though may be able to do that now / shortly, as I'm building a new project); looks great, and thanks a lot for building this (super helpful!).

Two comments / questions:

(1) The tests don't use the PgTable.enableRLS function, but instead use raw SQL to activate RLS. This appears to side-step testing some of the core functionality, no?

See: https://github.com/drizzle-team/drizzle-orm/pull/1481/files#diff-8cc5eab297b00bd8fd753c57063cc3dd441af6c96b23dba32df67e0acfe3f961R45

	await db.execute(sql`ALTER TABLE ${users} ENABLE ROW LEVEL SECURITY`);

Am I misunderstanding the implementation or tests?

(2) drizzle-kit integration -- Will the RLS features be picked up in migration generation, or does this require additional efforts?

Thanks again for doing this; I'll report back if I find anything useful to contribute.

[Angelelz]

The implementation I added will only affect the drizzle-orm side. Drizzle-kit is still close source.

1. The .enableRLS() method is only useful for drizzle kit during migration. The tests were not testing that.
2. Additional effort will be needed.

Now, according to the public plan, we should have drizzle-kit open up fairly quickly. I could work on that side when that happens but It will probably take me some time as I'm not as familiar with the codebase as I am with drizzle-orm.

[Angelelz]

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.

In my opinion this should be a different issue/feature request. It doesn't seem so much as RLS support but more like ergonomics. I think as soon as RLS support lands, this issues should be created. And this can be useful to the other dialects as well.

[bompi88]

@Angelelz Any news related to the PR?

[rphlmr]

@Angelelz Any news related to the PR?

We are working on it ;)

[rphlmr]

Hello there 👋

You can test (some requirements) or look at the draft implementation here: https://github.com/rphlmr/drizzle-supabase-rls

A before / after here: https://github.com/rphlmr/drizzle-supabase-rls/blob/main/src/index.ts

[Angelelz]

Great work @rphlmr! I think with this type of real world test we can release this with more confidence. I'll check it out and mark the PR as ready. Though we'll be waiting for drizzle-kit support.

[fnimick]

FYI: @Angelelz you don't need to set any transaction variables besides request.jwt.claims. All the supabase functions default to looking at the value of request.jwt.claims if the value of request.jwt.claim.<option> is missing.

Example:

CREATE FUNCTION auth.email() RETURNS text
    LANGUAGE sql STABLE
    AS $$
  select 
  coalesce(
    nullif(current_setting('request.jwt.claim.email', true), ''),
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'email')
  )::text
$$;

The function definitions are here: https://github.com/supabase/gotrue/blob/42802886c5e11b82ae51fa4f1ec6994c3b29d521/migrations/20220224000811_update_auth_functions.up.sql#L31

(and in addition, all functions besides auth.jwt() are deprecated)

[fnimick]

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.

fyi, prisma's support for transaction handling in client extensions is dangerous. It is extremely easy to accidentally execute code outside of the transaction context which is silently ignored. See: https://github.com/prisma/prisma/issues/20678 (which I discovered while trying to implement a prisma extrension for supabase RLS, appropriately enough)

[fnimick]

@Angelelz (sorry it's taken so long!)

I generally agree with this approach, though I have one quibble. It would be great to have the option for the RLS to be applied at the client level, rather than the transaction level - i.e. a new client instance could be created for each request, and attached to the request object, such that all downstream request handlers could use it to make RLS queries without needing to specify the RLS options manually.

As currently implemented, it is very easy to accidentally bypass the RLS by forgetting to use a transaction, or forgetting to provide the transaction RLS config.

I am currently using a solution which creates a new drizzle instance for each request, using the same underlying database client. It looks vaguely like this:

const session = // get session from supabase
const accessToken = session.access_token;
const claims = JSON.parse(Buffer.from(accessToken.split(".")[1], "base64").toString())
request.rlsDrizzle = createRlsDrizzle(claims);

createRlsDrizzle creates a proxy object wrapper around the current Drizzle client which throws errors on the use of any non-transaction methods such as select, update, etc. and wraps the transaction method to apply the claims to the JWT before running any transaction code. This ensures that no downstream request handlers ever can use bypass RLS by mistake using the RLS client, since the claims are attached to the transaction handler at the request middleware level, before it ever gets to the handler.

[Angelelz]

I am currently using a solution which creates a new drizzle instance for each request, using the same underlying database client. It looks vaguely like this:

Very interesting. Aren't you creating a new pool on every request? Or is createRlsDrizzle creating a db.transaction and giving that back? I would love to see that code to understand your feature request better.

[fnimick]

@Angelelz I'm currently using a not-recommended setup (I am using a separate db login rather than using postgres and switching to the authenticated role) but will be resolving that soon. With that caveat, my code is:

const rlsPgClient = postgres(RLS_DB_URL.replace("pgbouncer=true", ""), { prepare: false });
const rlsDb = drizzle(rlsPgClient, { schema });
export function createRlsDrizzle(claims: string) {
  return new Proxy<typeof rlsDb>(rlsDb, {
    get(target, prop, receiver) {
      if (prop === "transaction") {
        return async (
          first: DrizzleTransactionFunctionFirst,
          ...rest: DrizzleTransactionFunctionRest
        ) => {
          return target.transaction(async (tx) => {
            await tx.execute(sql.raw(`SELECT set_config('request.jwt.claims', '${claims}', TRUE)`));
            return first(tx);
          }, ...rest);
        };
      }
      // throw errors if it's select, etc...
    },
  });
}

I am using the same underlying client instance, but wrapping it in a new proxy which modifies the behavior of the transaction method for each request. This should have no performance implications re: database resources.

Edit: the benefit here is that farther down the request handling chain, I can do: request.rlsDrizzle.transaction(async (tx) => .... - with no need to remember to set transaction variables or anything, RLS always applies. In addition, if I forget to use a transaction, request.rlsDrizzle.select(...) will always fail, so no RLS will be bypassed by accident by misusing the request.rlsDrizzle client.

[Angelelz]

This solution is genius, pretty similar to this one up here. To be honest, when I saw the tests that @rphlmr implemented here I got a little worried. I'd feel like not setting rls config should not bypass rls altogether.

What should we do to prevent that?

I'm starting to think that drizzle should provide another method in the db object like rlsTransaction that requires the rls config. I wish we could just provide that configuration during the drizzle creation but we get the auth related stuff from each request. This reminded me of #543 where folks would like to save the transaction state in a global object to use a transaction if one is being used.

EDIT: The issue is actually #543

@dankochetov, @AndriiSherman, @AlexBlokh I would like your thoughts on this.