Add SQL example

[dan-hale]

Here is the migration file I used to get this working in my Supabase postgres project. I'm not confident I did everything properly, especially with the scopes... so please don't directly copy this - use it as a reference to build your own. I do not take responsibility for this implementation. I'm new to Oauth2.

create role oauth2;
GRANT oauth2 TO authenticator;
GRANT anon TO oauth2;

CREATE SCHEMA IF NOT EXISTS oauth2;

CREATE TYPE code_challenge_method AS ENUM ('S256', 'plain');

CREATE OR REPLACE VIEW oauth2.users AS SELECT raw_app_meta_data as app_metadata, raw_user_meta_data as user_metadata, aud, email, phone, 'oauth2' as iss, 'oauth2' as role, id as sub, id FROM auth.users;

CREATE TABLE IF NOT EXISTS oauth2.scopes (
    "name" text PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS oauth2.clients (
    "id" text PRIMARY KEY,
    "name" text NOT NULL,
    "secret" text,
    "redirectUris" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[], 
    "scopes" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    "allowedGrants" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[]
);

CREATE TABLE IF NOT EXISTS oauth2.codes (
    "code" text PRIMARY KEY,
    "redirectUri" text,
    "codeChallenge" text,
    "codeChallengeMethod" code_challenge_method,
    "expiresAt" TIMESTAMP WITH TIME ZONE NOT NULL,
    "user" uuid references auth.users (id), 
    "clientId" text NOT NULL references oauth2.clients(id), 
    "scopes" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[]
);

CREATE TABLE IF NOT EXISTS oauth2.tokens (
    "accessToken" TEXT PRIMARY KEY NOT NULL,
    "accessTokenExpiresAt" TIMESTAMP WITH TIME ZONE NOT NULL,
    "refreshToken" TEXT UNIQUE,
    "refreshTokenExpiresAt" TIMESTAMP WITH TIME ZONE,
    "client" text NOT NULL references oauth2.clients(id), 
    "user" uuid references auth.users (id), 
    "scopes" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    "originatingAuthCodeId" TEXT
);

CREATE OR REPLACE FUNCTION oauth2.scopes(oauth2.clients) RETURNS SETOF oauth2.scopes LANGUAGE sql STABLE SET search_path = '' AS $$
  SELECT * FROM oauth2.scopes WHERE scopes.name = ANY($1.scopes)
$$;

CREATE OR REPLACE FUNCTION oauth2.scopes(oauth2.codes) RETURNS SETOF oauth2.scopes LANGUAGE sql STABLE SET search_path = '' AS $$
  SELECT * FROM oauth2.scopes WHERE scopes.name = ANY($1.scopes)
$$;

CREATE OR REPLACE FUNCTION oauth2.scopes(oauth2.tokens) RETURNS SETOF oauth2.scopes LANGUAGE sql STABLE SET search_path = '' AS $$
  SELECT * FROM oauth2.scopes WHERE scopes.name = ANY($1.scopes)
$$;

CREATE OR REPLACE FUNCTION oauth2.users(oauth2.codes) RETURNS SETOF oauth2.users rows 1 LANGUAGE sql STABLE SET search_path = '' AS $$
  SELECT * FROM oauth2.users WHERE id = $1.user
$$;

CREATE OR REPLACE FUNCTION oauth2.users(oauth2.tokens) RETURNS SETOF oauth2.users rows 1 LANGUAGE sql STABLE SET search_path = '' AS $$
  SELECT * FROM oauth2.users WHERE id = $1.user
$$;

GRANT USAGE ON SCHEMA oauth2 TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA oauth2 TO service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA oauth2 TO service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA oauth2 TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA oauth2 GRANT ALL ON TABLES TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA oauth2 GRANT ALL ON ROUTINES TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA oauth2 GRANT ALL ON SEQUENCES TO service_role;

[jasonraimondi]

I could definitely add what an example sql repository, I suppose my main reason for not doing that initially was because I figured users would be using all sorts of backends. You could use any persistance layer you wanted, not just a relational db. MongoDB, DynamoDB, etc. I realize I need to make the examples more obvious because in the examples, it does come opinionated, using a relational db.

[jasonraimondi]

I realized there is an example Prisma schema file with a basic setup, and a more advanced prisma schema one here with migration here. If I remember correctly, Supabase uses Postgres under the hood. I think the sql functions are probably unnecessary since they're mainly working around the array-based scope storage and I think proper JOIN queries would be cleaner and more performant and your schema could improve with pivot tables for scopes instead of arrays.