Repository: supabase/cli
Pull request #2353 (2024-05-31 13:31:28)

feat: replace pgbouncer with supavisor
## What kind of change does this PR introduce?

feature

## What is the new behavior?

Replaces deprecated pgbouncer with supavisor while maintaining the same config.

## Additional context

Add any other context or screenshots.


coveralls: ## Pull Request Test Coverage Report for [Build 9319223158](https://coveralls.io/builds/67816546)


### Details

* **1** of **46**   **(2.17%)**  changed or added relevant lines in **2** files are covered.
* **7** unchanged lines in **2** files lost coverage.
* Overall coverage decreased (**-0.2%**) to **60.026%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [internal/services/services.go](https://coveralls.io/builds/67816546/source?filename=internal%2Fservices%2Fservices.go#L55) | 0 | 1 | 0.0%
| [internal/start/start.go](https://coveralls.io/builds/67816546/source?filename=internal%2Fstart%2Fstart.go#L928) | 1 | 45 | 2.22%
<!-- | **Total:** | **1** | **46** | **2.17%** | -->

|  Files with Coverage Reduction | New Missed Lines | % |
| :-----|--------------|--: |
| [internal/storage/rm/rm.go](https://coveralls.io/builds/67816546/source?filename=internal%2Fstorage%2Frm%2Frm.go#L78) | 2 | 89.53% |
| [internal/gen/keys/keys.go](https://coveralls.io/builds/67816546/source?filename=internal%2Fgen%2Fkeys%2Fkeys.go#L101) | 5 | 11.76% |
<!-- | **Total:** | **7** |  | -->

|  Totals | [![Coverage Status](https://coveralls.io/builds/67816546/badge)](https://coveralls.io/builds/67816546) |
| :-- | --: |
| Change from base [Build 9314572968](https://coveralls.io/builds/67810047): |  -0.2% |
| Covered Lines: | 6888 |
| Relevant Lines: | 11475 |

---
##### 💛  - [Coveralls](https://coveralls.io)


Patch:
diff --git a/cmd/bootstrap.go b/cmd/bootstrap.go
index 64fb1af2..da5d96e4 100644
--- a/cmd/bootstrap.go
+++ b/cmd/bootstrap.go
@@ -65,7 +65,7 @@ var (
 
 func init() {
 	bootstrapFlags := bootstrapCmd.Flags()
-	bootstrapFlags.StringP("password", "p", "", "Password to your remote Postgres database.")
+	bootstrapFlags.StringVarP(&dbPassword, "password", "p", "", "Password to your remote Postgres database.")
 	cobra.CheckErr(viper.BindPFlag("DB_PASSWORD", bootstrapFlags.Lookup("password")))
 	rootCmd.AddCommand(bootstrapCmd)
 }
diff --git a/cmd/link.go b/cmd/link.go
index facaa532..780b667e 100644
--- a/cmd/link.go
+++ b/cmd/link.go
@@ -45,7 +45,8 @@ var (
 func init() {
 	linkFlags := linkCmd.Flags()
 	linkFlags.StringVar(&flags.ProjectRef, "project-ref", "", "Project ref of the Supabase project.")
-	linkFlags.StringP("password", "p", "", "Password to your remote Postgres database.")
+	linkFlags.StringVarP(&dbPassword, "password", "p", "", "Password to your remote Postgres database.")
+	// For some reason, BindPFlag only works for StringVarP instead of StringP
 	cobra.CheckErr(viper.BindPFlag("DB_PASSWORD", linkFlags.Lookup("password")))
 	rootCmd.AddCommand(linkCmd)
 }
diff --git a/go.mod b/go.mod
index 5b0ac7e4..02d588e4 100644
--- a/go.mod
+++ b/go.mod
@@ -43,7 +43,7 @@ require (
 	github.com/spf13/afero v1.11.0
 	github.com/spf13/cobra v1.8.0
 	github.com/spf13/pflag v1.0.5
-	github.com/spf13/viper v1.18.2
+	github.com/spf13/viper v1.19.0
 	github.com/stretchr/testify v1.9.0
 	github.com/stripe/pg-schema-diff v0.7.0
 	github.com/withfig/autocomplete-tools/packages/cobra v1.2.0
diff --git a/go.sum b/go.sum
index 14623da3..78ce8f46 100644
--- a/go.sum
+++ b/go.sum
@@ -927,8 +927,8 @@ github.com/spf13/pflag v1.0.0/go.mod h1:DYY7MBk1bdzusC3SYhjObp+wFpr4gzcvqqNjLnIn
 github.com/spf13/pflag v1.0.5 h1:iy+VFUOCP1a+8yFto/drg2CJ5u0yRoB7fZw3DKv/JXA=
 github.com/spf13/pflag v1.0.5/go.mod h1:McXfInJRrz4CZXVZOBLb0bTZqETkiAhM9Iw0y3An2Bg=
 github.com/spf13/viper v0.0.0-20150530192845-be5ff3e4840c/go.mod h1:A8kyI5cUJhb8N+3pkfONlcEcZbueH6nhAm0Fq7SrnBM=
-github.com/spf13/viper v1.18.2 h1:LUXCnvUvSM6FXAsj6nnfc8Q2tp1dIgUfY9Kc8GsSOiQ=
-github.com/spf13/viper v1.18.2/go.mod h1:EKmWIqdnk5lOcmR72yw6hS+8OPYcwD0jteitLMVB+yk=
+github.com/spf13/viper v1.19.0 h1:RWq5SEjt8o25SROyN3z2OrDB9l7RPd3lwTWU8EcEdcI=
+github.com/spf13/viper v1.19.0/go.mod h1:GQUN9bilAbhU/jgc1bKs99f/suXKeUMct8Adx5+Ntkg=
 github.com/spkg/bom v0.0.0-20160624110644-59b7046e48ad/go.mod h1:qLr4V1qq6nMqFKkMo8ZTx3f+BZEkzsRUY10Xsm2mwU0=
 github.com/ssgreg/nlreturn/v2 v2.2.1 h1:X4XDI7jstt3ySqGU86YGAURbxw3oTDPK9sPEi6YEwQ0=
 github.com/ssgreg/nlreturn/v2 v2.2.1/go.mod h1:E/iiPB78hV7Szg2YfRgyIrk1AD6JVMTRkkxBiELzh2I=
diff --git a/internal/db/dump/dump.go b/internal/db/dump/dump.go
index ae812dea..680aed9e 100644
--- a/internal/db/dump/dump.go
+++ b/internal/db/dump/dump.go
@@ -96,11 +96,12 @@ func dumpData(ctx context.Context, config pgconn.Config, schema, excludeTable []
 		"extensions",
 		"pgbouncer",
 		"realtime",
-		"_realtime",
 		// "storage",
-		"_analytics",
 		// "supabase_functions",
 		"supabase_migrations",
+		"_analytics",
+		"_realtime",
+		"_supavisor",
 	}
 	var env []string
 	if len(schema) > 0 {
diff --git a/internal/db/start/templates/schema.sql b/internal/db/start/templates/schema.sql
index a215f0d8..0d001023 100644
--- a/internal/db/start/templates/schema.sql
+++ b/internal/db/start/templates/schema.sql
@@ -18,6 +18,9 @@ alter schema _realtime owner to postgres;
 create schema if not exists _analytics;
 alter schema _analytics owner to postgres;
 
+create schema if not exists _supavisor;
+alter schema _supavisor owner to postgres;
+
 BEGIN;
 
 -- Create pg_net extension
diff --git a/internal/services/services.go b/internal/services/services.go
index 626139f2..f0bd2e1b 100644
--- a/internal/services/services.go
+++ b/internal/services/services.go
@@ -52,7 +52,7 @@ func GetServiceImages() []string {
 		utils.StudioImage,
 		utils.PgmetaImage,
 		utils.LogflareImage,
-		utils.PgbouncerImage,
+		utils.SupavisorImage,
 		utils.ImageProxyImage,
 	}
 }
diff --git a/internal/start/start.go b/internal/start/start.go
index 16192be0..6b89ddd8 100644
--- a/internal/start/start.go
+++ b/internal/start/start.go
@@ -136,6 +136,23 @@ var (
 	vectorConfigTemplate = template.Must(template.New("vectorConfig").Parse(vectorConfigEmbed))
 )
 
+type poolerTenant struct {
+	DbHost            string
+	DbPort            uint16
+	DbDatabase        string
+	DbPassword        string
+	ExternalId        string
+	ModeType          utils.PoolMode
+	DefaultMaxClients uint
+	DefaultPoolSize   uint
+}
+
+var (
+	//go:embed templates/pooler.exs
+	poolerTenantEmbed    string
+	poolerTenantTemplate = template.Must(template.New("poolerTenant").Parse(poolerTenantEmbed))
+)
+
 func run(p utils.Program, ctx context.Context, fsys afero.Fs, excludedContainers []string, dbConfig pgconn.Config, options ...func(*pgx.ConnConfig)) error {
 	excluded := make(map[string]bool)
 	for _, name := range excludedContainers {
@@ -171,12 +188,9 @@ func run(p utils.Program, ctx context.Context, fsys afero.Fs, excludedContainers
 EOF
 `},
 				Healthcheck: &container.HealthConfig{
-					Test: []string{"CMD",
-						"wget",
-						"--no-verbose",
-						"--tries=1",
-						"--spider",
-						"http://127.0.0.1:9001/health"},
+					Test: []string{"CMD", "wget", "--no-verbose", "--tries=1", "--spider",
+						"http://127.0.0.1:9001/health",
+					},
 					Interval: 10 * time.Second,
 					Timeout:  2 * time.Second,
 					Retries:  3,
@@ -267,7 +281,9 @@ EOF
 EOF
 `},
 				Healthcheck: &container.HealthConfig{
-					Test:        []string{"CMD", "curl", "-sSfL", "--head", "-o", "/dev/null", "http://127.0.0.1:4000/health"},
+					Test: []string{"CMD", "curl", "-sSfL", "--head", "-o", "/dev/null",
+						"http://127.0.0.1:4000/health",
+					},
 					Interval:    10 * time.Second,
 					Timeout:     2 * time.Second,
 					Retries:     3,
@@ -570,7 +586,9 @@ EOF
 				Env:          env,
 				ExposedPorts: nat.PortSet{"9999/tcp": {}},
 				Healthcheck: &container.HealthConfig{
-					Test:     []string{"CMD", "wget", "--no-verbose", "--tries=1", "--spider", "http://127.0.0.1:9999/health"},
+					Test: []string{"CMD", "wget", "--no-verbose", "--tries=1", "--spider",
+						"http://127.0.0.1:9999/health",
+					},
 					Interval: 10 * time.Second,
 					Timeout:  2 * time.Second,
 					Retries:  3,
@@ -663,8 +681,10 @@ EOF
 				},
 				ExposedPorts: nat.PortSet{"4000/tcp": {}},
 				Healthcheck: &container.HealthConfig{
-					Test: []string{"CMD", "curl", "-sSfL", "--head", "-o", "/dev/null", "-H", "Authorization: Bearer " + utils.Config.Auth.AnonKey,
-						fmt.Sprintf("http://127.0.0.1:4000/api/tenants/%s/health", utils.Config.Realtime.TenantId),
+					// Podman splits command by spaces unless it's quoted, but curl header can't be quoted.
+					Test: []string{"CMD", "curl", "-sSfL", "--head", "-o", "/dev/null",
+						"-H", "Host:" + utils.Config.Realtime.TenantId,
+						"http://127.0.0.1:4000/api/ping",
 					},
 					Interval: 10 * time.Second,
 					Timeout:  2 * time.Second,
@@ -752,8 +772,10 @@ EOF
 					"UPLOAD_FILE_SIZE_LIMIT_STANDARD=5242880000",
 				},
 				Healthcheck: &container.HealthConfig{
-					// For some reason, 127.0.0.1 resolves to IPv6 address on GitPod which breaks healthcheck.
-					Test:     []string{"CMD", "wget", "--no-verbose", "--tries=1", "--spider", "http://127.0.0.1:5000/status"},
+					// For some reason, localhost resolves to IPv6 address on GitPod which breaks healthcheck.
+					Test: []string{"CMD", "wget", "--no-verbose", "--tries=1", "--spider",
+						"http://127.0.0.1:5000/status",
+					},
 					Interval: 10 * time.Second,
 					Timeout:  2 * time.Second,
 					Retries:  3,
@@ -837,7 +859,7 @@ EOF
 					"PG_META_DB_PASSWORD=" + dbConfig.Password,
 				},
 				Healthcheck: &container.HealthConfig{
-					Test:     []string{"CMD", "node", "-e", "fetch('http://127.0.0.1:8080/health').then((r) => {if (r.status !== 200) throw new Error(r.status)})"},
+					Test:     []string{"CMD", "node", `--eval='fetch("http://127.0.0.1:8080/health").then((r) => {if (r.status !== 200) throw new Error(r.status)})'`},
 					Interval: 10 * time.Second,
 					Timeout:  2 * time.Second,
 					Retries:  3,
@@ -882,7 +904,7 @@ EOF
 					"HOSTNAME=0.0.0.0",
 				},
 				Healthcheck: &container.HealthConfig{
-					Test:     []string{"CMD", "node", "-e", "fetch('http://127.0.0.1:3000/api/profile', (r) => {if (r.statusCode !== 200) throw new Error(r.statusCode)})"},
+					Test:     []string{"CMD", "node", `--eval='fetch("http://127.0.0.1:3000/api/profile", (r) => {if (r.statusCode !== 200) throw new Error(r.statusCode)})'`},
 					Interval: 10 * time.Second,
 					Timeout:  2 * time.Second,
 					Retries:  3,
@@ -907,34 +929,64 @@ EOF
 	}
 
 	// Start pooler.
-	if utils.Config.Db.Pooler.Enabled && !isContainerExcluded(utils.PgbouncerImage, excluded) {
+	if utils.Config.Db.Pooler.Enabled && !isContainerExcluded(utils.SupavisorImage, excluded) {
+		portSession := uint16(5432)
+		portTransaction := uint16(6543)
+		dockerPort := portTransaction
+		if utils.Config.Db.Pooler.PoolMode == utils.SessionMode {
+			dockerPort = portSession
+		}
+		// Create pooler tenant
+		var poolerTenantBuf bytes.Buffer
+		if err := poolerTenantTemplate.Execute(&poolerTenantBuf, poolerTenant{
+			DbHost:            dbConfig.Host,
+			DbPort:            dbConfig.Port,
+			DbDatabase:        dbConfig.Database,
+			DbPassword:        dbConfig.Password,
+			ExternalId:        utils.Config.Db.Pooler.TenantId,
+			ModeType:          utils.Config.Db.Pooler.PoolMode,
+			DefaultMaxClients: utils.Config.Db.Pooler.MaxClientConn,
+			DefaultPoolSize:   utils.Config.Db.Pooler.DefaultPoolSize,
+		}); err != nil {
+			return errors.Errorf("failed to exec template: %w", err)
+		}
 		if _, err := utils.DockerStart(
 			ctx,
 			container.Config{
-				Image: utils.PgbouncerImage,
+				Image: utils.SupavisorImage,
 				Env: []string{
-					"POSTGRESQL_HOST=" + dbConfig.Host,
-					fmt.Sprintf("POSTGRESQL_PORT=%d", dbConfig.Port),
-					"POSTGRESQL_USERNAME=pgbouncer",
-					"POSTGRESQL_PASSWORD=" + dbConfig.Password,
-					"POSTGRESQL_DATABASE=" + dbConfig.Database,
-					"PGBOUNCER_AUTH_USER=pgbouncer",
-					"PGBOUNCER_AUTH_QUERY=SELECT * FROM pgbouncer.get_auth($1)",
-					fmt.Sprintf("PGBOUNCER_POOL_MODE=%s", utils.Config.Db.Pooler.PoolMode),
-					fmt.Sprintf("PGBOUNCER_DEFAULT_POOL_SIZE=%d", utils.Config.Db.Pooler.DefaultPoolSize),
-					fmt.Sprintf("PGBOUNCER_MAX_CLIENT_CONN=%d", utils.Config.Db.Pooler.MaxClientConn),
-					// Default platform config: https://github.com/supabase/postgres/blob/develop/ansible/files/pgbouncer_config/pgbouncer.ini.j2
-					"PGBOUNCER_IGNORE_STARTUP_PARAMETERS=extra_float_digits",
+					"PORT=4000",
+					fmt.Sprintf("PROXY_PORT_SESSION=%d", portSession),
+					fmt.Sprintf("PROXY_PORT_TRANSACTION=%d", portTransaction),
+					fmt.Sprintf("DATABASE_URL=ecto://%s:%s@%s:%d/%s", dbConfig.User, dbConfig.Password, dbConfig.Host, dbConfig.Port, dbConfig.Database),
+					"CLUSTER_POSTGRES=true",
+					"SECRET_KEY_BASE=" + utils.Config.Db.Pooler.SecretKeyBase,
+					"VAULT_ENC_KEY=" + utils.Config.Db.Pooler.EncryptionKey,
+					"API_JWT_SECRET=" + utils.Config.Auth.JwtSecret,
+					"METRICS_JWT_SECRET=" + utils.Config.Auth.JwtSecret,
+					"REGION=local",
+					"ERL_AFLAGS=-proto_dist inet_tcp",
+				},
+				Cmd: []string{
+					"/bin/sh", "-c",
+					fmt.Sprintf("/app/bin/migrate && /app/bin/supavisor eval '%s' && /app/bin/server", poolerTenantBuf.String()),
+				},
+				ExposedPorts: nat.PortSet{
+					"4000/tcp": {},
+					nat.Port(fmt.Sprintf("%d/tcp", portSession)):     {},
+					nat.Port(fmt.Sprintf("%d/tcp", portTransaction)): {},
 				},
 				Healthcheck: &container.HealthConfig{
-					Test:     []string{"CMD", "bash", "-c", "printf \\0 > /dev/tcp/127.0.0.1/6432"},
+					Test:     []string{"CMD", "curl", "-sSfL", "--head", "-o", "/dev/null", "http://127.0.0.1:4000/api/health"},
 					Interval: 10 * time.Second,
 					Timeout:  2 * time.Second,
 					Retries:  3,
 				},
 			},
 			container.HostConfig{
-				PortBindings:  nat.PortMap{"6432/tcp": []nat.PortBinding{{HostPort: strconv.FormatUint(uint64(utils.Config.Db.Pooler.Port), 10)}}},
+				PortBindings: nat.PortMap{nat.Port(fmt.Sprintf("%d/tcp", dockerPort)): []nat.PortBinding{{
+					HostPort: strconv.FormatUint(uint64(utils.Config.Db.Pooler.Port), 10)},
+				}},
 				RestartPolicy: container.RestartPolicy{Name: "always"},
 			},
 			network.NetworkingConfig{
diff --git a/internal/start/templates/pooler.exs b/internal/start/templates/pooler.exs
new file mode 100644
index 00000000..0178fa22
--- /dev/null
+++ b/internal/start/templates/pooler.exs
@@ -0,0 +1,28 @@
+Application.ensure_all_started(:supavisor)
+
+{:ok, version} =
+  case Supavisor.Repo.query!("select version()") do
+    %{rows: [[ver]]} -> Supavisor.Helpers.parse_pg_version(ver)
+    _ -> nil
+  end
+
+params = %{
+  "external_id" => "{{ .ExternalId }}",
+  "db_host" => "{{ .DbHost }}",
+  "db_port" => {{ .DbPort }},
+  "db_database" => "{{ .DbDatabase }}",
+  "require_user" => false,
+  "auth_query" => "SELECT * FROM pgbouncer.get_auth($1)",
+  "default_max_clients" => {{ .DefaultMaxClients }},
+  "default_pool_size" => {{ .DefaultPoolSize }},
+  "default_parameter_status" => %{"server_version" => version},
+  "users" => [%{
+    "db_user" => "pgbouncer",
+    "db_password" => "{{ .DbPassword }}",
+    "mode_type" => "{{ .ModeType }}",
+    "pool_size" => {{ .DefaultPoolSize }},
+    "is_manager" => true
+  }]
+}
+
+{:ok, _} = Supavisor.Tenants.create_tenant(params)
diff --git a/internal/utils/config.go b/internal/utils/config.go
index 66e82871..c0e624da 100644
--- a/internal/utils/config.go
+++ b/internal/utils/config.go
@@ -174,6 +174,11 @@ var Config = config{
 		Image:    Pg15Image,
 		Password: "postgres",
 		RootKey:  "d4dc5b6d4a1d6a10b2c1e76112c994d65db7cec380572cc1839624d4be3fa275",
+		Pooler: pooler{
+			TenantId:      "pooler-dev",
+			EncryptionKey: "12345678901234567890123456789032",
+			SecretKeyBase: "EAx3IQ/wRG1v47ZD4NE4/9RzBI8Jmil3x0yhcW4V2NHBP6c2iPIzwjofi2Ep4HIG",
+		},
 	},
 	Realtime: realtime{
 		IpVersion:       AddressIPv4,
@@ -303,6 +308,9 @@ type (
 		DefaultPoolSize  uint     `toml:"default_pool_size"`
 		MaxClientConn    uint     `toml:"max_client_conn"`
 		ConnectionString string   `toml:"-"`
+		TenantId         string   `toml:"-"`
+		EncryptionKey    string   `toml:"-"`
+		SecretKeyBase    string   `toml:"-"`
 	}
 
 	realtime struct {
diff --git a/internal/utils/misc.go b/internal/utils/misc.go
index f8a2aa8e..88dc46e6 100644
--- a/internal/utils/misc.go
+++ b/internal/utils/misc.go
@@ -38,7 +38,7 @@ const (
 	ImageProxyImage  = "darthsim/imgproxy:v3.8.0"
 	EdgeRuntimeImage = "supabase/edge-runtime:v1.53.3"
 	VectorImage      = "timberio/vector:0.28.1-alpine"
-	PgbouncerImage   = "bitnami/pgbouncer:1.20.1-debian-11-r39"
+	SupavisorImage   = "supabase/supavisor:1.1.56"
 	PgProveImage     = "supabase/pg_prove:3.36"
 	GotrueImage      = "supabase/gotrue:v2.151.0"
 	RealtimeImage    = "supabase/realtime:v2.28.32"
@@ -61,7 +61,7 @@ var ServiceImages = []string{
 	EdgeRuntimeImage,
 	LogflareImage,
 	VectorImage,
-	PgbouncerImage,
+	SupavisorImage,
 }
 
 var JobImages = []string{
@@ -118,6 +118,7 @@ var (
 	ManagedSchemas = append([]string{
 		"_analytics",
 		"_realtime",
+		"_supavisor",
 		"pgbouncer",
 		"pgsodium",
 		"pgtle",
@@ -125,13 +126,14 @@ var (
 		"vault",
 	}, PgSchemas...)
 	InternalSchemas = append([]string{
+		"_analytics",
+		"_realtime",
+		"_supavisor",
 		"auth",
 		"extensions",
 		"pgbouncer",
 		"realtime",
-		"_realtime",
 		"storage",
-		"_analytics",
 		"supabase_functions",
 		"supabase_migrations",
 		// Owned by extensions


Test patch:
diff --git a/internal/db/diff/diff_test.go b/internal/db/diff/diff_test.go
index 6eb1a609..69671be7 100644
--- a/internal/db/diff/diff_test.go
+++ b/internal/db/diff/diff_test.go
@@ -37,6 +37,7 @@ var dbConfig = pgconn.Config{
 var escapedSchemas = []string{
 	`\_analytics`,
 	`\_realtime`,
+	`\_supavisor`,
 	"pgbouncer",
 	"pgsodium",
 	"pgtle",
diff --git a/internal/db/pull/pull_test.go b/internal/db/pull/pull_test.go
index 98fb5efb..37c1ee8f 100644
--- a/internal/db/pull/pull_test.go
+++ b/internal/db/pull/pull_test.go
@@ -32,6 +32,7 @@ var dbConfig = pgconn.Config{
 var escapedSchemas = []string{
 	`\_analytics`,
 	`\_realtime`,
+	`\_supavisor`,
 	"pgbouncer",
 	"pgsodium",
 	"pgtle",
diff --git a/internal/db/reset/reset_test.go b/internal/db/reset/reset_test.go
index f8b5cc7c..239cd9b8 100644
--- a/internal/db/reset/reset_test.go
+++ b/internal/db/reset/reset_test.go
@@ -308,6 +308,7 @@ var escapedSchemas = []string{
 	"public",
 	`\_analytics`,
 	`\_realtime`,
+	`\_supavisor`,
 	"pgbouncer",
 	"pgsodium",
 	"pgtle",