supabase / postgres

Unmodified Postgres with some useful plugins

Postgres + goodies

Unmodified Postgres with some useful plugins. Our goal with this repo is not to modify Postgres, but to provide some of the most common extensions with a one-click install.

Primary Features

• ✅ Postgres 15.
• ✅ Ubuntu 20.04 (Focal Fossa).
• ✅ wal_level = logical and max_replication_slots = 5. Ready for replication.
• ✅ Large Systems Extensions. Enabled for ARM images.

Extensions

Extension	Version	Description
Postgres contrib modules	-	Because everyone should enable pg_stat_statements.
PostGIS	3.3.2	Postgres' most popular extension - support for geographic objects.
pgRouting	v3.4.1	Extension of PostGIS - provides geospatial routing functionalities.
pgTAP	v1.2.0	Unit Testing for Postgres.
pg_cron	v1.6.2	Run CRON jobs inside Postgres.
pgAudit	1.7.0	Generate highly compliant audit logs.
pgjwt	commit	Generate JSON Web Tokens (JWT) in Postgres.
pgsql-http	1.5.0	HTTP client for Postgres.
plpgsql_check	2.2.3	Linter tool for PL/pgSQL.
pg-safeupdate	1.4	Protect your data from accidental updates or deletes.
wal2json	commit	JSON output plugin for logical replication decoding.
PL/Java	1.6.4	Write in Java functions in Postgres.
plv8	commit	Write in Javascript functions in Postgres.
pg_plan_filter	commit	Only allow statements that fulfill set criteria to be executed.
pg_net	v0.6.1	Expose the SQL interface for async networking.
pg_repack	ver_1.5.0	Tool to remove bloat from tables and indexes
rum	1.3.13	An alternative to the GIN index.
pg_hashids	commit	Generate unique identifiers from numbers.
pgsodium	3.1.0	Modern encryption API using libsodium.
pg_stat_monitor	1.0.1	Query Performance Monitoring Tool for PostgreSQL
pgvector	v0.4.0	Open-source vector similarity search for Postgres

Can't find your favorite extension? Suggest for it to be added into future releases here!

Enhanced Security

This is only available for our AWS EC2/ DO Droplet images

Aside from having ufw,fail2ban, and unattended-upgrades installed, we also have the following enhancements in place:

Enhancement	Description
fail2ban filter for PostgreSQL access	Monitors for brute force attempts over at port 5432.
fail2ban filter for PgBouncer access	Monitors for brute force attempts over at port 6543.

Additional Goodies

This is only available for our AWS EC2/ DO Droplet images

Goodie	Version	Description
PgBouncer	1.16.1	Set up Connection Pooling.
PostgREST	v10.1.1	Instantly transform your database into an RESTful API.
WAL-G	v2.0.1	Tool for physical database backup and recovery.

Install

See all installation instructions in the repo wiki.

Marketplace Images

	Postgres & Extensions	PgBouncer	PostgREST	WAL-G
Supabase Postgres	✔️	❌	❌	✔️
Supabase Postgres: PgBouncer Bundle	✔️	✔️	❌	✔️
Supabase Postgres: PostgREST Bundle	✔️	❌	✔️	✔️
Supabase Postgres: Complete Bundle	✔️	✔️	✔️	✔️

Availability

	AWS ARM	AWS x86	Digital Ocean x86
Supabase Postgres	Coming Soon	Coming Soon	Coming Soon
Supabase Postgres: PgBouncer Bundle	Coming Soon	Coming Soon	Coming Soon
Supabase Postgres: PostgREST Bundle	Coming Soon	Coming Soon	Coming Soon
Supabase Postgres: Complete Bundle	Coming Soon	Coming Soon	Coming Soon

Quick Build

$ time packer build -timestamp-ui \
  --var "aws_access_key=<insert aws access key>" \
  --var "aws_secret_key=<insert aws secret key>" \
  --var "ami_regions=<insert desired regions>" \
  amazon-arm.json

Motivation

• Make it fast and simple to get started with Postgres.
• Show off a few of Postgres' most exciting features.
• This is the same build we offer at Supabase.

Roadmap

