Skip to content

The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.

23.2 KB
Raw Blame
import {
  ident,
  joinSqlFragments,
  keyword,
  literal,
  safeSql,
  type SafeSqlFragment,
  type SqlFragmentSeparator,
} from '../../../pg-format'

type SimplifiedWrapperMeta = {
  name: string
  handlerName: string
  validatorName: string
  server: { options: { name: string; encrypted: boolean }[] }
}

type WrapperExistingTable = {
  id: string | number
  schema: string
  name: string
  columns: { name: string; type: string }[]
  options: string[] | null
}

type WrapperFormTable = {
  id?: string | number
  schema_name: string
  table_name: string
  columns: { name: string; type: string }[]
  is_new_schema?: boolean
} & Record<string, unknown>

// Old wrappers has an implicit dependency on pgsodium. For new wrappers we use Vault directly.
const LEGACY_WRAPPER_EXTENSION_VERSIONS = [
  '0.1.0',
  '0.1.1',
  '0.1.4',
  '0.1.5',
  '0.1.6',
  '0.1.7',
  '0.1.8',
  '0.1.9',
  '0.1.10',
  '0.1.11',
  '0.1.12',
  '0.1.14',
  '0.1.15',
  '0.1.16',
  '0.1.17',
  '0.1.18',
  '0.1.19',
  '0.2.0',
  '0.3.0',
  '0.3.1',
  '0.4.0',
  '0.4.1',
  '0.4.2',
  '0.4.3',
  '0.4.4',
  '0.4.5',
]

const TABLE_META_KEYS = new Set([
  'table_name',
  'schema_name',
  'schema',
  'id',
  'columns',
  'index',
  'is_new_schema',
])

const isUsingOldWrappersSql = safeSql`
  (select extversion from pg_extension where extname = 'wrappers') in (${joinSqlFragments(
    LEGACY_WRAPPER_EXTENSION_VERSIONS.map((version) => literal(version)),
    ','
  )})
`

function parseOptionsArray(options?: string[] | null): Record<string, string> {
  return Object.fromEntries(
    (options ?? []).map((entry) => {
      const index = entry.indexOf('=')
      return index === -1 ? [entry, ''] : [entry.slice(0, index), entry.slice(index + 1)]
    })
  )
}

function getTableOptionsMap(table: Record<string, unknown>): Record<string, string> {
  return Object.fromEntries(
    Object.entries(table).filter(([key, value]) => !TABLE_META_KEYS.has(key) && Boolean(value)) as [
      string,
      string,
    ][]
  )
}

// Many of this file's SQL fragments are optional (e.g. "only if renaming"),
// but composing them via a fixed multi-line template leaves the template's own
// line breaks behind even when the fragment is empty. Filtering empties out
// before joining keeps the generated SQL free of stray blank lines.
function joinNonEmptyFragments(
  fragments: SafeSqlFragment[],
  separator: SqlFragmentSeparator = '\n'
): SafeSqlFragment {
  return joinSqlFragments(
    fragments.filter((fragment) => fragment.trim().length > 0),
    separator
  )
}

function buildCreateForeignTableSql(
  table: {
    schema_name: string
    table_name: string
    columns: { name: string; type: string }[]
  } & Record<string, unknown>,
  serverName: string
): SafeSqlFragment {
  const optionsMap = getTableOptionsMap(table)

  return safeSql`
    create foreign table ${ident(table.schema_name)}.${ident(table.table_name)} (
      ${joinSqlFragments(
        table.columns.map((column) => safeSql`${ident(column.name)} ${keyword(column.type)}`),
        ','
      )}
    )
    server ${ident(serverName)}
    options (
      ${joinSqlFragments(
        Object.entries(optionsMap).map(([key, value]) => safeSql`${ident(key)} ${literal(value)}`),
        ','
      )}
    );
  `
}

