Self Hosted Supabase with External Postgresql

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 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

SHOW config_file;

You'll get the following output:

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

Open the file, then change the line:

 listen_addresses = 'localhost'

To

 listen_addresses = '*'

Get the pg_hba.conf file

SHOW hba_file;

You'll get the following output:

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

Open this file and add the following lines:

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:

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:

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

Install docker and docker-compose. You'll also need to install Git.

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

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

Then change directory into the pgjwt directory, then:

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

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.

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.

Replace the values in these files:

 .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:

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:

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:

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.

Comments

[Anton Melser]

I had a look at the supabase/postgres image and there seems to be a LOT of bespoke stuff going on. I wonder how easy it is to try and upgrade to secure versions of postgres? 15.1 seems to have a few reasonably annoying bugs and security issues, and having at least 15.6 would be ideal...

[D3nai Labs]

hello at what time those information will be create on externam db

`CREATE ROLE authenticator LOGIN NOINHERIT NOCREATEDB NOCREATEROLE NOSUPERUSER;
CREATE ROLE anon NOLOGIN;
-- CREATE ROLE anonymous NOLOGIN;
CREATE ROLE webuser NOLOGIN;

ALTER USER authenticator WITH PASSWORD '{{ vault_supabase_db_password }}';

--ALTER USER pgbouncer WITH PASSWORD '{{ vault_supabase_db_password }}';
--ALTER USER supabase_auth_admin WITH PASSWORD '{{ vault_supabase_db_password }}';
--ALTER USER supabase_functions_admin WITH PASSWORD '{{ vault_supabase_db_password }}';
--ALTER USER supabase_storage_admin WITH PASSWORD '{{ vault_supabase_db_password }}';`

[Asim Kaghzi]

docker/volumes/db/init has data.sql which is zero bytes, any ideas?

[lmish]

Thank you man! So helpful.