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…