function buildRenameForeignTableSql(
  current: WrapperExistingTable,
  desired: WrapperFormTable
): SafeSqlFragment {
  const schemaChanged = desired.schema_name !== current.schema
  const nameChanged = desired.table_name !== current.name

  const setSchemaSql = schemaChanged
    ? safeSql`alter foreign table ${ident(current.schema)}.${ident(current.name)} set schema ${ident(desired.schema_name)};`
    : safeSql``

  // Once SET SCHEMA has run, the table is addressed by its new schema but still its old name.
  const effectiveSchema = schemaChanged ? desired.schema_name : current.schema
  const renameSql = nameChanged
    ? safeSql`alter foreign table ${ident(effectiveSchema)}.${ident(current.name)} rename to ${ident(desired.table_name)};`
    : safeSql``

  return joinNonEmptyFragments([setSchemaSql, renameSql])
}

// ALTER FOREIGN TABLE doesn't support changing a column's type in place, so a
// retyped column is dropped and re-added with the new type.
function diffTableColumns(
  currentColumns: { name: string; type: string }[],
  desiredColumns: { name: string; type: string }[]
) {
  const currentByName = new Map(currentColumns.map((column) => [column.name, column.type]))
  const desiredByName = new Map(desiredColumns.map((column) => [column.name, column.type]))

  const columnsToAdd = desiredColumns.filter(
    (column) => currentByName.get(column.name) !== column.type
  )
  const columnsToDrop = currentColumns.filter(
    (column) => desiredByName.get(column.name) !== column.type
  )

  return { columnsToAdd, columnsToDrop }
}

function buildOptionsDiffClauses(
  currentOptions: Record<string, string>,
  desiredOptions: Record<string, string>
): SafeSqlFragment[] {
  const clauses: SafeSqlFragment[] = []

  for (const [key, value] of Object.entries(desiredOptions)) {
    if (!(key in currentOptions)) {
      clauses.push(safeSql`add ${ident(key)} ${literal(value)}`)
    } else if (currentOptions[key] !== value) {
      clauses.push(safeSql`set ${ident(key)} ${literal(value)}`)
    }
  }
  for (const key of Object.keys(currentOptions)) {
    if (!(key in desiredOptions)) {
      clauses.push(safeSql`drop ${ident(key)}`)
    }
  }

  return clauses
}

function buildAlterForeignTableSql(
  schema: string,
  table: string,
  current: WrapperExistingTable,
  desired: WrapperFormTable
): SafeSqlFragment {
  const { columnsToAdd, columnsToDrop } = diffTableColumns(current.columns, desired.columns)

  const dropColumnsSql =
    columnsToDrop.length > 0
      ? safeSql`
          alter foreign table ${ident(schema)}.${ident(table)}
          ${joinSqlFragments(
            columnsToDrop.map((column) => safeSql`drop column ${ident(column.name)}`),
            ', '
          )};
        `
      : safeSql``

  const addColumnsSql =
    columnsToAdd.length > 0
      ? safeSql`
          alter foreign table ${ident(schema)}.${ident(table)}
          ${joinSqlFragments(
            columnsToAdd.map(
              (column) => safeSql`add column ${ident(column.name)} ${keyword(column.type)}`
            ),
            ', '
          )};
        `
      : safeSql``

  const optionsClauses = buildOptionsDiffClauses(
    parseOptionsArray(current.options),
    getTableOptionsMap(desired)
  )
  const optionsSql =
    optionsClauses.length > 0
      ? safeSql`
          alter foreign table ${ident(schema)}.${ident(table)}
          options (${joinSqlFragments(optionsClauses, ', ')});
        `
      : safeSql``

  return joinNonEmptyFragments([dropColumnsSql, addColumnsSql, optionsSql])
}

export const getFDWsSql = (): SafeSqlFragment => {
  const sql = safeSql`
    select
      s.oid as "id",
      w.fdwname as "name",
      s.srvname as "server_name",
      s.srvoptions as "server_options",
      c.proname as "handler",
      (
        select jsonb_agg(
          jsonb_build_object(
            'id', c.oid::bigint,
            'schema', relnamespace::regnamespace::text,
            'name', c.relname,
            'columns', (
              select jsonb_agg(
                jsonb_build_object(
                  'name', a.attname,
                  'type', pg_catalog.format_type(a.atttypid, a.atttypmod)
                )
              )
              from pg_catalog.pg_attribute a
              where a.attrelid = c.oid and a.attnum > 0 and not a.attisdropped
            ),
            'options', t.ftoptions
          )
        )
        from pg_catalog.pg_class c
        join pg_catalog.pg_foreign_table t on c.oid = t.ftrelid
        where c.oid = any (select t.ftrelid from pg_catalog.pg_foreign_table t where t.ftserver = s.oid)
      ) as "tables"
    from pg_catalog.pg_foreign_server s
    join pg_catalog.pg_foreign_data_wrapper w on s.srvfdw = w.oid
    join pg_catalog.pg_proc c on w.fdwhandler = c.oid;
  `

  return sql
}

