Self Hosted Supabase with External Postgresql
Tags: supabase, postgres
Published: 2023-02-28 18:24:35

**Supabase** is a self-hosted open source application back-end with a cloud offering (hosted by the developers of the platform). The platform is considered an open source firebase alternative by many. Whether or not that statement is true, is a discussion for another day. 

What really stands out mostly for me, is the on the fly API & API documentation and most importantly simplified `Row Level Security` and `realtime` notifications are by far the biggest selling points.

## Requirements

* Linux (Debian)
* Docker
* Docker-compose
* postgres
* make
* git

## Dependencies

Install [postgres](https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwj_4LD1-aj5AhWJdcAKHfbWAMEQFnoECAoQAQ&url=https%3A%2F%2Fwww.postgresql.org%2Fdownload%2F&usg=AOvVaw1s9I9j-sVmmwVRYb_trcix) and configure it to allow tcp connections from networks other than localhost.

You'll need to edit the postgres.conf and pg_hda.conf files.

Enter your psql terminal:

Get the `postgres.conf` file

```sql
SHOW config_file;
```

You'll get the following output:

```sql
               config_file               
-----------------------------------------
 /etc/postgresql/12/main/postgresql.conf
(1 row)
```

Open the file, then change the line:

```conf
 listen_addresses = 'localhost'
```
To 

```conf
 listen_addresses = '*'
```

Get the `pg_hba.conf` file

```sql
SHOW hba_file;
```

You'll get the following output:

```bash
              hba_file               
-------------------------------------
 /etc/postgresql/12/main/pg_hba.conf
(1 row)
```

Open this file and add the following lines:

```conf
host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5
```

Then restart the postgres daemon, for the above changes to take, effect.
The default postgres user **postgres** does not have a password, now would be the right time to create a user with password, that will be used by supabase, and grant that user superuser with login, create db, create role, bypass rls roles


To install custom postgres extensions, the dev postgresl packages are required with make. To get these:

```bash

sudo apt install sudo apt install postgresql-server-dev-XX cmake make

```

Where `xx` is the version of your postgresql db. In my case, the above command is:

```bash

sudo apt install sudo apt install postgresql-server-dev-12 cmake make

```

Install [docker](https://docs.docker.com/engine/install/) and [docker-compose](https://docs.docker.com/compose/install/). You'll also need to install [Git](https://docs.docker.com/compose/install/).

Get the pg_jwt source code from github by cloning the project to your local machine:

```bash

git clone https://github.com/michelp/pgjwt

```

Then change directory into the pgjwt directory, then:

```bash

sudo make install

```

The above creates the pgjwt extension which will be installed with postgres **create extension pgjwt**

At this point we now need to get supabase from github; clone the repository t your local machine and cd into the project directory

```bash

git clone https://github.com/supabase/supabase && cd supabase

```
## Setting up secrets

Whilst you can use the defaults provided, it is advisable to set your own secrets.

```bash
cp .env.example .env
```
I recommend you change the default anon key and service keys in the .env file

Use your JWT_SECRET to generate a anon and service API keys using the [JWT Geneartor](https://supabase.com/docs/guides/hosting/overview#api-keys).

Replace the values in these files:

```conf
 .env:
    ANON_KEY - replace with an anon key
    SERVICE_ROLE_KEY - replace with a service key
 volumes/api/kong.yml
    anon - replace with an anon key
    service_role - replace with a service key
```

Having made the above changes; in the `.env` file add credentials to your external postgresql database

POSTGRES_PASSWORD=your-super-secret-and-long-postgres-password

POSTGRES_HOST=host.docker.internal
POSTGRES_DB=postgres
POSTGRES_USER=postgres
POSTGRES_PORT=5432

The `host.docker.internal` on `POSTGRES_HOST` makes it possible for the container to connect to the host machine. Further the `docker-compose.yaml` has been changed ti include the line:

```yaml
extra_hosts:
      - "host.docker.internal:host-gateway"
```

at the end of each service to allow them to connect with the host. And the  depends-on db removed everywhere in the file. The final file wil look as show below:

{% gist https://gist.github.com/arthurkay/5f09b44ff6c88e600abd19f1e05875a7 %}

## Migrations

You are now ready to run database migrations. Migrations are essentially table schemas that supabase will need to initialize and set up everything it needs. These also include postgres extensions; Whilst supabase self hosted, now supports graphql, I will not include its configurations has it  only works with postgres 14, which I have not yet tried.
While in the same supabase project, navigate to:

```bash
cd docker/volumes/db/init
```

In that directory you'll find files that need to be run in their order.

And you should be good to go.