• Support for more images
• Vote for more plugins/extensions
• Open a github issue if you have a feature request

License

The PostgreSQL License. We realize that licensing is tricky since we are bundling all the various plugins. If we have infringed on any license, let us know and we will make the necessary changes (or remove that extension from this repo).

Sponsors

We are building the features of Firebase using enterprise-grade, open source products. We support existing communities wherever possible, and if the products don’t exist we build them and open source them ourselves.

Experimental Nix Packaging of resources

There is a /nix folder in this repo, plus a flake.nix and flake.lock that facilitate using the Nix package management system to package supabase/postgres, and all of our extensions and wrappers. A user will need nix installed on their machine. As of 4/1/2024 the package set only builds on target machines (x86_64-linux and aarch64-linux), however work is under way to also support building and using directly on aarch64-darwin (macOs). As of 4/1/2024, versions of packages and extensions are synced from /ansible/vars.yml via a utility that can be run by executing nix run .#sync-exts-versions (you must have nix installed and be on the supported x86_64-linux and aarch64-linux for this command to work). The short term goal is to sync these versions as they are updated by our infrastructure and postgres teams, then to see the nix packaged versions build successfully in parallel over time, along with tests of the nix packaged versions passing.

The supabase/postgres repo will continue to source it's dependencies from ansible for the short term, while we stabilize this nix build.

Forthcoming PR's will include: integrating the nix work into our ansible/packer builds, building natively on aarch64-darwin (macOs), more testing

postgres's Issues

Clean up wal-g installation

RIght now /root/go directory occupies 1.1 GB which reduces the spaces we have for postgres. After we make the wal-g binary, we should clean this up

Add pgsql-http extension

https://github.com/pramsey/pgsql-http/

We may be doing some http requests for our Auth system, so if we are doing a new release soon, we should sneak this one in too. There are a number of use-cases that we can use this for

Automate our build with Github Actions

Since we are going to need to build this often, it is a good candidate to automate with Continuous Integration.

Possible steps:

• Do all work on develop
• Once the code is ready, merge to master
• Any commits to master trigger an Action: https://github.com/features/actions

RFC: new extensions

Possible new extensions:

• is_jsonb_valid: supabase/supabase#679 (comment)
• IP4R: supabase/supabase#679 (comment)
• https://github.com/postgrespro/rum RUM provided a enhanced GIN Index for common search function
• https://github.com/amutu/zhparser zhparser is a PostgreSQL extension for full-text search of Chinese language
• pgRouting supabase/supabase#679 (comment)
• https://github.com/iCyberon/pg_hashids for obscuring ids
• supascript: https://github.com/burggraf/SupaScript

My current thoughts:

• We should do pgRouting now, since we want to be useful for mapping services in the future
• we should add a bunch of extensions around text search + dictionaries - great differentiator
• supascript could be useful

Add support for pgRouting

Feature request

• Add the pgRouting extension https://github.com/pgRouting/pgrouting

Rationale

• To use in combination with PostGIST

Install pg_plan_filter

We have a reasonable threshold on the query cost

Investigate the use of templates

Question: can we use this for more than just languages? If so, can we enable some of our plugins without polluting the public schema?

Would love a good investigation into templates, what they do, and how they work. Once we have all the details I think it's worth a blog post too.

Remove readonly role

I have been thinking about the readonly role. There is very little benefit offered by adding this role for the additional surface area introduced.

We already make this simple in Supabase dashboard, so let's remove it from the default database setup.

Set up with Layer CI - https://show.layerci.com

Same as this issue: supabase/realtime#29

I think it makes more sense to start with Postgres only.

1. Log in
2. click "Submit project"
3. Post title: "Postgres by Supabase"

It makes sense to just copy the config from one of the other projects. Click the project, then in the popup switch to the "Configuration" tab, copy it and use it for this.

Upgrade to postgres 13

reconfigure fail2ban to prevent postgres password bruteforce

to prevent brute forcing of weak postgres passwords, we already have fairly strict password requirements but this would be a strong addition

Add features for DB admins

Feature request

Now that our SQL editor is becoming more full featured, I think it will be necessary for DB admins to put restrictions on the database to prevent beginner errors. This is to start a list of possible features that DB admins will require.

• safeupdate: https://github.com/eradman/pg-safeupdate

While we're at it, we can add some other requested extensions and anything planning to use in our features:

• pg_cron comment

Set up max_replication_slots

@awalias I think you did this right? If yes, I can update the readme with a tick and then push to the App

Upgrade supautils to v1.1.1

• Bump version from 1.1.0 to 1.1.1
• Move supautils from shared_preload_libraries to session_preload_libraries

CI/CD pipeline for Postgres AMIs

Automatically build and deploy to staging env and run a smoke test suite against it.

Optimising build time

With the introduction of plv8, build times for new images have gone up to unfavourable timings. Biggest factor thus far is RAM allocated to the image upon building.

Digital Ocean

• Increasing RAM at build time is not ideal as Digital Ocean plans are tagged to both RAM and disk space.
• Going for a plan with high RAM equates to higher disk space as well.
• We would be unable to scale down the image afterwards

Possible Solutions

• Build locally then deploy. (Perhaps with something like Vagrant)
• Build on top of an existing image with v8 already installed.
• Build on top of previous version.

AWS

• It is possible to increase RAM during build time and scale it accordingly in both directions for instances that would be produced from the resultant AMI.

Docker

• Not that of a worry as building it is done locally first then eventually deployed on to Docker Hub.

pg_net extension availability within docker image

Discussed in supabase/supabase#3435

^{Originally posted by bhvngt October 6, 2021}
Do you have plans to add pg_net extension as part of supabase postgres docker image? It does not seem to be available based on the extensions listed on the supabase/postgres mage page.

SSL enabled by default

Feature request

Is your feature request related to a problem? Please describe.

I'm deploying to a test server on digitalocean and noticed SSL isn't enabled by default.

Describe the solution you'd like

Perhaps there could be a "setup-wizard" to enable SSL or if it can be done since the beginning better

Update the wiki docs with the new Digital Ocean instructions

Supabase Postgres v0.13.0 Checklist

Centralised Checklist for upcoming features/ changes to be implemented in the next version

• #23 Remove readonly role
• #24 Add pgjwt extension
• #25 Add pgsql-http extension
• #26 Add plpgsql_check extension
• pljava: significant demand as well from #5 (comment).
• wal-g (for PITR recovery)
• aws sdk (for PITR recovery, tbc)
• daemontools (for PITR recovery)

Afterwards

• Upload to AWS Marketplace
• Upload to DO Marketplace
• Update Docker image
• Update everything else from v0.12.0

Supbase postgres ssl root certificate relies on legacy Common Name field

Bug report

The SSL root cert provided on by supabase.co (https://app.supabase.io/project//settings/database) relies on legacy Common Name field which is deprecated in the RFC 2818

Describe the bug

using golang 1.6.n SSL connection to supabase DB fails
A clear and concise description of what the bug is.
Please have a look at
rancher/rke2/issues/775

ps. I wasn't sure where exactly to report this

Digital Ocean Build Does not work

Hi Just spinned up your Postgres image. It does not work. Commands cannot be executed as root. postgresql is not a known command. when running from /bin directly error is shown that no config is found.

Remove build dependencies from all images

I was able to remove ~1.4GB worth of build dependencies from one of our AMIs

Add pgTAP

I forgot a useful plugin - https://pgtap.org/

I think if we can sneak this in before many of our users sign up then we can offer a nice interface for them to run test suites on their database. I haven't seen that anywhere else, would be quite cool

plv8 v3.0.0 fails to build for ARM instances

Using these steps:

1. Ensure PostgreSQL is installed beforehand.
2. apt install build-essential ca-certificates curl git-core gpp cpp pkg-config apt-transport-https cmake libc++-dev libc++abi-dev libc++1 libglib2.0-dev libtinfo5 libc++abi1 ninja-build python
3. git clone https://github.com/plv8/plv8.git
4. cd plv8 && git checkout v3.0.0
5. make
6. make install

Step 6) make eventually returns an error clang++: error: unknown argument: '-ftrivial-auto-var-init=pattern'

Instance used

• AWS r6g.2xlarge
• Ubuntu 20.04 LTS

