Drizzle Supabase RLS

Requirements

• Docker (https://docs.docker.com/get-docker/)
• Supabase cli (https://github.com/supabase/cli)

Doc: https://supabase.com/docs/guides/cli/local-development

Initial setup

Test the incoming Drizzle RLS Support

• Fork (or clone) @Angelelz repo https://github.com/Angelelz/drizzle-orm/tree/feat-rls, checkout the PR branch and pnpm run build
• Then you go to drizzle-orm/dist and run pwd to get the path
• Finally, in this project, in the package.json file, replace the drizzle-orm dependency with the path you got previously. "drizzle-orm": "file:/your/folder/to/drizzle-orm/dist"
• Then run pnpm install to install this new dependency

Start Supabase services

supabase start

Once all of the Supabase services are running, you'll see output containing your local Supabase credentials. It should look like this, with urls and keys that you'll use in the project:

Started supabase local development setup.

         API URL: http://127.0.0.1:54321
     GraphQL URL: http://127.0.0.1:54321/graphql/v1
          DB URL: postgresql://postgres:postgres@127.0.0.1:54322/postgres
      Studio URL: http://127.0.0.1:54323
    Inbucket URL: http://127.0.0.1:54324
      JWT secret: super-secret-jwt-token-with-at-least-32-characters-long
        anon key: eyJhbGci.....
service_role key: eyJhbGci....

Note I have made a dump of the auth.users table that is applied by Supabase on the first start. supabase/seed.sql

Copy the .env.example file to .env

You can keep the default value for DATABASE_URL

In your .env file, set the following environment variables (from the output above):

• SUPABASE_ANON_KEY ➡️ anon key

  This public sharable key is used in combination with RLS.

• SUPABASE_SERVICE_KEY ➡️ service_role key

  This private secret key bypass RLS

• SUPABASE_API_URL ➡️ API URL

  Used by Supabase SDK. We need it to seed some data.

Note We will enable RLS on all table and use the SUPABASE_ANON_KEY when a user want to access the data.

Deploy migration and seed the DB

# In a terminal, run
pnpm run seed

Supabase Dashboard (Studio)

http://127.0.0.1:54323

Supabase cli quick commands

supabase start # Start Supabase services
supabase stop  # Stop Supabase services
supabase stop --no-backup # Stop Supabase services and reset your local database

How to test the incoming Drizzle RLS Support

I have defined some policies on this project (0001_colossal_snowbird.sql).

You can delete them and re-create them in the Drizzle schema.

Note In order to going further, I have created a custom auth function to use in a policy. It should demonstrate that we can inject any configuration we want in the transaction.

create or replace function auth.faction() returns text as $$
 select nullif(current_setting('user.faction', true), '')::text;
$$ language sql stable;

Deleting a policy

DROP POLICY IF EXISTS "Full policy name" ON "public"."the_table_name";

DROP POLICY IF EXISTS "User can only read its row" ON "public"."user";
DROP POLICY IF EXISTS "User can update its name" ON "public"."user";
DROP POLICY IF EXISTS "User can read all quotes" ON "public"."quote";
DROP POLICY IF EXISTS "User can insert quotes" ON "public"."quote";
DROP POLICY IF EXISTS "User can delete its quotes" ON "public"."quote";

-- Special goodies: use a custom auth function --
DROP POLICY IF EXISTS "User can only list users of the same faction" ON "public"."faction";

Disabling RLS on a table

ALTER TABLE "the_table_name" DISABLE ROW LEVEL SECURITY;

I am stuck, I have errors when I deploy a migration

Stop Supabase services and delete docker containers/volumes and start again 😂.

# In a terminal, run
supabase stop --no-backup