export function getCreateForeignDataWrapperSql(
  wrapperMeta: SimplifiedWrapperMeta
): SafeSqlFragment {
  return safeSql`
    do $$
    begin
      if not exists (
        select 1 from pg_catalog.pg_foreign_data_wrapper where fdwname = ${literal(wrapperMeta.name)}
      ) then
        execute format(
          'create foreign data wrapper %I handler %I validator %I',
          ${literal(wrapperMeta.name)},
          ${literal(wrapperMeta.handlerName)},
          ${literal(wrapperMeta.validatorName)}
        );
      end if;
    end $$;
  `
}

export function getCreateFDWSql({
  wrapperMeta,
  formState,
  mode,
  tables,
  sourceSchema,
  targetSchema,
  schemaOptions = [],
}: {
  wrapperMeta: SimplifiedWrapperMeta
  formState: {
    [k: string]: string
  }
  // If mode is skip, the wrapper will skip the last step, binding the schema/tables to foreign data. This could be done later.
  mode: 'tables' | 'schema' | 'skip'
  tables: WrapperFormTable[]
  sourceSchema: string
  targetSchema: string
  schemaOptions?: SafeSqlFragment[]
}): SafeSqlFragment {
  const newSchemasSql = joinSqlFragments(
    tables
      .filter((table) => table.is_new_schema)
      .map((table) => safeSql`create schema if not exists ${ident(table.schema_name)};`),
    '\n'
  )

  const encryptedOptions = wrapperMeta.server.options.filter((option) => option.encrypted)
  const unencryptedOptions = wrapperMeta.server.options.filter((option) => !option.encrypted)

  const createEncryptedKeysSqlArray = encryptedOptions.map((option) => {
    const key = `${formState.server_name}_${option.name}`
    const quotedValue = literal(formState[option.name] || '')

    return safeSql`
      do $$
      begin
        if ${isUsingOldWrappersSql} then
          create extension if not exists pgsodium;

          perform pgsodium.create_key(
            name := ${literal(key)}
          );

          perform vault.create_secret(
            new_secret := ${quotedValue},
            new_name   := ${literal(key)},
            new_key_id := (select id from pgsodium.valid_key where name = ${literal(key)})
          );
        else
          perform vault.create_secret(
            new_secret := ${quotedValue},
            new_name := ${literal(key)}
          );
        end if;
      end $$;
    `
  })

  const createEncryptedKeysSql = joinSqlFragments(createEncryptedKeysSqlArray, '\n')

  const encryptedOptionsSqlArray = encryptedOptions
    .filter((option) => formState[option.name])
    .map((option) => safeSql`${ident(option.name)} ''%s''`)
  // Unencrypted option values are passed as format() %L arguments (below) so
  // Postgres escapes them once for the inner create-server literal. Pre-escaping
  // here and embedding in the outer E'...' string would decode backslashes twice,
  // which aborts creation (invalid Unicode escape) or silently corrupts the value.
  const unencryptedOptionsFilter = unencryptedOptions.filter((option) => formState[option.name])
  const unencryptedOptionsSqlArray = unencryptedOptionsFilter.map(
    (option) => safeSql`${ident(option.name)} %L`
  )
  const optionsSqlArray = joinSqlFragments(
    [...encryptedOptionsSqlArray, ...unencryptedOptionsSqlArray],
    ','
  )

  const createServerSql = safeSql`
    do $$
    declare
      -- Old wrappers has an implicit dependency on pgsodium. For new wrappers
      -- we use Vault directly.
      is_using_old_wrappers bool;
      ${joinSqlFragments(
        encryptedOptions.map((option) => safeSql`${ident(`v_${option.name}`)} text;`),
        '\n'
      )}
    begin
      is_using_old_wrappers := ${isUsingOldWrappersSql};
      ${joinSqlFragments(
        encryptedOptions.map(
          (option) => safeSql`
              if is_using_old_wrappers then
                select id into ${ident(`v_${option.name}`)} from pgsodium.valid_key where name = ${literal(`${formState.server_name}_${option.name}`)} limit 1;
              else
                select id into ${ident(`v_${option.name}`)} from vault.secrets where name = ${literal(`${formState.server_name}_${option.name}`)} limit 1;
              end if;
            `
        ),
        '\n'
      )}
    
      execute format(
        E'create server ${ident(formState.server_name)} foreign data wrapper ${ident(wrapperMeta.name)} options (${optionsSqlArray});',
        ${joinSqlFragments(
          [
            ...encryptedOptions
              .filter((option) => formState[option.name])
              .map((option) => ident(`v_${option.name}`)),
            ...unencryptedOptionsFilter.map((option) => literal(formState[option.name])),
          ],
          ','
        )}
      );
    end $$;
  `

  const createTablesSql = joinSqlFragments(
    tables.map((newTable) => buildCreateForeignTableSql(newTable, formState.server_name)),
    '\n\n'
  )

  const options = joinSqlFragments([...schemaOptions, safeSql`strict 'true'`], ', ')

  function createImportForeignSchemaSql(): SafeSqlFragment {
    return safeSql`
  import foreign schema ${ident(sourceSchema)} from server ${ident(formState.server_name)} into ${ident(targetSchema)} options (${options});
`
  }

  const sql = joinNonEmptyFragments(
    [
      newSchemasSql,
      createEncryptedKeysSql,
      createServerSql,
      mode === 'tables' ? createTablesSql : safeSql``,
      mode === 'schema' ? createImportForeignSchemaSql() : safeSql``,
    ],
    '\n\n'
  )

  return sql
}

export const getDeleteFDWSql = ({
  wrapper,
  wrapperMeta,
}: {
  wrapper: { id: number; name: string; server_name: string; server_options?: string[] | null }
  wrapperMeta: SimplifiedWrapperMeta
}): SafeSqlFragment => {
  const encryptedOptions = wrapperMeta.server.options.filter((option) => option.encrypted)
  const currentServerOptions = parseOptionsArray(wrapper.server_options)

  // Secrets are looked up by the id already stored in the server's own option
  // value, never by a guessed name - the naming convention for new secrets has
  // changed more than once across this feature's history, but the stored id is
  // stable regardless of when the wrapper was created.
  const deleteEncryptedSecretsSqlArray = encryptedOptions
    .filter((option) => currentServerOptions[option.name] !== undefined)
    .map((option) => {
      const existingSecretId = currentServerOptions[option.name]

      return safeSql`
        do $$
        begin
          if ${isUsingOldWrappersSql} then
            delete from vault.secrets where key_id = ${literal(existingSecretId)};
            delete from pgsodium.key where id = ${literal(existingSecretId)};
          else
            delete from vault.secrets where id = ${literal(existingSecretId)}::uuid;
          end if;
        end $$;
      `
    })

  const deleteEncryptedSecretsSql = joinSqlFragments(deleteEncryptedSecretsSqlArray, '\n')

  const ensureSelectedServerSql = safeSql`
    begin
      if not exists (
        select 1
        from pg_catalog.pg_foreign_server s
        join pg_catalog.pg_foreign_data_wrapper w on w.oid = s.srvfdw
        where s.oid = ${literal(wrapper.id)}
          and s.srvname = ${literal(wrapper.server_name)}
          and w.fdwname = ${literal(wrapper.name)}
      ) then
        raise exception 'The selected foreign server no longer belongs to this wrapper.';
      end if;
    end
  `

  const sql = safeSql`
    do ${literal(ensureSelectedServerSql)};

    drop server if exists ${ident(wrapper.server_name)} cascade;

    do $$
    begin
      if not exists (
        select 1
        from pg_catalog.pg_foreign_server s
        join pg_catalog.pg_foreign_data_wrapper w on w.oid = s.srvfdw
        where w.fdwname = ${literal(wrapper.name)}
      ) then
        execute format('drop foreign data wrapper if exists %I cascade', ${literal(wrapper.name)});
      end if;
    end $$;

    ${deleteEncryptedSecretsSql}
  `

  return sql
}

