Supabase

Supabase is great, having your own supabase is even better. Pigsty helps you self-hosting supabase on your own servers, with better control, more extensions, lower cost and enterprise-grade features.

Migration in an hour, scales to billions!

Short Version

Here’s a comprehensive tutorial for self-hosting production-grade supabase on local/cloud VM/BMs.

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap                # install ansible
./configure -c app/supa    # use supabase config (please CHANGE CREDENTIALS in pigsty.yml)
vi pigsty.yml              # edit domain name, password, keys,...
./install.yml              # install pigsty
./docker.yml               # install docker compose
./app.yml                  # launch supabase stateless part with docker

What is Supabase?

Supabase is an open-source Firebase alternative, a Backend as a Service (BaaS).

Supabase wraps PostgreSQL kernel and vector extensions, alone with authentication, realtime subscriptions, edge functions, object storage, and instant REST and GraphQL APIs from your postgres schema. It let you skip most backend work, requiring only database design and frontend skills to ship quickly.

Currently, Supabase may be the most popular open-source project in the PostgreSQL ecosystem, boasting over 80,000 stars on GitHub. And become quite popular among developers, and startups, since they have a generous free plan, just like cloudflare & neon.

Why Self-Hosting?

Supabase’s slogan is: “Build in a weekend, Scale to millions”. It has great cost-effectiveness in small scales (4c8g) indeed. But there is no doubt that when you really grow to millions of users, some may choose to self-hosting their own Supabase —— for functionality, performance, cost, and other reasons.

That’s where Pigsty comes in. Pigsty provides a complete one-click self-hosting solution for Supabase. Self-hosted Supabase can enjoy full PostgreSQL monitoring, IaC, PITR, and high availability capability.

You can run the latest PostgreSQL 17(,16,15) kernels, (supabase is using the 15 currently), alone with 404 PostgreSQL extensions out-of-the-box. Run on mainstream Linus OS distros with production grade HA PostgreSQL, MinIO, Prometheus & Grafana Stack for observability, and Nginx for reverse proxy.

Since most of the supabase maintained extensions are not available in the official PGDG repo, we have compiled all the RPM/DEBs for these extensions and put them in the Pigsty repo:

• pg_graphql: GraphQL support in PostgreSQL (Rust extension via PIGSTY)
• pg_jsonschema: JSON Schema validation (Rust extension via PIGSTY)
• wrappers: Supabase’s foreign data source wrappers bundle (Rust extension via PIGSTY)
• index_advisor: Query index advisor (SQL extension via PIGSTY)
• pg_net: Async non-blocking HTTP/HTTPS requests in SQL (C extension via PIGSTY)
• vault: Store encrypted credentials in Vault (C extension via PIGSTY)
• pgjwt: PostgreSQL implementation of JSON Web Token API (SQL extension via PIGSTY)
• pgsodium: Table data encryption storage TDE (C extension via PIGSTY)
• supautils: Secure database clusters in cloud environments (C extension via PIGSTY)
• pg_plan_filter: Block specific queries using execution plan cost filtering (C extension via PIGSTY)

Everything is under your control, you have the ability and freedom to scale PGSQL, MinIO, and Supabase itself. And take full advantage of the performance and cost advantages of modern hardware like Gen5 NVMe SSD.

All you need is prepare a VM with several commands and wait for 10 minutes….

Get Started

First, download & install pigsty as usual, with the supa config template:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty
./bootstrap                # install ansible
./configure -c app/dify    # use dify config (please CHANGE CREDENTIALS in pigsty.yml)
vi pigsty.yml              # edit config file
./install.yml              # install pigsty

Please change the pigsty.yml config file according to your need before deploying Supabase. (Credentials) For dev/test/demo purposes, we will just skip that, and comes back later.

Then, run the docker.yml & app.yml to launch stateless part of supabase.

./docker.yml               # install docker compose
./app.yml                  # launch dify stateless part with docker

You can access the supabase API / Web UI through the 8000/8443 directly.

with configured DNS, or a local /etc/hosts entry, you can also use the default supa.pigsty domain name via the 80/443 infra portal.

Credentials for Supabase Studio: supabase : pigsty

Architecture

Pigsty’s supabase is based on the Supabase Docker Compose Template, with some slight modifications to fit-in Pigsty’s default ACL model.

The stateful part of this template is replaced by Pigsty’s managed PostgreSQL cluster and MinIO cluster. The container part are stateless, so you can launch / destroy / run multiple supabase containers on the same stateful PGSQL / MINIO cluster simultaneously to scale out.

The built-in supa.yml config template will create a single-node supabase, with a singleton PostgreSQL and SNSD MinIO server. You can use Multinode PostgreSQL Clusters and MNMD MinIO Clusters / external S3 service instead in production, we will cover that later.

Config Detail

Here are checklists for self-hosting

• Hardware: necessary VM/BM resources, one node at least, 3-4 are recommended for HA.
• Linux OS: Linux x86_64 server with fresh installed Linux, check compatible distro
• Network: Static IPv4 address which can be used as node identity
• Admin User: nopass ssh & sudo are recommended for admin user
• Conf Template: Use the supa config template, if you don’t know how to manually configure pigsty

The built-in conf/app/supa.yml config template is shown below.

For advanced topics, we may need to modify the configuration file to fit our needs.

• Security Enhancement
• Domain Name and HTTPS
• Sending Mail with SMTP
• MinIO or External S3
• True High Availability

Security Enhancement

For security reasons, you should change the default passwords in the pigsty.yml config file.

• grafana_admin_password: pigsty, Grafana admin password
• pg_admin_password: DBUser.DBA, PGSQL superuser password
• pg_monitor_password: DBUser.Monitor, PGSQL monitor user password
• pg_replication_password: DBUser.Replicator, PGSQL replication user password
• patroni_password: Patroni.API, Patroni HA Agent Password
• haproxy_admin_password: pigsty, Load balancer admin password
• minio_access_key: minioadmin, MinIO root username
• minio_secret_key: minioadmin, MinIO root password

Supabase will use PostgreSQL & MinIO as its backend, so also change the following passwords for supabase business users:

• pg_users: password for supabase business users in postgres
• minio_users: minioadmin, MinIO business user’s password

The pgbackrest will take backups and WALs to MinIO, so also change the following passwords reference

• pgbackrest_repo: refer to the

PLEASE check the Supabase Self-Hosting: Generate API Keys to generate supabase credentials:

• jwt_secret: a secret key with at least 40 characters
• anon_key: a jwt token generate for anonymous users, based on jwt_secret
• service_role_key: a jwt token generate for elevated service roles, based on jwt_secret
• dashboard_username: supabase studio web portal username, supabase by default
• dashboard_password: supabase studio web portal password, pigsty by default

If you have chanaged the default password for PostgreSQL and MinIO, you have to update the following parameters as well:

• postgres_password, according to pg_users
• s3_access_key and s3_secret_key, according to minio_users

Domain Name and HTTPS

If you’re using Supabase on your local machine or within a LAN, you can directly connect to Supabase via IP:Port through Kong’s exposed HTTP port 8000.

You can use a locally resolved domain name, but for serious production deployments, we recommend using a real domain name + HTTPS to access Supabase. In this case, you typically need to prepare the following:

• Your server should have a public IP address
• Purchase a domain name, use the DNS resolution services provided by cloud/DNS/CDN providers to point it to your installation node’s public IP (lower alternative: local /etc/hosts)
• Apply for a certificate, using free HTTPS certificates issued by certificate authorities like Let’s Encrypt for encrypted communication (lower alternative: default self-signed certificates, manually trusted)

You can refer to the certbot tutorial to apply for a free HTTPS certificate. Here we assume your custom domain name is: supa.pigsty.cc, then you should modify the supa domain in infra_portal like this:

all:
  vars:
    infra_portal:
      supa :
        domain: supa.pigsty.cc        # replace with your own domain!
        endpoint: "10.10.10.10:8000"
        websocket: true
        certbot: supa.pigsty.cc       # certificate name, usually the same as the domain name

If the domain name has been resolved to your server’s public IP, then you can automatically complete the certificate application and application in the Pigsty directory by executing the following command:

make cert

In addition to the Pigsty component passwords, you also need to modify the domain name related configurations of Supabase, including:

• SITE_URL
• API_EXTERNAL_URL
• SUPABASE_PUBLIC_URL:

Configure them to your custom domain name, for example: supa.pigsty.cc, then apply the configuration again:

./app.yml -t app_config,app_launch

As a lower alternative, you can use a local domain name to access Supabase. When using a local domain name, you can configure the resolution of supa.pigsty in the browser’s /etc/hosts or LAN DNS, pointing it to the public IP address of the installation node. The Nginx on the Pigsty management node will apply for a self-signed certificate for this domain name (the browser will display Unsecure), and forward the request to the 8000 port of Kong, which is processed by Supabase.

MinIO or External S3