Analysis

Changes made to the Makefile in this commit could have most likely affected the build configuration for ARM instances.

Temporary Solution

Reverted back to 3.0.alpha by building from a commit prior to the above commit. This worked as per normal.

Upgrade PgBouncer to 1.16.0

PgBouncer 1.16.0 was released on 9 August 2021.

Set default encoding and collation to UTF-8

Feature request

Consider something along the lines of

update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template0';
update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template1';

from https://www.postgresql.org/docs/9.5/manage-ag-templatedbs.html

Describe the solution you'd like

This makes some sensible defaults for the create database command. For some reason this fixes acute problems on Docker for Windows Desktop hosts along with adding this to many different containerized applications, including probably yours:

ENV LANGUAGE=en_US.UTF-8
ENV LANG=en_US.UTF-8
ENV LC_ALL=en_US.UTF-8

Describe alternatives you've considered

FROM supabase/postgres:0.13.0
COPY 99-database-customizations.sql /docker-entrypoint-initdb.d/

99-database-customizations.sql:

update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template0';
update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template1';

Install supautils in the image instead of in the worker

Supautils needs to be part of the image, so that we can track which version of supautils is installed in which postgres image

ARM Docker image

Feature request

Build & publish ARM64 version of the Docker image.

Is your feature request related to a problem? Please describe.

AMD64 images have terrible performance on M1 Macs (even M1 Pro/M1 Max)

Describe the solution you'd like

It would be cool to build & publish linux/arm64 version of the Docker image to Docker Hub, it would help a lot with local development on M1 Macs.

Describe alternatives you've considered

Running existing linux/amd64 image.

Additional context

sysbench CPU benchmark results for linux/amd64 version of the base postgres image:

root@c0af6e852479:/# sysbench cpu --threads=2 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 2
Initializing random number generator from current time


Prime numbers limit: 10000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:   360.03

General statistics:
    total time:                          10.0029s
    total number of events:              3602

Latency (ms):
         min:                                    5.33
         avg:                                    5.55
         max:                                   11.34
         95th percentile:                        5.67
         sum:                                19994.19

Threads fairness:
    events (avg/stddev):           1801.0000/0.00
    execution time (avg/stddev):   9.9971/0.00

sysbench CPU benchmark results for linux/arm64 version of the base postgres image:

root@3cd6496ebf2a:/# sysbench cpu --threads=2 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 2
Initializing random number generator from current time


Prime numbers limit: 10000

Initializing worker threads...

Threads started!

CPU speed:
    events per second: 21270.60

General statistics:
    total time:                          10.0001s
    total number of events:              212719

Latency (ms):
         min:                                    0.09
         avg:                                    0.09
         max:                                    2.68
         95th percentile:                        0.10
         sum:                                19970.88

Threads fairness:
    events (avg/stddev):           106359.5000/5.50
    execution time (avg/stddev):   9.9854/0.00

60x faster!

Set up plv8

https://github.com/plv8/plv8

• Docker
• Packer

Set up testing suites

At the moment, testing the database to ensure that all extensions and settings are working is done manually at the end of each new build. Perhaps I could make use of a combination of psql scripts and pgTAP to automate this. This is especially so since we have more features than last time.

Install postgis_sfcgal extension

Currently missing in current image in #44. When building PostGIS, additional steps are required to enable this extension. Refer to point 2. here.

Docker Admin Cleanup

• Sweep through and remove any signs of port 6543.
• Update documentation over at Docker Hub.
• Spruce up the wiki page.

Compile postgres with LSE enabled

So, if you’re running PostgreSQL on graviton2 instance, make sure you’ve binaries compiled with LSE support

http://akorotkov.github.io/blog/2021/04/30/arm/

currently the ansible role just does apt-get and the ubuntu binaries dont have LSE turned on..we might have to migrate away from the role to enable this which is a shame

Set up Python3

https://www.postgresql.org/docs/current/plpython-python23.html

Use python3: plpython3u

• Docker
• Packer

Support for more images

This issue is for tracking requests/demand for more images. Give a thumbs up if you want an image built.

Please refrain from adding comments unless you are adding an image which isn't already listed.

