跳转到主要内容

文档 v2.7.0 版本已不再积极维护，你当前浏览的是一份归档快照。 最新文档请参阅 最新版本。

Supabase：自建后端一条龙

如何使用 Pigsty 拉起当红炸子鸡数据库 —— 开源的 Firebase 替代 Supabase，并使用 Pigsty 管理其后端 PostgreSQL 数据库。

Supabase —— Build in a weekend, Scale to millions

Supabase 口号是：“花个周末写写，随便扩容至百万”

Supabase 是一个开源的 Firebase 替代，基于 PostgreSQL 提供了认证，开箱即用的 API，边缘函数，实时订阅，存储，向量嵌入能力。

Supabase 已经于 2024.04.15 正式宣布进入 GA 状态，Pigsty 也于第一时间跟进并提供了自托管的支持。

快速上手

To run supabase with existing postgres instance, prepare the database with supabase.yml

then launch the stateless part with the docker-compose.yml file:

cd app/supabase; make up    # https://supabase.com/docs/guides/self-hosting/docker

Then you can access the supabase studio dashboard via http://<admin_ip>:8000 by default, the default dashboard username is supabase and password is pigsty.

You can also configure the infra_portal to expose the WebUI to the public through Nginx and SSL.

PostgreSQL置备

Supabase 需要特定的 PostgreSQL 扩展插件，以及一系列角色与权限才可以正常工作。请注意，这些扩展插件目前仅在 EL系 操作系统（EL8 / EL9）下齐全，因此自建 Supabase 仅在 EL 发行版中可行。

Pigsty 可以通过声明式配置，替用户置备好所需的 PostgreSQL 集群：supabase.yml。

例如，下面的集群 pg-meta 就配置了 Supabase 所需的用户，数据库，扩展插件，权限等信息：

# supabase example cluster: pg-meta
# this cluster needs to be migrated with app/supabase/migration.sql :
# psql postgres://supabase_admin:[email protected]:5432/supa -v ON_ERROR_STOP=1 --no-psqlrc -f ~pigsty/app/supabase/migration.sql
pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta

    pg_users:
      # supabase roles: anon, authenticated, dashboard_user
      - { name: anon           ,login: false }
      - { name: authenticated  ,login: false }
      - { name: dashboard_user ,login: false ,replication: true ,createdb: true ,createrole: true }
      - { name: service_role   ,login: false ,bypassrls: true }
      # supabase users: please use the same password
      - { name: supabase_admin             ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: true   ,superuser: true ,replication: true ,createdb: true ,createrole: true ,bypassrls: true }
      - { name: authenticator              ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,roles: [ authenticated ,anon ,service_role ] }
      - { name: supabase_auth_admin        ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,createrole: true }
      - { name: supabase_storage_admin     ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,createrole: true ,roles: [ authenticated ,anon ,service_role ] }
      - { name: supabase_functions_admin   ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,createrole: true }
      - { name: supabase_replication_admin ,password: 'DBUser.Supa' ,replication: true }
      - { name: supabase_read_only_user    ,password: 'DBUser.Supa' ,bypassrls: true ,roles: [ pg_read_all_data ] }

    pg_databases:
      - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [ pigsty ]} # the optional pigsty cmdb

      # the supabase database (pg_cron should be installed in this database after bootstrap)
      - name: supa
        baseline: supa.sql    # the init-scripts: https://github.com/supabase/postgres/tree/develop/migrations/db/init-scripts
        owner: supabase_admin
        comment: supabase postgres database
        schemas: [ extensions ,auth ,realtime ,storage ,graphql_public ,supabase_functions ,_analytics ,_realtime ]
        extensions:
          - { name: pgcrypto  ,schema: extensions  } # 1.3   : cryptographic functions
          - { name: pg_net    ,schema: extensions  } # 0.9.1 : async HTTP
          - { name: pgjwt     ,schema: extensions  } # 0.2.0 : json web token API for postgres
          - { name: uuid-ossp ,schema: extensions  } # 1.1   : generate universally unique identifiers (UUIDs)
          - { name: pgsodium        }                # 3.1.9 : pgsodium is a modern cryptography library for Postgres.
          - { name: supabase_vault  }                # 0.2.8 : Supabase Vault Extension
          - { name: pg_graphql      }                # 1.5.4 : pg_graphql: GraphQL support
          - { name: pg_jsonschema   }                # 0.3.1 : pg_jsonschema: Validate json schema
          - { name: wrappers        }                # 0.3.1 : wrappers: FDW collections
          - { name: http            }                # 1.6   : http: allows web page retrieval inside the database.

    # supabase required extensions
    pg_libs: 'pg_net, pg_cron, pg_stat_statements, auto_explain'    # add pg_net to shared_preload_libraries
    pg_extensions:                                         # supabase required extensions
      - pg_repack_16* wal2json_16* pgvector_16* pg_cron_16* pgsodium_16*
      - pg_graphql_16 pg_jsonschema_16 wrappers_16 vault_16* pgjwt_16* pg_net_16* pgsql_http_16*

    # supabase hba rules, require access from docker network
    pg_hba_rules:
      - { user: all ,db: supa ,addr: intra       ,auth: pwd ,title: 'allow supa database access from intranet'      }
      - { user: all ,db: supa ,addr: 172.0.0.0/8 ,auth: pwd ,title: 'allow supa database access from docker network'}
      - { user: all ,db: supa ,addr: all         ,auth: pwd ,title: 'allow supa database access from entire world'  }  # not safe!

请注意在 supa 数据库定义中的 baseline: supa.sql，它会在集群初始化的时候执行 files/supa.sql，完成一部分数据库迁移任务，这些模式定义是来自 Supabase的初始化脚本，基本是固定的，会自动执行。

