Open in app

Sign in

Write

Sign in

Using Prisma with Supabase Row Level Security and Multi-Schema

Ngoc Tran
20 min readApr 30, 2023

Deprecated and Outdated: I advise you to not do this type of change and instead opt for Supabase Migrations. This adds complexity and when Supabase makes changes to their base DB schemas, it will cause issues with your Prisma Migrations. Read it for enjoyment, but don’t follow what’s given here!

Supabase is a great Firebase replacement that uses a PostgreSQL backend. Prisma is the next generation ORM built for Node.JS and Typescript. I personally love using both as they have significantly sped up my development. However, making sure they are both compatible and work with each other was not easy.

Why do you want to use Prisma with Supabase?

  1. Migrations help to keep track of database changes
  2. Security and Row Policies / Functions can be tracked and be redeployed to different test and production servers.
  3. Have generated types for database access and a modern next-generation ORM

In this example, I’ll use the following technologies to demonstrate the setup:

  1. Supabase Backend as a service
  2. Nest.JS as our backend
  3. Prisma (ORM)

Starting our Supabase Nest.JS Project

Let’s get started by creating a new Nest.JS project. Let’s do this with Nest-CLI. You can use any name you choose, but for this example, I will choose “prisma-with-supabase”.

If you don’t have a NestJS installed. Use any package manager to install it. I’m using pnpm

npm install -g @nestjs/cli

Let’s create the project

nest new prisma-with-supabase

Now let’s install Prisma to the project

pnpm install prisma --save-dev

Now let’s initialize the Prisma in our project

npx prisma init

This command will create an .envfile and also the schemafolder with a schema.prisma file. Let’s update our .env file

#.env
# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="postgres://postgres::<your_password>@db.<supabase_url>.supabase.co:6543/postgres?pgbouncer=true"
# PostgreSQL connection string used for migrations
DIRECT_URL="postgres://postgres:<your_password>@db.<supabase_url>.supabase.co:5432/postgres"

If you are curious where I got this, it is embedded in your Supabase settings.

Both of these URLS will be used for accessing your database and using migrations. If you want to read more about the setup and why both is needed, please refer to Supabase’s integration guides.

Now, let’s update our Prisma file to include the following

// schema.prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider        = "prisma-client-js"
  // We need multischema and we will need client extensions later!
  previewFeatures = ["multiSchema", "clientExtensions"] 
  binaryTargets   = ["native"]
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
  schemas   = ["auth", "public"]
}

We need multischema because we want to be aware of our auth schema as well as the public schema (where our own created databases will live). This is required because we will need it to be aware of the auth schema in order for us to create foreign key links (one-to-one, one-to-many, and many-to-many relations with the user_id from the auth schema). This is required to enable us to write our own row-level-security policies for our tables.

Baselining with Prisma

Next, let’s work on baselining our database. To understand why baselining is important or why it is necessary, please read more about it on Prisma’s amazing guide on why you might want to try baselining your database. For us, the reason is quite obvious: our Database was initially created by Supabase and there are a lot of permissions we might not have and that is handled by Supabase that we don’t want to mess with (especially the auth and such).

Run the following command to pull our Database for the `auth` and the `public` schema. This shouldn’t pull any public changes, since you should have none

npx prisma db pull

Your updated prisma.schema file should look like the following below.

// schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema", "clientExtensions"]
  binaryTargets   = ["native"]
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
  schemas   = ["auth", "public"]
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model audit_log_entries {
  instance_id String?   @db.Uuid
  id          String    @id @db.Uuid
  payload     Json?     @db.Json
  created_at  DateTime? @db.Timestamptz(6)
  ip_address  String    @default("") @db.VarChar(64)

  @@index([instance_id], map: "audit_logs_instance_id_idx")
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model flow_state {
  id                     String                @id @db.Uuid
  user_id                String?               @db.Uuid
  auth_code              String
  code_challenge_method  code_challenge_method
  code_challenge         String
  provider_type          String
  provider_access_token  String?
  provider_refresh_token String?
  created_at             DateTime?             @db.Timestamptz(6)
  updated_at             DateTime?             @db.Timestamptz(6)
  authentication_method  String

  @@index([auth_code], map: "idx_auth_code")
  @@index([user_id, authentication_method], map: "idx_user_id_auth_method")
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model identities {
  id              String
  user_id         String    @db.Uuid
  identity_data   Json
  provider        String
  last_sign_in_at DateTime? @db.Timestamptz(6)
  created_at      DateTime? @db.Timestamptz(6)
  updated_at      DateTime? @db.Timestamptz(6)
  email           String?   @default(dbgenerated("lower((identity_data ->> 'email'::text))"))
  users           users     @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@id([provider, id])
  @@index([email])
  @@index([user_id])
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model instances {
  id              String    @id @db.Uuid
  uuid            String?   @db.Uuid
  raw_base_config String?
  created_at      DateTime? @db.Timestamptz(6)
  updated_at      DateTime? @db.Timestamptz(6)

  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model mfa_amr_claims {
  session_id            String   @db.Uuid
  created_at            DateTime @db.Timestamptz(6)
  updated_at            DateTime @db.Timestamptz(6)
  authentication_method String
  id                    String   @id(map: "amr_id_pk") @db.Uuid
  sessions              sessions @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@unique([session_id, authentication_method], map: "mfa_amr_claims_session_id_authentication_method_pkey")
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model mfa_challenges {
  id          String      @id @db.Uuid
  factor_id   String      @db.Uuid
  created_at  DateTime    @db.Timestamptz(6)
  verified_at DateTime?   @db.Timestamptz(6)
  ip_address  String      @db.Inet
  mfa_factors mfa_factors @relation(fields: [factor_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "mfa_challenges_auth_factor_id_fkey")

  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model mfa_factors {
  id             String           @id @db.Uuid
  user_id        String           @db.Uuid
  friendly_name  String?
  factor_type    factor_type
  status         factor_status
  created_at     DateTime         @db.Timestamptz(6)
  updated_at     DateTime         @db.Timestamptz(6)
  secret         String?
  mfa_challenges mfa_challenges[]
  users          users            @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([user_id, created_at], map: "factor_id_created_at_idx")
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model refresh_tokens {
  instance_id String?   @db.Uuid
  id          BigInt    @id @default(autoincrement())
  token       String?   @unique(map: "refresh_tokens_token_unique") @db.VarChar(255)
  user_id     String?   @db.VarChar(255)
  revoked     Boolean?
  created_at  DateTime? @db.Timestamptz(6)
  updated_at  DateTime? @db.Timestamptz(6)
  parent      String?   @db.VarChar(255)
  session_id  String?   @db.Uuid
  sessions    sessions? @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([instance_id])
  @@index([instance_id, user_id])
  @@index([parent])
  @@index([session_id, revoked])
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model saml_providers {
  id                String        @id @db.Uuid
  sso_provider_id   String        @db.Uuid
  entity_id         String        @unique
  metadata_xml      String
  metadata_url      String?
  attribute_mapping Json?
  created_at        DateTime?     @db.Timestamptz(6)
  updated_at        DateTime?     @db.Timestamptz(6)
  sso_providers     sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([sso_provider_id])
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model saml_relay_states {
  id              String        @id @db.Uuid
  sso_provider_id String        @db.Uuid
  request_id      String
  for_email       String?
  redirect_to     String?
  from_ip_address String?       @db.Inet
  created_at      DateTime?     @db.Timestamptz(6)
  updated_at      DateTime?     @db.Timestamptz(6)
  sso_providers   sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([for_email])
  @@index([sso_provider_id])
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model schema_migrations {
  version String @id @db.VarChar(255)

  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model sessions {
  id             String           @id @db.Uuid
  user_id        String           @db.Uuid
  created_at     DateTime?        @db.Timestamptz(6)
  updated_at     DateTime?        @db.Timestamptz(6)
  factor_id      String?          @db.Uuid
  aal            aal_level?
  not_after      DateTime?        @db.Timestamptz(6)
  mfa_amr_claims mfa_amr_claims[]
  refresh_tokens refresh_tokens[]
  users          users            @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([user_id])
  @@index([user_id, created_at], map: "user_id_created_at_idx")
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model sso_domains {
  id              String        @id @db.Uuid
  sso_provider_id String        @db.Uuid
  domain          String
  created_at      DateTime?     @db.Timestamptz(6)
  updated_at      DateTime?     @db.Timestamptz(6)
  sso_providers   sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([sso_provider_id])
  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model sso_providers {
  id                String              @id @db.Uuid
  resource_id       String?
  created_at        DateTime?           @db.Timestamptz(6)
  updated_at        DateTime?           @db.Timestamptz(6)
  saml_providers    saml_providers[]
  saml_relay_states saml_relay_states[]
  sso_domains       sso_domains[]

  @@schema("auth")
}

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model users {
  instance_id                 String?       @db.Uuid
  id                          String        @id @db.Uuid
  aud                         String?       @db.VarChar(255)
  role                        String?       @db.VarChar(255)
  email                       String?       @db.VarChar(255)
  encrypted_password          String?       @db.VarChar(255)
  email_confirmed_at          DateTime?     @db.Timestamptz(6)
  invited_at                  DateTime?     @db.Timestamptz(6)
  confirmation_token          String?       @db.VarChar(255)
  confirmation_sent_at        DateTime?     @db.Timestamptz(6)
  recovery_token              String?       @db.VarChar(255)
  recovery_sent_at            DateTime?     @db.Timestamptz(6)
  email_change_token_new      String?       @db.VarChar(255)
  email_change                String?       @db.VarChar(255)
  email_change_sent_at        DateTime?     @db.Timestamptz(6)
  last_sign_in_at             DateTime?     @db.Timestamptz(6)
  raw_app_meta_data           Json?
  raw_user_meta_data          Json?
  is_super_admin              Boolean?
  created_at                  DateTime?     @db.Timestamptz(6)
  updated_at                  DateTime?     @db.Timestamptz(6)
  phone                       String?       @unique
  phone_confirmed_at          DateTime?     @db.Timestamptz(6)
  phone_change                String?       @default("")
  phone_change_token          String?       @default("") @db.VarChar(255)
  phone_change_sent_at        DateTime?     @db.Timestamptz(6)
  confirmed_at                DateTime?     @default(dbgenerated("LEAST(email_confirmed_at, phone_confirmed_at)")) @db.Timestamptz(6)
  email_change_token_current  String?       @default("") @db.VarChar(255)
  email_change_confirm_status Int?          @default(0) @db.SmallInt
  banned_until                DateTime?     @db.Timestamptz(6)
  reauthentication_token      String?       @default("") @db.VarChar(255)
  reauthentication_sent_at    DateTime?     @db.Timestamptz(6)
  is_sso_user                 Boolean       @default(false)
  deleted_at                  DateTime?     @db.Timestamptz(6)
  identities                  identities[]
  mfa_factors                 mfa_factors[]
  sessions                    sessions[]

  @@index([instance_id])
  @@schema("auth")
}

enum aal_level {
  aal1
  aal2
  aal3

  @@schema("auth")
}

enum code_challenge_method {
  s256
  plain

  @@schema("auth")
}

enum factor_status {
  unverified
  verified

  @@schema("auth")
}

enum factor_type {
  totp
  webauthn

  @@schema("auth")
}

Why is this necessary? It is necessary because Prisma tries to update our changes using a “shadow-database” and we need to baseline it so that we can work on our other migrations and use all the auth tables that are necessary for us to create associations.

Next, you want to run the following command

mkdir -p prisma/migrations/0_init_prisma_db_pull

Remember, the 0is important because Prisma Migrate applies migrations in lexicographic order. This should be one of the few times, if not the only time, you should ever create a manual migration like this.

Next. Let’s generate a migration that migrates from our empty database to the file that was generated by supabase.

Run the following

npx prisma migrate diff \
--from-empty \
--to-schema-datamodel prisma/schema.prisma \
--script > prisma/migrations/0_init_prisma_db_pull/migration.sql

This will generate an SQL script that will basically be used by your shadow-database. Let’s now make sure to resolve this migration manually and mark it as applied to baseline our database.

Run the following command

npx prisma migrate resolve --applied 0_init_prisma_db_pull

Take a look at your public database in Supabase. After the command has finished, you will notice that it has created a migration entry in the table _prisma_migrations. Now we have almost finished baselining our database. I say almost, because it doesn’t technically work yet and we will find out why.

Next, let’s try to create another migration. This will help to uncover errors that we need to finish to manually edit our migration.sqlscript a bit.

Go to the bottom of your schema.prisma script, and add the following to add a testtable with a namecolumn.

// Your Database Changes Start Here
model user_profiles {
  name        String @unique
  @@schema("public")
}

Now, let’s run a Prisma migration with the following command:

prisma migrate dev

At this point, you will actually get a failure. The error will look something like the following:

Error: P3006

Migration `0_init_prisma_db_pull` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: cannot use column reference in DEFAULT expression
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:266

So what’s happening. It seems that for some reason, we cannot apply the migration.sql script to our database for some reason. You can read about the issue on Github, but I will go over how to fix it here too to help you understand the issue.

The issue occurs because the script is messing up generating the auth.identies and auth.users table for 2 columns that need to be modified. Well, how should we get the correct script? Let’s do a pg_dump. To do this, let’s connect to our database using the connection url that supabase gave us!

Run the following command. When prompted for the password, type in your database password that you set. If you forgot it, reset it in Supabase’s Database Settings.

pg_dump -h db.<your_url>.supabase.co -p 5432 -d postgres -U postgres -p 5432 -f supase_pg_dump.sql

There’s a lot going on in the dump, but if you take a look, there’s 2 places that you need to change for now. It’s these 2 lines


CREATE TABLE auth.identities (
    id text NOT NULL,
    user_id uuid NOT NULL,
    identity_data jsonb NOT NULL,
    provider text NOT NULL,
    last_sign_in_at timestamp with time zone,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
-- Copy the Line below and replace that 
-- with the column line for "auth"."identities" migration.sql
    email text GENERATED ALWAYS AS (lower((identity_data ->> 'email'::text))) STORED
);


CREATE TABLE auth.users (
-- ....Other Columns Here----
-- Copy the line below and replace that with the `confirmed_at` 
-- column under "auth"."users" table in your migration.sql 
    confirmed_at timestamp with time zone GENERATED ALWAYS AS (LEAST(email_confirmed_at, phone_confirmed_at)) STORED,
-- ....Other Columns Here----
);

Now, make sure to go back to your Supabase Dashboard UI and clear our the Initial Migration Table that was finished. Otherwise, you’ll run into an issue where Supabase detects a migration drift and ask you to destroy and reset the database. NEVER EVER reset the database as this will mess up your Supabase and it will be hard or impossible to recover from.

Make sure to delete this so that we can re-apply our migration without issues

Now re-run the following command:

npx prisma migrate resolve --applied 0_init_prisma_db_pull

Now, let’s run the previous command

npx prisma migrate dev

When prompted for the migration name, use create-user-profiles-table.

Given that you have executed all the above commands correctly, the migration should execute correctly, and your output should look similar to the following:

You’ve successfully created and baselined your database!

Setting Up Row Level Security

Next is the fun part, where we create foreign-keys to our auth schema that was created by Supabase and setup Row Level Security. Let’s now do that. Modify your Prisma File to include the following changes

/// This model or at least one of its fields has comments in the database, and requires an additional setup for migrations: Read more: https://pris.ly/d/database-comments
model users {
  instance_id                 String?       @db.Uuid
  id                          String        @id @db.Uuid
  aud                         String?       @db.VarChar(255)
  role                        String?       @db.VarChar(255)
  email                       String?       @db.VarChar(255)
  encrypted_password          String?       @db.VarChar(255)
  email_confirmed_at          DateTime?     @db.Timestamptz(6)
  invited_at                  DateTime?     @db.Timestamptz(6)
  confirmation_token          String?       @db.VarChar(255)
  confirmation_sent_at        DateTime?     @db.Timestamptz(6)
  recovery_token              String?       @db.VarChar(255)
  recovery_sent_at            DateTime?     @db.Timestamptz(6)
  email_change_token_new      String?       @db.VarChar(255)
  email_change                String?       @db.VarChar(255)
  email_change_sent_at        DateTime?     @db.Timestamptz(6)
  last_sign_in_at             DateTime?     @db.Timestamptz(6)
  raw_app_meta_data           Json?
  raw_user_meta_data          Json?
  is_super_admin              Boolean?
  created_at                  DateTime?     @db.Timestamptz(6)
  updated_at                  DateTime?     @db.Timestamptz(6)
  phone                       String?       @unique
  phone_confirmed_at          DateTime?     @db.Timestamptz(6)
  phone_change                String?       @default("")
  phone_change_token          String?       @default("") @db.VarChar(255)
  phone_change_sent_at        DateTime?     @db.Timestamptz(6)
  confirmed_at                DateTime?     @default(dbgenerated("LEAST(email_confirmed_at, phone_confirmed_at)")) @db.Timestamptz(6)
  email_change_token_current  String?       @default("") @db.VarChar(255)
  email_change_confirm_status Int?          @default(0) @db.SmallInt
  banned_until                DateTime?     @db.Timestamptz(6)
  reauthentication_token      String?       @default("") @db.VarChar(255)
  reauthentication_sent_at    DateTime?     @db.Timestamptz(6)
  is_sso_user                 Boolean       @default(false)
  deleted_at                  DateTime?     @db.Timestamptz(6)
  identities                  identities[]
  mfa_factors                 mfa_factors[]
  sessions                    sessions[]
  // Your Foreign Key Links Start Here
  user_profile                user_profiles?

  @@index([instance_id])
  @@schema("auth")
}

// Your Database Changes Start Here
model user_profiles {
  name              String              @unique
  user_id           String              @unique  @db.Uuid
  user_auth_info    users               @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
  @@schema("public")
}

What we are doing here is creating a one-to-one relationship link. In the user_profiles row, the column user_id will be storing the id from the users table. In addition, we are making sure to also do a Cascade on Delete, which means if a user is ever deleted, we will also delete the corresponding rows linked to that user in the user_profiles table.

Let’s now create a new migration. This time run the following command.

npx prisma migrate dev --name alter-user-profiles-with-user-access --create-only

What’s different about this command? This command will actually create the migration scripts without apply it. This gives us a chance to modify the migration.sql script a bit before it gets applied.

This will create a migration script migration.sql. Take a look at the script. At this point, this looks pretty good, but we actually want to look at enforcing some row-level-policies. How do we do that? We need to create Security Policies! However, let’s actually do it in an SQL script as a part of the migration script instead of relying on the Supabase table.

You need to add the policy to your script. It will look like the following below

-- alter-user-profiles-with-user-access
/*
  Warnings:

  - A unique constraint covering the columns `[user_id]` on the table `user_profiles` will be added. If there are existing duplicate values, this will fail.
  - Added the required column `user_id` to the `user_profiles` table without a default value. This is not possible if the table is not empty.

*/
-- AlterTable
ALTER TABLE "public"."user_profiles" ADD COLUMN     "user_id" UUID NOT NULL;

-- CreateIndex
CREATE UNIQUE INDEX "user_profiles_user_id_key" ON "public"."user_profiles"("user_id");

-- AddForeignKey
ALTER TABLE "public"."user_profiles" ADD CONSTRAINT "user_profiles_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE ON UPDATE NO ACTION;

------------------------------------------------ START TO ADDITIONAL ITEMS FOR POLICIES ---------------------------------------------------
ALTER TABLE "public"."user_profiles" ENABLE ROW LEVEL SECURITY;
CREATE Policy "individual_authorized_user_profile_access"
    on user_profiles for select
    using (auth.uid() = user_id);
-- This Policy added will allow only users to access their own data
-- if the user has the correct auth id that matches the user_id of that row.

When you run this, you will still get a failure. It should like the below


Error: P3006

Migration `20230430022945_alter_user_profiles_with_user_access` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: function auth.uid() does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
   0: sql_schema_connector::validate_migrations
           with namespaces=Some(Namespaces("public", ["auth"]))
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:309
   1: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:266

What’s going on? Well, looking at it, it seems like the auth.uid() function doesn’t exist. If you take a look at the supabase_pg_dump file we generated, you will see that there’s 3 auth check functions that supabase created that we will need to add. This means we have to redo our baselining.

mkdir -p prisma/migrations/1_add_auth_rls_supabase_functions && touch prisma/migrations/1_add_auth_rls_supabase_functions/migration.sql

Now, under that migration.sql file, add the following. It will now look like below

-- 0_init_prisma_db_pull/migration.sql
-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "auth";

-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "public";

---------------------------- START OF MADE ADJUSTMENTS TO MAKE PRISMA WORK WITH SUPABASE ---------------------------

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
$$;

--
-- Name: FUNCTION email(); Type: COMMENT; Schema: auth; Owner: supabase_auth_admin
--

COMMENT ON FUNCTION auth.email() IS 'Deprecated. Use auth.jwt() -> ''email'' instead.';


--
-- Name: jwt(); Type: FUNCTION; Schema: auth; Owner: supabase_auth_admin
--

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


--
-- Name: role(); Type: FUNCTION; Schema: auth; Owner: supabase_auth_admin
--

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


--
-- Name: FUNCTION role(); Type: COMMENT; Schema: auth; Owner: supabase_auth_admin
--

COMMENT ON FUNCTION auth.role() IS 'Deprecated. Use auth.jwt() -> ''role'' instead.';


--
-- Name: uid(); Type: FUNCTION; Schema: auth; Owner: supabase_auth_admin
--

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

--
-- Name: FUNCTION uid(); Type: COMMENT; Schema: auth; Owner: supabase_auth_admin
--

COMMENT ON FUNCTION auth.uid() IS 'Deprecated. Use auth.jwt() -> ''sub'' instead.';


---------------------------- END OF MADE ADJUSTMENTS TO MAKE PRISMA WORK WITH SUPABASE ---------------------------

-- More SQL Code, but left out here since you should already have this.

However, at this point, our database has drifted quite a bit. This means, you will have to clear out your old migrations again. Go to your Supabase and delete your user_profiles table. Also, delete your _prisma_migrations. We will be redoing our migrations again to re-baseline with our new functions.

Make sure to delete _prisma_migrations_table too

This basically resets our migration and database state to the initial state as if we just baselined our database. You should now have the following migration orders at this point:

Now, let’s re-baseline and then migrate our database again.

npx prisma migrate resolve --applied 0_init_prisma_db_pull                                                                                                                ✔  22:56:53  
npx prisma migrate dev

Your migrations should have been applied and looks like the following:

Now, go to your user_profiles table, you will see that it has an active RLS policy applied to it on your supabase.

Now, that we got out of the way, let’s actually test the Supabase RLS policies.

Testing Your Supabase RLS Policies in Code

Create a new RLS User

Let’s create a new user that adheres to RLS policies (as our current Postgresql user with Prisma does not). Execute the following queries on Supbase’s query UI.

Past the following in the SQL Editor on Supabase:

-- while create user is an alias for role, user add login access
CREATE USER rls_user WITH PASSWORD 'password';
-- With a new user, they still need certain permissions to be able to access the database:
-- need usage of the public schema
GRANT USAGE ON SCHEMA public to rls_user;
-- need access to sequences for creates
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public to rls_user;
-- need access to tables
GRANT ALL ON ALL TABLES IN SCHEMA public TO rls_user;

Now, that we have our user, let’s edit our .env to add the RLS_DATABASE_URL which will be used by our Prisma Client so that it has limited execution permissions and abide by RLS security policies.

Our .env file will now look like the following:

#.env
# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="postgres://postgres::<your_password>@db.<supabase_url>.supabase.co:6543/postgres?pgbouncer=true"
# PostgreSQL connection string used for migrations
DIRECT_URL="postgres://postgres:<your_password>@db.<supabase_url>.supabase.co:5432/postgres"
# RLS_DATABASE_URL
RLS_DATABASE_URL="postgres://rls_user:password@db.<supabase_url>.supabase.co:6543/postgres?pgbouncer=true"

Let’s create 2 User accounts on Supabase Console for testing. Make sure to auto_confirm your users too.

Now, let’s create 2 entries in the user_profiles for each user.

Use Prisma Client with RLS

Now, let’s update our code to use the Prisma Client. First, modify app.controller.ts to return a promise. It should now look like this

// app.controller.ts
import { Controller, Get } from '@nestjs/common';
import { AppService } from './app.service';

@Controller()
export class AppController {
  constructor(private readonly appService: AppService) {}

  @Get()
  async getHello(): Promise<string> {
    return this.appService.getHello();
  }
}

Next, we will be using the useSupabaseRowLevelSecurity repository to allow us to set runtime parameters for our Prisma queries.You can read about it here at the Supabase guide. We are going to be creating a useSupabaseRowLevelSecurity extension and code from this repository (or you can use it directly).

At the core, we are basically setting a runtime parameter for our Postgesql database.

Run the following command

mkdir src/prismaExtensions && touch src/prismaExtensions/useSupabaseRowLevelSecurity.ts

Copy the following code from the prisma-extension-supabase-rls repository. Your file will now look like this

// useSupabaseRowLevelSecurity.ts
// prisma-extension-enable-supabase-row-level-security

import { Prisma, PrismaClient } from '@prisma/client'

export interface SupabaseRowLevelSecurityOptions {
  /**
   * The client extension name
   * @default 'supabaseRowLevelSecurity'
   */
  name?: string
  /**
   * The name of the Postgres setting to use for the claims, `request.jwt.claims` by default.
   * Supabase sets using set_config() and get with current_setting()
   *
   * @default 'request.jwt.claims'
   */
  claimsSetting?: string
  /**
   * A function that returns the JWT claims to use for the current request decoded from the Supabase access token.
   *
   * E.g.:
   *
   * {
   *   "aud": "authenticated",
   *   "exp": 1675711033,
   *   "sub": "00000000-0000-0000-0000-000000000000",
   *   "email": "user@example.com",
   *   "phone": "",
   *   "app_metadata": {
   *     "provider": "email",
   *     "providers": [
   *       "email"
   *     ]
   *   },
   *   "user_metadata": {},
   *   "role": "authenticated",
   *   "aal": "aal1",
   *   "amr": [
   *     {
   *       "method": "otp",
   *       "timestamp": 1675696651
   *     }
   *   ],
   *   "session_id": "000000000000-0000-0000-0000-000000000000"
   *  }
   */
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  claimsFn?: undefined | (() => Record<string, any>)
  /**
   * The error to throw when the policy check fails.
   */
  policyError?: Error
  /**
   * Log errors to the console.
   * @default false
   */
  logging?: boolean
}

const defaultSupabaseRowLevelSecurityOptions: SupabaseRowLevelSecurityOptions =
  {
    name: 'useSupabaseRowLevelSecurity',
    claimsSetting: 'request.jwt.claims',
    claimsFn: undefined,
    policyError: new Error('Not authorized.'),
    logging: false,
  }

export const useSupabaseRowLevelSecurity = (
  options: SupabaseRowLevelSecurityOptions = defaultSupabaseRowLevelSecurityOptions
) => {
  const name = options.name || defaultSupabaseRowLevelSecurityOptions.name
  const claimsFn =
    options.claimsFn || defaultSupabaseRowLevelSecurityOptions.claimsFn
  const claimsSetting =
    options.claimsSetting ||
    defaultSupabaseRowLevelSecurityOptions.claimsSetting
  const policyError =
    options.policyError || defaultSupabaseRowLevelSecurityOptions.policyError

  return Prisma.defineExtension((client: PrismaClient) =>
    client.$extends({
      name: name || 'useSupabaseRowLevelSecurity',
      query: {
        $allModels: {
          async $allOperations({ args, query }) {
            const claims = claimsFn ? JSON.stringify(claimsFn() || {}) : ''
            try {
              const [, result] = await client.$transaction([
                client.$executeRaw`SELECT set_config(${claimsSetting}, ${claims}, TRUE)`,
                query(args),
              ])

              return result
            } catch (e) {
              if (options.logging) console.error(e)
              throw policyError || e
            }
          },
        },
      },
    })
  )
}

Next, modify app.service.ts to now create a new rlsPrismaClient and point it to the RLS_DATABASE_URL. Your app.service.ts should now look like the folllowing:

// app.service.ts
import { Injectable } from '@nestjs/common';
import { Prisma, PrismaClient } from '@prisma/client';
import { useSupabaseRowLevelSecurity } from './prismaExtensions/useSupabaseRowLevelSecurity';

@Injectable()
export class AppService {
  // Prisma Client with RLS Policies Enforced
  private readonly rlsPrismaClient = new PrismaClient({
    datasources: {
      db: {
        url: process.env.RLS_DATABASE_URL
      }
    }
  });
  // Prisma Client that Bypasses Any Security Policies (SHOULD BE LIMITED)
  private readonly bypassRlsPrismaClient = new PrismaClient();

  /**
   * Database currently has two entries with the following user_ids
   * 1. b2e41732-ef4d-493e-92aa-9dfdfc2a0c08
   * 2. df0cc8cb-8483-41c4-a361-22070c8848c8
   * @returns string
   */
  async getHello(): Promise<string> {
    console.log("Getting first user profiles")
    const userId1 = 'b2e41732-ef4d-493e-92aa-9dfdfc2a0c08';
    const userId2 = 'df0cc8cb-8483-41c4-a361-22070c8848c8';

    const result = await this.rlsPrismaClient.$extends(useSupabaseRowLevelSecurity({
      claimsFn: () => ({
        sub: userId1,
      })
    })).user_profiles.findMany({
      where: {
        user_id: userId1,
      }
    });
    const noClaimsResult = await this.rlsPrismaClient.user_profiles.findMany({
      where: {
        user_id: userId1,
      }
    });
    const bypassResult = await this.bypassRlsPrismaClient.user_profiles.findMany();

    const myResponse = {
      rlsUserResult: result,
      bypassResult: bypassResult,
      rlsUserWithNoClaims: noClaimsResult,
    }
    console.log('Here are my results:', myResponse);
    return JSON.stringify(myResponse, null, '\t');
  }
}

Looking at the prints out, you should get a nice print like the following:

Here are my results: {
  rlsUserResult: [
    { name: 'User1', user_id: 'b2e41732-ef4d-493e-92aa-9dfdfc2a0c08' }
  ],
  bypassResult: [
    { name: 'User1', user_id: 'b2e41732-ef4d-493e-92aa-9dfdfc2a0c08' },
    { name: 'User2', user_id: 'df0cc8cb-8483-41c4-a361-22070c8848c8' }
  ],
  rlsUserWithNoClaims: []
}

What is happening? Despite trying to find all the users in the database, each query is different above. I’ll explain each below

  1. If you take a look at the rlsUserResult, that returns only the user1 results. That is because the auth_id only matches with only one user so it only returns that user’s row (abiding by row-level security policies)
  2. The bypassResult uses the bypass client, which allows it to default bypass any security policies so it returns all the entries it can find.
  3. The rlsUserWithNoClaims is using the rlsPrismaClient but it passes no claims data or proof of its user config or context. Therefore, it cannot access any results.

Now, you can use your Prisma Client with Supabase!

Conclusion (Updated)

As you can see, we now have Prisma with all the benefits of Supabase Row Level Security policies and we also get the included Migration benefits included.

I hope you all enjoyed this guide. I didn’t see another guide that was as comprehensive and really took me through all the setup steps to enable Supabase Row Level Security with Prisma that was able to walk me through all the error steps. If you have any tips, have any advice, or just want to reach out to me, feel free to comment below!

For the final repository after all the steps are completed: ngoctranfire/prisma-supabase-rls-example: Using Prisma with Supabase Row Level Security and Multischema (github.com)

UPDATED: I honestly don’t recommend this because it couples Prisma with your Supabase too much. You should do this in a way where there is no dependency. Be expecting another article soon.

Supabase
Prisma
Row Level Security
Nestjs

--

--

Ngoc Tran

Written by Ngoc Tran

23 Followers

I’m interested in learning about new technologies and sharing some of my passions on programming

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams