DEV Community

Arthur
Arthur

Posted on

Self Hosted Supabase with External Postgresql

#supabase #postgres

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;
Enter fullscreen mode Exit fullscreen mode

You'll get the following output:

               config_file               
-----------------------------------------
 /etc/postgresql/12/main/postgresql.conf
(1 row)
Enter fullscreen mode Exit fullscreen mode

Open the file, then change the line:

 listen_addresses = 'localhost'
Enter fullscreen mode Exit fullscreen mode

To

 listen_addresses = '*'
Enter fullscreen mode Exit fullscreen mode

Get the pg_hba.conf file

SHOW hba_file;
Enter fullscreen mode Exit fullscreen mode

You'll get the following output:

              hba_file               
-------------------------------------
 /etc/postgresql/12/main/pg_hba.conf
(1 row)
Enter fullscreen mode Exit fullscreen mode

Open this file and add the following lines:

host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Then change directory into the pgjwt directory, then:


sudo make install
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Setting up secrets

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

cp .env.example .env
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

And you should be good to go.

Top comments (4)

Collapse
 
antonofthewoods profile image
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...

Collapse
 
d3nai_labs_d2f079cfe45a3b profile image
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 }}';`

Collapse
 
akaghzi profile image
Asim Kaghzi •

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

Collapse
 
lmish profile image
lmish •

Thank you man! So helpful.

Read next

dbvismarketing profile image

PostgreSQL and DbVisualizer for Better Inventory Management

DbVisualizer -

travisv profile image

Stop Supabase Projects From Being Paused

Travis -

developpeurtaf profile image

Associer une Base de Données PostgreSQL à un Projet Laravel 11 sous Linux et Windows

Fabrice -

cezz profile image

Why should you upgrade your PostgreSQL today?

Cezzaine Zaher -