你还需要在集群初始化后，运行另一部分初始化脚本： migration.sql，这一部分的模式定义是可能会变化的，是从 supabase/postgres/migrations/db/migrations 目录中按照执行顺序合并而来，并根据 Pigsty 的实际情况进行调整得到的模式变更脚本。

当前的最新版本：同步至 20231013070755

为了运行这个模式迁移 SQL，你可以使用 psql 命令行工具，连接到 pg-meta 集群，并执行：

# connection string
PGURL=postgres://supabase_admin:[email protected]:5432/supa

# check connectivity & extensions
psql ${PGURL} -c '\dx'

# perform migration
psql ${PGURL} -v ON_ERROR_STOP=1 --no-psqlrc -f ~/pigsty/app/supabase/migration.sql

成功执行后，这个 PostgreSQL 数据库集群就可以用于 Supabase 了！

已知的 pgsodium 参数问题

已知的问题：“ERROR: unrecognized configuration parameter “pgsodium.enable_event_trigger”: https://github.com/Vonng/pigsty/issues/350

为了避免出现这个问题，你需要修改 PostgreSQL 的 配置，并重启生效：

pg edit-config pg-meta --force -p pgsodium.enable_event_trigger='off' # setup pgsodium event trigger
pg restart pg-meta                                                    # restart pg-meta to enable the new configuration
psql ${PGURL} -c 'SHOW pgsodium.enable_event_trigger;'                # should be off or false
启用 pg_cron 扩展插件

pg_cron 是一个可选的扩展插件，用于在 PostgreSQL 中执行定时任务，但不幸地是，你只能选择一个数据库安装此扩展。如果你想要在 supa 数据库中启用可选的 pg_cron 支持，你可以使用以下命令：

pg edit-config pg-meta --force -p cron.database_name='supa'           # setup pg_cron database_name parameter
psql ${PGURL} -c 'SHOW cron.database_name;'                           # should be the underlying database name for supabase
psql ${PGURL} -c 'CREATE EXTENSION pg_cron;';                         # create pg_cron extension on the database 'supa'

无状态的部分

Supabase stateless part is managed by docker-compose, the docker-compose.yml file we use here is a simplified version of github.com/supabase/docker/docker-compose.yml.

Everything you need to care about is in the .env file, which contains important settings for supabase. It is already configured to use the pg-meta.supa database by default, You have to change that according to your actual deployment.

############
# Secrets - YOU MUST CHANGE THESE BEFORE GOING INTO PRODUCTION
############
# you have to change the JWT_SECRET to a random string with at least 32 characters long
# and issue new ANON_KEY/SERVICE_ROLE_KEY JWT with that new secret, check the tutorial:
# https://supabase.com/docs/guides/self-hosting/docker#securing-your-services
JWT_SECRET=your-super-secret-jwt-token-with-at-least-32-characters-long
ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyAgCiAgICAicm9sZSI6ICJhbm9uIiwKICAgICJpc3MiOiAic3VwYWJhc2UtZGVtbyIsCiAgICAiaWF0IjogMTY0MTc2OTIwMCwKICAgICJleHAiOiAxNzk5NTM1NjAwCn0.dc_X5iR_VP_qT0zsiyj_I_OZ2T9FtRU2BBNWN8Bu4GE
SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyAgCiAgICAicm9sZSI6ICJzZXJ2aWNlX3JvbGUiLAogICAgImlzcyI6ICJzdXBhYmFzZS1kZW1vIiwKICAgICJpYXQiOiAxNjQxNzY5MjAwLAogICAgImV4cCI6IDE3OTk1MzU2MDAKfQ.DaYlNEoUrrEn2Ig7tqibS-PHK5vgusbcbo7X36XVt4Q

############
# Dashboard - Credentials for the Supabase Studio WebUI
############
DASHBOARD_USERNAME=supabase         # change to your own username
DASHBOARD_PASSWORD=pigsty           # change to your own password

############
# Database - You can change these to any PostgreSQL database that has logical replication enabled.
############
POSTGRES_HOST=10.10.10.10           # change to Pigsty managed PostgreSQL cluster/instance VIP/IP/Hostname
POSTGRES_PORT=5432                  # you can use other service port such as 5433, 5436, 6432, etc...
POSTGRES_DB=supa                    # change to supabase database name, `supa` by default in pigsty
POSTGRES_PASSWORD=DBUser.Supa       # supabase dbsu password (shared by multiple supabase biz users)

Usually you’ll have to change these parameters accordingly. Here we’ll use fixed username, password and IP:Port database connstr for simplicity.

The postgres username is fixed as supabase_admin and the password is DBUser.Supa, change that according to your supabase.yml And the supabase studio WebUI credential is managed by DASHBOARD_USERNAME and DASHBOARD_PASSWORD, which is supabase and pigsty by default.

The official tutorial: Self-Hosting with Docker just have all the details you need.

Hint

You can use the Primary Service of that cluster through DNS/VIP and other service ports, or whatever access method you like.

You can also configure supabase.storage service to use the MinIO service managed by pigsty, too

Once configured, you can launch the stateless part with docker-compose or make up shortcut:

cd ~/pigsty/app/supabase; make up    #  = docker compose up

对外暴露服务

Supabase Studio 提供了一个 Web 管理界面，默认监听 8000 端口，你可以将其加入 infra_portal，来对外 暴露服务。

    infra_portal:                     # domain names and upstream servers
      # ...
      supa         : { domain: supa.pigsty ,endpoint: "10.10.10.10:8000", websocket: true }

To expose the service, you can run the infra.yml playbook with the nginx tag:

./infra.yml -t nginx

Make suare supa.pigsty or your own domain is resolvable to the infra_portal server, and you can access the supabase studio dashboard via https://supa.pigsty.

最后修改 2024年06月23日