Skip to content

A full-featured CRM built with React, shadcn/ui, and Supabase.

4.6 KB
Raw Blame
// RLS-scoped SQL execution for the MCP tools.
//
// Replaces @supabase/server's withPostgresClient, which runs every query as
// `begin; set_config(claims); set local role; <sql>; commit` and offers no way
// to extend that prelude. The connection string is Supabase's SUPABASE_DB_URL,
// i.e. the `postgres` role (BYPASSRLS, member of service_role), so everything
// that keeps a query inside the caller's rights happens in this transaction.
//
// This module has no npm: imports on purpose: it only needs the `query` and
// `release` shape of a pg client, so the unit tests drive it with a stub.

export interface SqlQueryResult {
  rows: unknown[];
}

export interface SqlClient {
  query(text: string, params?: unknown[]): Promise<SqlQueryResult>;
  release(destroy?: boolean): void;
}

export interface SqlPool {
  connect(): Promise<SqlClient>;
}

export interface QueryOptions {
  // Runs the statement in a READ ONLY transaction: Postgres then refuses any
  // write, including those hidden inside a volatile function called from a
  // SELECT (merge_contacts, pg_net's request queue...).
  readOnly?: boolean;
}

export interface RlsPostgres {
  query(
    text: string,
    params?: unknown[],
    options?: QueryOptions,
  ): Promise<unknown[]>;
}

// Only role the MCP endpoint ever assumes. withSupabase({ auth: "user" })
// already guarantees a user token; refusing anything else here means a
// surprising claim can never widen the role instead of silently narrowing it.
const ALLOWED_ROLE = "authenticated";

// Same budget as the `authenticated` role gets through PostgREST. That role
// setting does not apply here: statement_timeout from a role's config is read
// at login, and this connection logs in as `postgres`, not as `authenticated`.
export const STATEMENT_TIMEOUT = "8s";

// Only `public` (pg_catalog stays implicit). `extensions` and `net` hold the
// http functions that let a query make the database issue HTTP requests;
// keeping them off the search_path means an unqualified `http_get(...)` does
// not resolve, whatever the SQL validator did or did not catch. Functions in
// `public` carry their own `SET search_path`, so they are unaffected.
export const SEARCH_PATH = "public";

const PERMISSION_DENIED = "42501";

function resolveRole(claims: Record<string, unknown>): string {
  const requested = claims.role;
  if (requested !== ALLOWED_ROLE) {
    throw new Error(
      `Unsupported role ${JSON.stringify(requested)}: the MCP server only runs queries as "${ALLOWED_ROLE}".`,
    );
  }
  return requested;
}

async function runInTransaction(
  client: SqlClient,
  claims: Record<string, unknown>,
  role: string,
  text: string,
  params: unknown[] | undefined,
  options: QueryOptions,
): Promise<unknown[]> {
  await client.query("begin");
  await client.query(`select set_config('request.jwt.claims', $1, true)`, [
    JSON.stringify(claims),
  ]);
  await client.query(`set local role "${role}"`);
  await client.query(`set local search_path = ${SEARCH_PATH}`);
  await client.query(`set local statement_timeout = '${STATEMENT_TIMEOUT}'`);
  if (options.readOnly) {
    await client.query("set transaction read only");
  }
  const result = await client.query(text, params);
  await client.query("commit");
  return result.rows;
}

export function createRlsPostgres(
  pool: SqlPool,
  claims: Record<string, unknown>,
): RlsPostgres {
  const role = resolveRole(claims);
  return {
    async query(text, params, options = {}) {
      const client = await pool.connect();
      let poisoned = false;
      try {
        return await runInTransaction(
          client,
          claims,
          role,
          text,
          params,
          options,
        );
      } catch (error) {
        try {
          await client.query("rollback");
        } catch {
          // The connection is in an unknown state: drop it from the pool.
          poisoned = true;
        }
        if (
          error instanceof Error &&
          (error as { code?: string }).code === PERMISSION_DENIED
        ) {
          error.message += ` (queries run as the caller's role '${role}'; grant that role the table privileges it needs, e.g. "grant select on <table> to ${role}")`;
        }
        throw error;
      } finally {
        if (!poisoned) {
          // The pool is shared by every caller and logs in as `postgres`:
          // drop whatever session state the statement may have left behind
          // (session-level advisory locks, LISTEN, temp objects...) so nothing
          // carries over to the next request.
          try {
            await client.query("discard all");
          } catch {
            poisoned = true;
          }
        }
        client.release(poisoned);
      }
    },
  };
}