Set up readonly role

This role provides the user read only access to the public schema

• Docker
• Packer

Implementation Notes

• For the public schema under the database postgres, all roles have been stripped of access aside from the superuser postgres.
• A role called public_readonly has been created which has readonly access to the public schema.
• This is only a role, not a user. Future users can be granted this role to inherit the readonly privilege:

CREATE USER sample_user WITH ENCRYPTED PASSWORD 'sample_password';
GRANT public_readonly TO sample_user;

Further hardening of build

This is to ensure that we are above and beyond in being compliant with the requirements of the AWS AMI and Digital Ocean marketplace.

Checklist

• Comply to Digital Ocean's security standards.
• Comply to AWS's security standards.
• Install Fail2ban. [Server][Additional]
• Install unattended-upgrades. [Server][Additional]
• Add in pgAudit [DB][Additional]

Additional AMI "bundles"

Context

We'd like to build a few different bundles for: a) the AWS marketplace and b) internal usage.

Bundles

We would like to produce AMI bundles

• Postgres -> this one we already have
• Postgres + PgBouncer
• Postgres + PostgREST
• Postgres + PgBouncer + PostgREST

Marketplaces

The top priority is to produce these images for the AWS marketplace. We can target other marketplaces (DO, GCP etc) in a separate issue.

Add plpgsql_check extension

There is significant demand for the plpgsql_check extension:
https://github.com/supabase/postgres/issues/5#issuecomment-636765436

plpgsql_check is for linting and validating SQL code quality. It looks like it is well maintained and is supported on PG12.

Set default password encryption to `scram-sha-256`

reference guide

todo

• Change from md5 to scram-sha-256 on this line of postgresql.conf.
• Edit all md5 on this file to scram-sha-256.
• Change from md5 to scram-sha-256 on this line of pgbouncer.ini.

Use Dockerfile to build the image

Hi. Usually most Docker images are built using a Dockerfile. It would be nice if either -

• The supabase/postgres image could also be built via a Dockerfile, thereby simplifying the build process

or

• The readme could be updated to educate users like me about the reason for choosing ansible over a Dockerfile.

Clean up build dependencies

Could be an easy win in reducing bloat in both our cloud and docker images. Also, some when updated could cause clashes in configurations (in the case of libpq-dev clashing with pg_config).

Upgrade Postgres to 13.4

Postgres 13.4 was released on 12 August 2021

Add pgjwt extension

https://github.com/michelp/pgjwt/blob/master/pgjwt--0.1.0.sql

We will be using this extension for Auth.

Supabase Postgres v0.14.0 Checklist

Centralised Checklist for upcoming features/ changes to be implemented in the next version. For any further feature requests, just comment below!

• #28 Add pgcron
• #31 Remove plpython
• #30 Set default encoding and collation to UTF-8 for docker image
• #29 Add pg-safeupdate

Afterwards

• Upload to AWS Marketplace [in progress]
• Release ARM build to AWS Marketplace [in progress]
• Upload to DO Marketplace
• Update Docker image
• Update everything else from v0.13.0

The expected release will be the end of 2020 to accommodate any more feature requests.

Optimise Docker image

At the moment, we are building the Docker image differently from the cloud images via a Dockerfile. This is starting to be a hassle to update separately every time. Also, the compressed size of the latest image is 723.42 MB , which is getting out of hand. Downloaded image stands at 1.76 GB.

---

Attempt the following :

• Build the image through Ansible and make use of existing task files.
• Reduce and minimise final image size. (somewhat)
  • Remove packages that are only used for building.
  • Make sure downloaded files are also cleaned up at the end.

Change default logging level

It will be useful to have more detailed logging

Explore the following parameters

• log_statement
• log_min_messages
• log_connections

Adding multiple regions for Digital Ocean build

This is done by using the parameter snapshot_regions over region in Packer.

Support for data directory on attached volume

DO wizard gives the option to attach a volume when provisioning the droplet,

I would like to have postgres use this as the default data directory:

• either automatically on startup
• or give instructions in 'getting started' for how to switch the data directory to my attached volume

I imagine the same/similar thing is possible on AWS