# Supabase：自建后端一条龙

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

[Supabase](https://supabase.com/) —— Build in a weekend, Scale to millions

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

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

Supabase 已经于 2024.04.15 正式宣布进入 [GA](https://supabase.com/ga) 状态，Pigsty 也于第一时间跟进并提供了自托管的支持。


-----------------------

## 快速上手

To run supabase with existing postgres instance, prepare the [database](#database) with [`supabase.yml`](https://github.com/Vonng/pigsty/blob/v2.7.0/files/pigsty/supabase.yml)

then launch the [stateless part](#stateless-part) with the [`docker-compose.yml`](https://github.com/Vonng/pigsty/blob/v2.7.0/app/supabase/docker-compose.yml) file:

```bash
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`](https://github.com/Vonng/pigsty/blob/v2.7.0/files/pigsty/supabase.yml)。

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

```yaml
# supabase example cluster: pg-meta
# this cluster needs to be migrated with app/supabase/migration.sql :
# psql postgres://supabase_admin:DBUser.Supa@10.10.10.10: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`](https://github.com/Vonng/pigsty/blob/v2.7.0/files/supa.sql)，完成一部分数据库迁移任务，这些模式定义是来自 [Supabase的初始化脚本](https://github.com/supabase/postgres/tree/develop/migrations/db/init-scripts)，基本是固定的，会自动执行。


你还需要在集群初始化后，运行另一部分初始化脚本： [`migration.sql`](https://github.com/Vonng/pigsty/blob/v2.7.0/app/supabase/migration.sql)，这一部分的模式定义是可能会变化的，是从 [supabase/postgres/migrations/db/migrations](https://github.com/supabase/postgres/tree/develop/migrations/db/migrations) 目录中按照执行顺序合并而来，并根据 Pigsty 的实际情况进行调整得到的模式变更脚本。

> 当前的最新版本：同步至 [20231013070755](https://github.com/supabase/postgres/blob/develop/migrations/db/migrations/20231013070755_grant_authenticator_to_supabase_storage_admin.sql)

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

```bash
# connection string
PGURL=postgres://supabase_admin:DBUser.Supa@10.10.10.10: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 的 [配置](/zh/docs/pgsql/admin#配置集群)，并重启生效：

```bash
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` 支持，你可以使用以下命令：

```bash
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`](https://github.com/Vonng/pigsty/blob/v2.7.0/app/supabase/docker-compose.yml) file we use here is a simplified version of [github.com/supabase/docker/docker-compose.yml](https://github.com/supabase/supabase/blob/master/docker/docker-compose.yml).

Everything you need to care about is in the [`.env`](https://github.com/Vonng/pigsty/blob/v2.7.0/app/supabase/.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.

```bash
############
# 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`](https://github.com/Vonng/pigsty/blob/v2.7.0/files/pigsty/supabase.yml#L43)
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](https://supabase.com/docs/guides/self-hosting/docker) just have all the details you need.

> ### Hint
>
> You can use the [Primary Service](https://github.com/Vonng/pigsty/blob/v2.7.0/docs/PGSQL-SVC.md#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:

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




-----------------------

## 对外暴露服务

Supabase Studio 提供了一个 Web 管理界面，默认监听 `8000` 端口，你可以将其加入 `infra_portal`，来对外 [**暴露服务**](/zh/docs/tasks/nginx)。

```yaml
    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:

```bash
./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`.