Pigsty’s self-hosting supabase will use a local SNSD MinIO server, which is used by Supabase itself for object storage, and by PostgreSQL for backups. For production use, you should consider using a HA MNMD MinIO cluster or an external S3 compatible service instead.

We recommend using an external S3 when:

• you just have one single server available, then external s3 gives you a minimal disaster recovery guarantee, with RTO in hours and RPO in MBs.
• you are operating in the cloud, then using S3 directly is recommended rather than wrap expensively EBS with MinIO

The terraform/spec/aliyun-meta-s3.tf provides an example of how to provision a single node alone with an S3 bucket.

To use an external S3 compatible service, you’ll have to update two related references in the pigsty.yml config.

First, let’s update the S3 related configurations in all.children.supa.vars.apps.[supabase].conf, pointing it to the external S3 compatible service:

# if using s3/minio as file storage
S3_BUCKET: supa
S3_ENDPOINT: https://sss.pigsty:9000
S3_ACCESS_KEY: supabase
S3_SECRET_KEY: S3User.Supabase
S3_FORCE_PATH_STYLE: true
S3_PROTOCOL: https
S3_REGION: stub
MINIO_DOMAIN_IP: 10.10.10.10  # sss.pigsty domain name will resolve to this ip statically

Then, reload the supabase service with the following command:

./app.yml -t app_config,app_launch

You can also use S3 as the backup repository for PostgreSQL, in all.vars.pgbackrest_repo add a new aliyun backup repository definition:

all:
  vars:
    pgbackrest_method: aliyun          # pgbackrest backup method: local,minio,[other user-defined repositories...]
    pgbackrest_repo:                   # pgbackrest backup repository: https://pgbackrest.org/configuration.html#section-repository
      aliyun:                          # define a new backup repository aliyun
        type: s3                       # aliyun oss is s3-compatible object storage
        s3_endpoint: oss-cn-beijing-internal.aliyuncs.com
        s3_region: oss-cn-beijing
        s3_bucket: pigsty-oss
        s3_key: xxxxxxxxxxxxxx
        s3_key_secret: xxxxxxxx
        s3_uri_style: host

        path: /pgbackrest
        bundle: y
        cipher_type: aes-256-cbc
        cipher_pass: PG.${pg_cluster}   # set a cipher password bound to the cluster name
        retention_full_type: time
        retention_full: 14

Then, specify the use of the aliyun backup repository in all.vars.pgbackrest_mehod, and reset the pgBackrest backup:

./pgsql.yml -t pgbackrest

Pigsty will switch the backup repository to the external object storage.

Sending Mail with SMTP

Some Supabase features require email. For production use, I’d recommend using an external SMTP service. Since self-hosted SMTP servers often result in rejected or spam-flagged emails.

To do this, modify the Supabase configuration and add SMTP credentials:

all:
  children:
    supa:            # supa group
      vars:          # supa group vars
        apps:        # supa group app list
          supabase:  # the supabase app
            conf:    # the supabase app conf entries
              SMTP_HOST: smtpdm.aliyun.com:80
              SMTP_PORT: 80
              SMTP_USER: no_reply@mail.your.domain.com
              SMTP_PASS: your_email_user_password
              SMTP_SENDER_NAME: MySupabase
              SMTP_ADMIN_EMAIL: adminxxx@mail.your.domain.com
              ENABLE_ANONYMOUS_USERS: false

And don’t forget to reload the supabase service with ./app.yml -t app_config,app_launch

Backup Strategy

In the supabase template, Pigsty has already used a daily full backup at 01:00 AM, you can refer to the backup/restore to modify the backup strategy.

all:
  children:
    pg-meta:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars:
        pg_cluster: pg-meta  # daily full backup at 01:00 AM
        node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ]

Then, apply the Crontab configuration to the nodes with the following command:

./node.yml -t node_crontab

More about backup strategy, please refer to Backup Strategy

True High Availability

The default single-node deployment (with external S3) provides a minimal disaster recovery guarantee, with RTO in hours and RPO in MBs.

To achieve RTO < 30s and zero data loss, you need a multi-node high availability cluster with at least 3-nodes.

Which involves high availability for these components:

• ETCD: DCS requires at least three nodes to tolerate one node failure.
• PGSQL: PGSQL synchronous commit mode recommends at least three nodes.
• INFRA: It’s good to have two or three copies of observability stack.
• Supabase itself can also have multiple replicas to achieve high availability.

We recommend you to refer to the trio and safe config to upgrade your cluster to three nodes or more.

In this case, you also need to modify the access points for PostgreSQL and MinIO to use the DNS / L2 VIP / HAProxy HA access points.