export const getUpdateFDWSql = ({
  wrapper,
  wrapperMeta,
  formState,
  tables,
}: {
  wrapper: {
    id: number
    name: string
    server_name: string
    server_options?: string[] | null
    tables?: WrapperExistingTable[] | null
  }
  wrapperMeta: SimplifiedWrapperMeta
  formState: { [k: string]: string }
  tables: WrapperFormTable[]
}): SafeSqlFragment => {
  // wrapperMeta.name is fixed per wrapper type and shared across every server
  // that uses it, so it's never user-editable here - only the server itself
  // (formState.server_name / wrapper.server_name) can be configured per connection.
  const newServerName = formState.server_name || wrapper.server_name
  const isRenamingServer = newServerName !== wrapper.server_name

  // Runs before every other statement so the rest of this transaction can
  // target the server by its new name.
  const renameServerSql = isRenamingServer
    ? safeSql`alter server ${ident(wrapper.server_name)} rename to ${ident(newServerName)};`
    : safeSql``

  const encryptedOptions = wrapperMeta.server.options.filter((option) => option.encrypted)
  const unencryptedOptions = wrapperMeta.server.options.filter((option) => !option.encrypted)
  const currentServerOptions = parseOptionsArray(wrapper.server_options)

  const serverOptionClauses: SafeSqlFragment[] = []
  const encryptedOptionSqlArray: SafeSqlFragment[] = []

  for (const option of unencryptedOptions) {
    const currentValue = currentServerOptions[option.name]
    const newValue = formState[option.name]

    if (newValue) {
      if (currentValue === undefined) {
        serverOptionClauses.push(safeSql`add ${ident(option.name)} ${literal(newValue)}`)
      } else if (currentValue !== newValue) {
        serverOptionClauses.push(safeSql`set ${ident(option.name)} ${literal(newValue)}`)
      }
    } else if (currentValue !== undefined) {
      serverOptionClauses.push(safeSql`drop ${ident(option.name)}`)
    }
  }

  for (const option of encryptedOptions) {
    const existingSecretId = currentServerOptions[option.name]
    const newValue = formState[option.name]
    const newSecretName = `${newServerName}_${option.name}`

    if (newValue && existingSecretId !== undefined) {
      // Secret already exists: update its value in place instead of deleting and recreating it.
      encryptedOptionSqlArray.push(safeSql`
        do $$
        declare
          v_secret_id uuid;
        begin
          if ${isUsingOldWrappersSql} then
            select id into v_secret_id from vault.secrets where key_id = ${literal(existingSecretId)} limit 1;
          else
            v_secret_id := ${literal(existingSecretId)}::uuid;
          end if;

          perform vault.update_secret(
            secret_id := v_secret_id,
            new_secret := ${literal(newValue)},
            new_name := ${literal(newSecretName)}
          );
        end $$;
      `)
    } else if (newValue && existingSecretId === undefined) {
      // Option newly populated: create the secret then wire it up as a server option.
      encryptedOptionSqlArray.push(safeSql`
        do $$
        begin
          if ${isUsingOldWrappersSql} then
            create extension if not exists pgsodium;

            perform pgsodium.create_key(name := ${literal(newSecretName)});

            perform vault.create_secret(
              new_secret := ${literal(newValue)},
              new_name := ${literal(newSecretName)},
              new_key_id := (select id from pgsodium.valid_key where name = ${literal(newSecretName)})
            );
          else
            perform vault.create_secret(
              new_secret := ${literal(newValue)},
              new_name := ${literal(newSecretName)}
            );
          end if;
        end $$;

        do $$
        declare
          v_secret_ref text;
        begin
          if ${isUsingOldWrappersSql} then
            select id::text into v_secret_ref from pgsodium.valid_key where name = ${literal(newSecretName)} limit 1;
          else
            select id::text into v_secret_ref from vault.secrets where name = ${literal(newSecretName)} limit 1;
          end if;

          execute format('alter server ${ident(newServerName)} options (add ${ident(option.name)} %L)', v_secret_ref);
        end $$;
      `)
    } else if (!newValue && existingSecretId !== undefined) {
      // Option cleared: drop the server option and delete the now-orphaned secret.
      serverOptionClauses.push(safeSql`drop ${ident(option.name)}`)
      encryptedOptionSqlArray.push(safeSql`
        do $$
        begin
          if ${isUsingOldWrappersSql} then
            delete from vault.secrets where key_id = ${literal(existingSecretId)};
            delete from pgsodium.key where id = ${literal(existingSecretId)};
          else
            delete from vault.secrets where id = ${literal(existingSecretId)}::uuid;
          end if;
        end $$;
      `)
    }
  }

  const alterServerOptionsSql =
    serverOptionClauses.length > 0
      ? safeSql`
          alter server ${ident(newServerName)}
          options (${joinSqlFragments(serverOptionClauses, ', ')});
        `
      : safeSql``
  const encryptedOptionsSql = joinSqlFragments(encryptedOptionSqlArray, '\n')

  const currentTables = wrapper.tables ?? []
  const currentTableById = new Map(currentTables.map((table) => [table.id, table]))
  const desiredTableIds = new Set(
    tables.map((table) => table.id).filter((id): id is string | number => id !== undefined)
  )

  const tablesToCreate = tables.filter(
    (table) => table.id === undefined || !currentTableById.has(table.id)
  )
  const tablesToDrop = currentTables.filter((table) => !desiredTableIds.has(table.id))
  const matchedTables = tables
    .map((desired) => {
      if (desired.id === undefined) return null
      const current = currentTableById.get(desired.id)
      return current ? { current, desired } : null
    })
    .filter(
      (match): match is { current: WrapperExistingTable; desired: WrapperFormTable } =>
        match !== null
    )

  const newSchemasSql = joinSqlFragments(
    tablesToCreate
      .filter((table) => table.is_new_schema)
      .map((table) => safeSql`create schema if not exists ${ident(table.schema_name)};`),
    '\n'
  )
  const createTablesSql = joinSqlFragments(
    tablesToCreate.map((table) => buildCreateForeignTableSql(table, newServerName)),
    '\n'
  )
  const dropTablesSql = joinSqlFragments(
    tablesToDrop.map((table) =>
      getDropForeignTableSql({ schema: table.schema, table: table.name })
    ),
    '\n'
  )
  const renameTablesSql = joinSqlFragments(
    matchedTables.map(({ current, desired }) => buildRenameForeignTableSql(current, desired)),
    '\n'
  )
  const alterTablesSql = joinSqlFragments(
    matchedTables.map(({ current, desired }) =>
      buildAlterForeignTableSql(desired.schema_name, desired.table_name, current, desired)
    ),
    '\n'
  )

  const sql = joinNonEmptyFragments([
    renameServerSql,
    alterServerOptionsSql,
    encryptedOptionsSql,
    newSchemasSql,
    dropTablesSql,
    createTablesSql,
    renameTablesSql,
    alterTablesSql,
  ])

  return sql
}

export function getImportForeignSchemaSql({
  serverName,
  sourceSchema,
  targetSchema,
  schemaOptions = [],
}: {
  serverName: string
  sourceSchema: string
  targetSchema: string
  schemaOptions?: SafeSqlFragment[]
}): SafeSqlFragment {
  const options = joinSqlFragments([...schemaOptions, safeSql`strict 'true'`], ', ')

  const sql = safeSql`
  import foreign schema ${ident(sourceSchema)} from server ${ident(serverName)} into ${ident(targetSchema)} options (${options});
`

  return sql
}

export function getDropForeignTableSql({
  schema,
  table,
}: {
  schema: string
  table: string
}): SafeSqlFragment {
  const sql = safeSql`
drop foreign table if exists ${ident(schema)}.${ident(table)};
`

  return sql
}