Simulate Supabase Postgres RLS (Row Level Security)

If we want to guarantee that the user’s request has permission to run a query, we either have to check for it on the code level or somehow join and add a where clause everywhere in our code.

As it turns out Supabase has a well-integrated Postgres RLS (Row Level Security) system for granular authorization rules.

https://supabase.com/docs/guides/auth/row-level-security

You can emulate Supabase’s Postgres RLS, via the following:

/** -------------------------------------------------------
 * Example users table
 * ------------------------------------------------------- */
CREATE TABLE "some_schema"."user" (
  "id" UUID NOT NULL DEFAULT UUID_generate_v7(),
  "name" VARCHAR(255),
  "token" VARCHAR(255),
  "created_ts" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Enable RLS on the table
-- Once enabled, all read & write for non object-owner will be denied
-- ALTER TABLE "some_schema"."user" FORCE ROW LEVEL SECURITY;
ALTER TABLE "some_schema"."user" ENABLE ROW LEVEL SECURITY;

/** -------------------------------------------------------
 * Function to retrieve the user's auth session ID from a
 * transactional context config key "auth.session.uid" set
 * by the client for the current query
 * ------------------------------------------------------- */
CREATE OR REPLACE FUNCTION "public"."auth_uid"() RETURNS UUID 
AS $$
  SELECT NULLIF(CURRENT_SETTING('auth.session.uid', True), '')::UUID;
$$ LANGUAGE sql;

/** -------------------------------------------------------
 * Define the policy on the table
 * Users can only see their own data
 * ------------------------------------------------------- */
CREATE POLICY "Users can select their own data"
ON "some_schema"."user"
AS PERMISSIVE -- enum [PERMISSIVE, RESTRICTIVE]
FOR SELECT
USING (
  (SELECT "public".auth_uid()) = "user".id
);

Notes:

• ENABLE ROW LEVEL SECURITY: It allows users with the BYPASSRLS privilege (like the table owner or superusers) to bypass these RLS policies.
• FORCE ROW LEVEL SECURITY: It ensures that row-level security policies are applied to all users, including those with the BYPASSRLS privilege.

RLS policies can be defined as either PERMISSIVE or RESTRICTIVE.

• PERMISSIVE: This is the default behavior for policies. If multiple PERMISSIVE policies exist for a table and a specific action (e.g., SELECT, INSERT, UPDATE, DELETE), a row will be accessible if it satisfies the conditions of any of those permissive policies. Logical OR
• RESTRICTIVE: If at least one restrictive policy exists for an action on a table, a row must satisfy both the conditions of all permissive policies (if any) and all restrictive policies to be accessible. So there needs to be at least one permissive policy before restrictive policies can be usefully used to reduce that access. When a mix of permissive and restrictive policies is present, a record is only accessible if at least one of the permissive policies passes, in addition to all the restrictive policies.

/** -------------------------------------------------------
 * Testing the policy with non-object owner role
 * No rows returned
 * ------------------------------------------------------- */
SELECT * FROM "some_schema"."user";

/** -------------------------------------------------------
 * Note: The config is scoped locally when "is_local = True" to avoid
 * affecting other transactions even within the same session
 * ------------------------------------------------------- */
BEGIN TRANSACTION;
SELECT SET_CONFIG ('auth.session.uid', '018e4eac-4eab-71d7-9172-6be8dee057f7', True);
SELECT "public".auth_uid(); -- Just for testing
SELECT * FROM "some_schema"."user";
END TRANSACTION;

Examples of RLS:

1. https://supabase.com/docs/guides/database/postgres/row-level-security
2. https://github.com/barbinbrad/carbon/blob/main/packages/database/supabase/migrations/20230126050441_abac.sql
3. https://github.com/barbinbrad/carbon/blob/main/packages/database/supabase/migrations/20230123004206_claims.sql
4. https://supabase-sql.vercel.app/rls-advanced-policies
5. https://github.com/drizzle-team/drizzle-orm/issues/594
6. https://github.com/drizzle-team/drizzle-orm/discussions/2450