supabase-postgres.git / summary / log / commit / refs
commit cfbb1714dad2535bca42b58f62f6be4edf85a65f
Author: samrose <samuel.rose@gmail.com>
Commit: GitHub <noreply@github.com>
Date: Thu Oct 17 12:02:35 2024 +0000
Sam/15 16 nix package, build and test of 15.8 and 16.3 (#1266)
* feat: build and flake check of pg 16.3 with exts/wrappers
* pg_partman test 15/16 compat
* merge sql interface test
* tests: build test and cache both versions
* chore: run checks individually
* pg 15 and 16 packer/ansible/ghactions (#1268)
* fix: reformat ec2 cleanup commands (#1267)
Co-authored-by: Sam Rose <samuel@supabase.io>
* feat: build and flake check of pg 16.3 with exts/wrappers
* pg_partman test 15/16 compat
* merge sql interface test
* tests: build test and cache both versions
* chore: run checks individually
* feat: realease 15 and 16 to staging
* chore: update versions
* chore: make yq available
* chore: run yq from nix
* chore: more setup for staging AMI
* fix: yq usage
* chore: shell vars
* fix: When --init none is used, only users who can elevate to sudo privileges can run Nix
* fix: no -i
* fix: quote correction
* fix: newline extra quotes
* fix: no need for pg major version on packer
* fix: postgresql_major
* fix: ql
* fix: no ansible args in stage to invocation
* fix: unique val
* fix: adjustments to build scripts
* chore: env var handling
* fix: bump to build
* chore: set up more required vars
* chore: bump var
* feat: pg 16 debug symbols
* feat: matrix pg versions build on testinfra
* feat: matrix on Test Database
* chore: running nix in the right context
* feat: just use existing Dockerfile + pg version
* chore: refer to var
* fix: read name without including quotes
* chore: try format function
* fix: strip quotes from version number
* chore: env var
* fix: pg client
* fix
* fix: try to use psql from our own corresponding pkg
* fix: try psql from ppa
* fix: dbmate per pg version
* build dbmate and then install client
* fix: account for architecture
* chore: limit changes detection migrations/schema.sql
* missing docker compose call
* ore: drop tests while investigating
* test: try on pg15 only
* chore: schema needs update
* chore: now run on all versions in matrix
* test: trying a version of schema per major pg version as there are type diffs
---------
Co-authored-by: Sam Rose <samuel@supabase.io>
Co-authored-by: Oliver Rice <github@oliverrice.com>
* chore: rm dead code
* chore: resolve versions from ansible/vars.yml instead of packer file
create a matrix build for 15 16 (or how ever many versions there are)
* fix: don not lose if statements used for hotfix when needed
* chore: cleanup
---------
Co-authored-by: Sam Rose <samuel@supabase.io>
Co-authored-by: Oliver Rice <github@oliverrice.com>
.github/workflows/ami-release-nix.yml | 65 +-
.../publish-nix-pgupgrade-bin-flake-version.yml | 33 +-
.../workflows/publish-nix-pgupgrade-scripts.yml | 38 +-
.github/workflows/test.yml | 65 +-
.github/workflows/testinfra-nix.yml | 37 +-
Dockerfile-15 | 223 ++++
Dockerfile-16 | 223 ++++
ansible/tasks/stage2-setup-postgres.yml | 14 +-
ansible/vars.yml | 14 +-
common-nix.vars.pkr.hcl | 1 -
docker/nix/build_nix.sh | 5 +-
flake.nix | 38 +-
migrations/Dockerfile.dbmate | 23 +
migrations/docker-compose.yaml | 4 +-
migrations/schema-15.sql | 1064 ++++++++++++++++++++
migrations/schema-16.sql | 1064 ++++++++++++++++++++
migrations/schema.sql | 2 +-
nix/ext/hypopg.nix | 2 +-
nix/ext/pg_backtrace.nix | 2 +-
nix/ext/pg_cron.nix | 2 +-
nix/ext/pg_hashids.nix | 2 +-
nix/ext/pg_net.nix | 2 +-
nix/ext/pg_plan_filter.nix | 2 +-
nix/ext/pg_stat_monitor.nix | 2 +-
nix/ext/pg_tle.nix | 2 +-
nix/ext/pgrouting.nix | 33 +-
nix/ext/pgsodium.nix | 2 +-
nix/ext/pgsql-http.nix | 2 +-
nix/ext/pgvector.nix | 2 +-
nix/ext/plv8.nix | 76 +-
nix/ext/supautils.nix | 2 +-
nix/postgresql/16.nix | 4 +
nix/postgresql/default.nix | 1 +
nix/tests/expected/extensions_sql_interface.out | 17 +-
nix/tests/expected/pg_partman.out | 5 +-
nix/tests/sql/extensions_sql_interface.sql | 12 +
nix/tests/sql/pg_partman.sql | 5 +-
scripts/nix-provision.sh | 7 +
stage2-nix-psql.pkr.hcl | 8 +-
39 files changed, 2957 insertions(+), 148 deletions(-)
diff --git a/.github/workflows/ami-release-nix.yml b/.github/workflows/ami-release-nix.yml
index 194c8de2..643f26fc 100644
--- a/.github/workflows/ami-release-nix.yml
+++ b/.github/workflows/ami-release-nix.yml
@@ -8,12 +8,31 @@ on:
paths:
- '.github/workflows/ami-release-nix.yml'
- 'common-nix.vars.pkr.hcl'
+ - 'ansible/vars.yml'
workflow_dispatch:
jobs:
+ prepare:
+ runs-on: ubuntu-latest
+ outputs:
+ postgres_versions: ${{ steps.set-versions.outputs.postgres_versions }}
+ steps:
+ - name: Checkout Repo
+ uses: actions/checkout@v3
+
+ - uses: DeterminateSystems/nix-installer-action@main
+
+ - name: Set PostgreSQL versions
+ id: set-versions
+ run: |
+ VERSIONS=$(nix run nixpkgs#yq -- '.postgres_major[]' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
+ echo "postgres_versions=$VERSIONS" >> $GITHUB_OUTPUT
+
build:
+ needs: prepare
strategy:
matrix:
+ postgres_version: ${{ fromJson(needs.prepare.outputs.postgres_versions) }}
include:
- runner: arm-runner
arch: arm64
@@ -31,42 +50,55 @@ jobs:
- name: Checkout Repo
uses: actions/checkout@v3
+ - uses: DeterminateSystems/nix-installer-action@main
+
- name: Run checks if triggered manually
if: ${{ github.event_name == 'workflow_dispatch' }}
- # Update `ci.yaml` too if changing constraints.
run: |
- SUFFIX=$(sed -E 's/postgres-version = "[0-9\.]+(.*)"/\1/g' common-nix.vars.pkr.hcl)
+ SUFFIX=$(sudo nix run nixpkgs#yq -- '.postgres_release["postgres${{ matrix.postgres_version }}"]' ansible/vars.yml | sed -E 's/[0-9\.]+(.*)$/\1/')
if [[ -z $SUFFIX ]] ; then
echo "Version must include non-numeric characters if built manually."
exit 1
fi
- # extensions are build in nix prior to this step
- # so we can just use the binaries from the nix store
- # for postgres, extensions and wrappers
+ - name: Set PostgreSQL version environment variable
+ run: echo "POSTGRES_MAJOR_VERSION=${{ matrix.postgres_version }}" >> $GITHUB_ENV
+
+ - name: Generate common-nix.vars.pkr.hcl
+ run: |
+ PG_VERSION=$(sudo nix run nixpkgs#yq -- '.postgres_release["postgres'${{ matrix.postgres_version }}'"]' ansible/vars.yml)
+ PG_VERSION=$(echo $PG_VERSION | tr -d '"') # Remove any surrounding quotes
+ echo 'postgres-version = "'$PG_VERSION'"' > common-nix.vars.pkr.hcl
+ # Ensure there's a newline at the end of the file
+ echo "" >> common-nix.vars.pkr.hcl
- name: Build AMI stage 1
+ env:
+ POSTGRES_MAJOR_VERSION: ${{ env.POSTGRES_MAJOR_VERSION }}
run: |
packer init amazon-arm64-nix.pkr.hcl
GIT_SHA=${{github.sha}}
- packer build -var "git-head-version=${GIT_SHA}" -var "packer-execution-id=${GITHUB_RUN_ID}" -var-file="development-arm.vars.pkr.hcl" -var-file="common-nix.vars.pkr.hcl" -var "ansible_arguments=" amazon-arm64-nix.pkr.hcl
+ packer build -var "git-head-version=${GIT_SHA}" -var "packer-execution-id=${GITHUB_RUN_ID}" -var-file="development-arm.vars.pkr.hcl" -var-file="common-nix.vars.pkr.hcl" -var "ansible_arguments=-e postgresql_major=${POSTGRES_MAJOR_VERSION}" amazon-arm64-nix.pkr.hcl
- name: Build AMI stage 2
+ env:
+ POSTGRES_MAJOR_VERSION: ${{ env.POSTGRES_MAJOR_VERSION }}
run: |
packer init stage2-nix-psql.pkr.hcl
GIT_SHA=${{github.sha}}
- packer build -var "git_sha=${GIT_SHA}" -var "git-head-version=${GIT_SHA}" -var "packer-execution-id=${GITHUB_RUN_ID}" -var-file="development-arm.vars.pkr.hcl" -var-file="common-nix.vars.pkr.hcl" stage2-nix-psql.pkr.hcl
+ POSTGRES_MAJOR_VERSION=${{ env.POSTGRES_MAJOR_VERSION }}
+ packer build -var "git_sha=${GIT_SHA}" -var "git-head-version=${GIT_SHA}" -var "packer-execution-id=${GITHUB_RUN_ID}" -var "postgres_major_version=${POSTGRES_MAJOR_VERSION}" -var-file="development-arm.vars.pkr.hcl" -var-file="common-nix.vars.pkr.hcl" stage2-nix-psql.pkr.hcl
- name: Grab release version
id: process_release_version
run: |
- VERSION=$(sed -e 's/postgres-version = "\(.*\)"/\1/g' common-nix.vars.pkr.hcl)
- echo "version=$VERSION" >> "$GITHUB_OUTPUT"
+ VERSION=$(cat common-nix.vars.pkr.hcl | sed -e 's/postgres-version = "\(.*\)"/\1/g')
+ echo "version=$VERSION" >> $GITHUB_OUTPUT
- name: Create nix flake revision tarball
run: |
GIT_SHA=${{github.sha}}
- MAJOR_VERSION=$(echo "${{ steps.process_release_version.outputs.version }}" | cut -d. -f1)
+ MAJOR_VERSION=${{ env.POSTGRES_MAJOR_VERSION }}
mkdir -p "/tmp/pg_upgrade_bin/${MAJOR_VERSION}"
echo "$GIT_SHA" >> "/tmp/pg_upgrade_bin/${MAJOR_VERSION}/nix_flake_version"
@@ -84,17 +116,13 @@ jobs:
ansible-playbook -i localhost \
-e "ami_release_version=${{ steps.process_release_version.outputs.version }}" \
-e "internal_artifacts_bucket=${{ secrets.ARTIFACTS_BUCKET }}" \
+ -e "postgres_major_version=${{ env.POSTGRES_MAJOR_VERSION }}" \
manifest-playbook.yml
- name: Upload nix flake revision to s3 staging
run: |
aws s3 cp /tmp/pg_binaries.tar.gz s3://${{ secrets.ARTIFACTS_BUCKET }}/upgrades/postgres/supabase-postgres-${{ steps.process_release_version.outputs.version }}/20.04.tar.gz
- #Our self hosted github runner already has permissions to publish images
- #but they're limited to only that;
- #so if we want s3 access we'll need to config credentials with the below steps
- # (which overwrites existing perms) after the ami build
-
- name: configure aws credentials - prod
uses: aws-actions/configure-aws-credentials@v4
with:
@@ -107,6 +135,7 @@ jobs:
ansible-playbook -i localhost \
-e "ami_release_version=${{ steps.process_release_version.outputs.version }}" \
-e "internal_artifacts_bucket=${{ secrets.PROD_ARTIFACTS_BUCKET }}" \
+ -e "postgres_major_version=${{ env.POSTGRES_MAJOR_VERSION }}" \
manifest-playbook.yml
- name: Upload nix flake revision to s3 prod
@@ -130,12 +159,12 @@ jobs:
SLACK_MESSAGE: 'Building Postgres AMI failed'
SLACK_FOOTER: ''
- - name: Cleanup resources on build cancellation
+ - name: Cleanup resources after build
if: ${{ always() }}
run: |
- aws ec2 describe-instances --filters "Name=tag:packerExecutionId,Values=${GITHUB_RUN_ID}" --query "Reservations[].Instances[].InstanceId" --output text | xargs -n 1 -I {} aws ec2 terminate-instances --instance-ids {}
+ aws ec2 describe-instances --filters "Name=tag:packerExecutionId,Values=${GITHUB_RUN_ID}" --query "Reservations[].Instances[].InstanceId" --output text | xargs -r aws ec2 terminate-instances --instance-ids
- name: Cleanup resources on build cancellation
if: ${{ cancelled() }}
run: |
- aws ec2 describe-instances --filters "Name=tag:packerExecutionId,Values=${GITHUB_RUN_ID}" --query "Reservations[].Instances[].InstanceId" --output text | xargs -n 1 -I {} aws ec2 terminate-instances --instance-ids {}
+ aws ec2 describe-instances --filters "Name=tag:packerExecutionId,Values=${GITHUB_RUN_ID}" --query "Reservations[].Instances[].InstanceId" --output text | xargs -r aws ec2 terminate-instances --instance-ids
diff --git a/.github/workflows/publish-nix-pgupgrade-bin-flake-version.yml b/.github/workflows/publish-nix-pgupgrade-bin-flake-version.yml
index 5b985f4b..6160b586 100644
--- a/.github/workflows/publish-nix-pgupgrade-bin-flake-version.yml
+++ b/.github/workflows/publish-nix-pgupgrade-bin-flake-version.yml
@@ -11,8 +11,28 @@ permissions:
id-token: write
jobs:
+ prepare:
+ runs-on: ubuntu-latest
+ outputs:
+ postgres_versions: ${{ steps.set-versions.outputs.postgres_versions }}
+ steps:
+ - name: Checkout Repo
+ uses: actions/checkout@v3
+
+ - uses: DeterminateSystems/nix-installer-action@main
+
+ - name: Set PostgreSQL versions
+ id: set-versions
+ run: |
+ VERSIONS=$(nix run nixpkgs#yq -- '.postgres_major[]' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
+ echo "postgres_versions=$VERSIONS" >> $GITHUB_OUTPUT
+
publish-staging:
+ needs: prepare
runs-on: ubuntu-latest
+ strategy:
+ matrix:
+ postgres_version: ${{ fromJson(needs.prepare.outputs.postgres_versions) }}
steps:
- name: Checkout Repo
@@ -21,7 +41,8 @@ jobs:
- name: Grab release version
id: process_release_version
run: |
- VERSION=$(grep 'postgres-version' common-nix.vars.pkr.hcl | sed -e 's/postgres-version = "\(.*\)"/\1/g')
+ VERSION=$(sudo nix run nixpkgs#yq -- '.postgres_release["postgres'${{ matrix.postgres_version }}'"]' ansible/vars.yml)
+ VERSION=$(echo $PG_VERSION | tr -d '"') # Remove any surrounding quotes
if [[ "${{ inputs.postgresVersion }}" != "" ]]; then
VERSION=${{ inputs.postgresVersion }}
fi
@@ -58,6 +79,10 @@ jobs:
publish-prod:
runs-on: ubuntu-latest
if: github.ref_name == 'develop' || contains( github.ref, 'release' )
+ needs: prepare
+ strategy:
+ matrix:
+ postgres_version: ${{ fromJson(needs.prepare.outputs.postgres_versions) }}
steps:
- name: Checkout Repo
@@ -66,10 +91,8 @@ jobs:
- name: Grab release version
id: process_release_version
run: |
- VERSION=$(grep 'postgres-version' common-nix.vars.pkr.hcl | sed -e 's/postgres-version = "\(.*\)"/\1/g')
- if [[ "${{ inputs.postgresVersion }}" != "" ]]; then
- VERSION=${{ inputs.postgresVersion }}
- fi
+ VERSION=$(sudo nix run nixpkgs#yq -- '.postgres_release["postgres'${{ matrix.postgres_version }}'"]' ansible/vars.yml)
+ VERSION=$(echo $PG_VERSION | tr -d '"') # Remove any surrounding quotes
echo "version=$VERSION" >> "$GITHUB_OUTPUT"
echo "major_version=$(echo $VERSION | cut -d'.' -f1)" >> "$GITHUB_OUTPUT"
diff --git a/.github/workflows/publish-nix-pgupgrade-scripts.yml b/.github/workflows/publish-nix-pgupgrade-scripts.yml
index eb5f7a75..176f57a0 100644
--- a/.github/workflows/publish-nix-pgupgrade-scripts.yml
+++ b/.github/workflows/publish-nix-pgupgrade-scripts.yml
@@ -7,7 +7,6 @@ on:
- release/*
paths:
- '.github/workflows/publish-nix-pgupgrade-scripts.yml'
- - 'common-nix.vars.pkr.hcl'
workflow_dispatch:
inputs:
postgresVersion:
@@ -18,17 +17,40 @@ permissions:
id-token: write
jobs:
+ prepare:
+ runs-on: ubuntu-latest
+ outputs:
+ postgres_versions: ${{ steps.set-versions.outputs.postgres_versions }}
+ steps:
+ - name: Checkout Repo
+ uses: actions/checkout@v3
+
+ - uses: DeterminateSystems/nix-installer-action@main
+
+ - name: Set PostgreSQL versions
+ id: set-versions
+ run: |
+ VERSIONS=$(nix run nixpkgs#yq -- '.postgres_major[]' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
+ echo "postgres_versions=$VERSIONS" >> $GITHUB_OUTPUT
+
publish-staging:
+ needs: prepare
runs-on: ubuntu-latest
+ strategy:
+ matrix:
+ postgres_version: ${{ fromJson(needs.prepare.outputs.postgres_versions) }}
steps:
- name: Checkout Repo
uses: actions/checkout@v3
+ - uses: DeterminateSystems/nix-installer-action@main
+
- name: Grab release version
id: process_release_version
run: |
- VERSION=$(grep 'postgres-version' common-nix.vars.pkr.hcl | sed -e 's/postgres-version = "\(.*\)"/\1/g')
+ VERSION=$(sudo nix run nixpkgs#yq -- '.postgres_release["postgres'${{ matrix.postgres_version }}'"]' ansible/vars.yml)
+ VERSION=$(echo $PG_VERSION | tr -d '"') # Remove any surrounding quotes
if [[ "${{ inputs.postgresVersion }}" != "" ]]; then
VERSION=${{ inputs.postgresVersion }}
fi
@@ -61,9 +83,15 @@ jobs:
SLACK_FOOTER: ''
publish-prod:
+ needs: prepare
runs-on: ubuntu-latest
if: github.ref_name == 'develop' || contains( github.ref, 'release' )
+ strategy:
+ matrix:
+ postgres_version: ${{ fromJson(needs.prepare.outputs.postgres_versions) }}
+
+
steps:
- name: Checkout Repo
uses: actions/checkout@v3
@@ -71,10 +99,8 @@ jobs:
- name: Grab release version
id: process_release_version
run: |
- VERSION=$(grep 'postgres-version' common-nix.vars.pkr.hcl | sed -e 's/postgres-version = "\(.*\)"/\1/g')
- if [[ "${{ inputs.postgresVersion }}" != "" ]]; then
- VERSION=${{ inputs.postgresVersion }}
- fi
+ VERSION=$(sudo nix run nixpkgs#yq -- '.postgres_release["postgres'${{ matrix.postgres_version }}'"]' ansible/vars.yml)
+ VERSION=$(echo $PG_VERSION | tr -d '"') # Remove any surrounding quotes
echo "version=$VERSION" >> "$GITHUB_OUTPUT"
- name: Create a tarball containing pg_upgrade scripts
diff --git a/.github/workflows/test.yml b/.github/workflows/test.yml
index 2be06194..1457eb10 100644
--- a/.github/workflows/test.yml
+++ b/.github/workflows/test.yml
@@ -8,9 +8,27 @@ on:
workflow_dispatch:
jobs:
+ prepare:
+ runs-on: ubuntu-latest
+ outputs:
+ postgres_versions: ${{ steps.set-versions.outputs.postgres_versions }}
+ steps:
+ - name: Checkout Repo
+ uses: actions/checkout@v4
+
+ - uses: DeterminateSystems/nix-installer-action@main
+
+ - name: Set PostgreSQL versions
+ id: set-versions
+ run: |
+ VERSIONS=$(nix run nixpkgs#yq -- '.postgres_major[]' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
+ echo "postgres_versions=$VERSIONS" >> $GITHUB_OUTPUT
+
build:
+ needs: prepare
strategy:
matrix:
+ postgres_version: ${{ fromJson(needs.prepare.outputs.postgres_versions) }}
include:
- runner: [self-hosted, X64]
arch: amd64
@@ -23,14 +41,36 @@ jobs:
POSTGRES_PASSWORD: password
steps:
- uses: actions/checkout@v3
+
+ - uses: DeterminateSystems/nix-installer-action@main
+
+ - name: Set PostgreSQL version environment variable
+ run: echo "POSTGRES_MAJOR_VERSION=${{ matrix.postgres_version }}" >> $GITHUB_ENV
+
+ - name: Strip quotes from pg major and set env var
+ run: |
+ stripped_version=$(echo ${{ matrix.postgres_version }} | sed 's/^"\(.*\)"$/\1/')
+ echo "PGMAJOR=$stripped_version" >> $GITHUB_ENV
+
+ - name: Generate common-nix.vars.pkr.hcl
+ run: |
+ PG_VERSION=$(sudo nix run nixpkgs#yq -- '.postgres_release["postgres'${{ matrix.postgres_version }}'"]' ansible/vars.yml)
+ PG_VERSION=$(echo $PG_VERSION | tr -d '"') # Remove any surrounding quotes
+ echo 'postgres-version = "'$PG_VERSION'"' > common-nix.vars.pkr.hcl
+ # Ensure there's a newline at the end of the file
+ echo "" >> common-nix.vars.pkr.hcl
+
- id: settings
# Remove spaces and quotes to get the raw version string
run: sed -r 's/(\s|\")+//g' common-nix.vars.pkr.hcl >> $GITHUB_OUTPUT
- - id: args
- uses: mikefarah/yq@master
- with:
- cmd: yq 'to_entries | map(select(.value|type == "!!str")) | map(.key + "=" + .value) | join("\n")' 'ansible/vars.yml'
+ - name: Generate args
+ id: args
+ run: |
+ ARGS=$(sudo nix run nixpkgs#yq -- 'to_entries | map(select(.value|type == "!!str")) | map(.key + "=" + .value) | join("\n")' ansible/vars.yml)
+ echo "result<<EOF" >> $GITHUB_OUTPUT
+ echo "$ARGS" >> $GITHUB_OUTPUT
+ echo "EOF" >> $GITHUB_OUTPUT
- run: docker context create builders
- uses: docker/setup-buildx-action@v3
@@ -40,7 +80,7 @@ jobs:
with:
load: true
context: .
- file: "Dockerfile-156"
+ file: Dockerfile-${{ env.PGMAJOR }}
target: production
build-args: |
${{ steps.args.outputs.result }}
@@ -57,10 +97,13 @@ jobs:
-p ${{ env.POSTGRES_PORT }}:5432 \
--name supabase_postgres \
-d supabase/postgres:${{ steps.settings.outputs.postgres-version }}
+
- name: Install psql
run: |
+ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
+ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
- sudo apt install -y --no-install-recommends postgresql-client
+ sudo apt install -y --no-install-recommends postgresql-client-${{ env.PGMAJOR }}
- name: Install pg_prove
run: sudo cpan -T TAP::Parser::SourceHandler::pgTAP
@@ -107,11 +150,15 @@ jobs:
PGUSER: supabase_admin
PGPASSWORD: ${{ env.POSTGRES_PASSWORD }}
+ - name: Update Dockerfile.dbmate version
+ run: |
+ sed -i 's/%VERSION%/${{ env.PGMAJOR }}/g' migrations/Dockerfile.dbmate
+
- name: verify schema.sql is committed
run: |
docker compose -f migrations/docker-compose.yaml up db dbmate --abort-on-container-exit
- if ! git diff --ignore-space-at-eol --exit-code --quiet migrations/schema.sql; then
- echo "Detected uncommitted changes after build. See status below:"
- git diff
+ if ! git diff --exit-code --quiet migrations/schema-${{ env.PGMAJOR }}.sql; then
+ echo "Detected changes in schema.sql:"
+ git diff migrations/schema-${{ env.PGMAJOR }}.sql
exit 1
fi
diff --git a/.github/workflows/testinfra-nix.yml b/.github/workflows/testinfra-nix.yml
index fee2fd79..4a51c159 100644
--- a/.github/workflows/testinfra-nix.yml
+++ b/.github/workflows/testinfra-nix.yml
@@ -5,17 +5,35 @@ on:
workflow_dispatch:
jobs:
+ prepare:
+ runs-on: ubuntu-latest
+ outputs:
+ postgres_versions: ${{ steps.set-versions.outputs.postgres_versions }}
+ steps:
+ - name: Checkout Repo
+ uses: actions/checkout@v4
+
+ - uses: DeterminateSystems/nix-installer-action@main
+
+ - name: Set PostgreSQL versions
+ id: set-versions
+ run: |
+ VERSIONS=$(nix run nixpkgs#yq -- '.postgres_major[]' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
+ echo "postgres_versions=$VERSIONS" >> $GITHUB_OUTPUT
+
test-ami-nix:
+ needs: prepare
strategy:
fail-fast: false
matrix:
+ postgres_version: ${{ fromJson(needs.prepare.outputs.postgres_versions) }}
include:
- runner: arm-runner
arch: arm64
ubuntu_release: focal
ubuntu_version: 20.04
mcpu: neoverse-n1
- runs-on: ${{ matrix.runner }}
+ runs-on: ${{ matrix.runner }}
timeout-minutes: 150
permissions:
contents: write
@@ -40,18 +58,29 @@ jobs:
- name: Generate random string
id: random
run: echo "random_string=$(openssl rand -hex 8)" >> $GITHUB_OUTPUT
+
+ - name: Set PostgreSQL version environment variable
+ run: echo "POSTGRES_MAJOR_VERSION=${{ matrix.postgres_version }}" >> $GITHUB_ENV
+
+ - name: Generate common-nix.vars.pkr.hcl
+ run: |
+ PG_VERSION=$(sudo nix run nixpkgs#yq -- '.postgres_release["postgres'${{ matrix.postgres_version }}'"]' ansible/vars.yml)
+ PG_VERSION=$(echo $PG_VERSION | tr -d '"') # Remove any surrounding quotes
+ echo 'postgres-version = "'$PG_VERSION'"' > common-nix.vars.pkr.hcl
+ # Ensure there's a newline at the end of the file
+ echo "" >> common-nix.vars.pkr.hcl
- name: Build AMI stage 1
run: |
packer init amazon-arm64-nix.pkr.hcl
GIT_SHA=${{github.sha}}
- packer build -var "git-head-version=${GIT_SHA}" -var "packer-execution-id=${GITHUB_RUN_ID}" -var-file="development-arm.vars.pkr.hcl" -var-file="common-nix.vars.pkr.hcl" -var "ansible_arguments=" -var "postgres-version=${{ steps.random.outputs.random_string }}" -var "region=ap-southeast-1" -var 'ami_regions=["ap-southeast-1"]' -var "force-deregister=true" amazon-arm64-nix.pkr.hcl
+ packer build -var "git-head-version=${GIT_SHA}" -var "packer-execution-id=${GITHUB_RUN_ID}" -var-file="development-arm.vars.pkr.hcl" -var-file="common-nix.vars.pkr.hcl" -var "ansible_arguments=" -var "postgres-version=${{ steps.random.outputs.random_string }}" -var "region=ap-southeast-1" -var 'ami_regions=["ap-southeast-1"]' -var "force-deregister=true" -var "ansible_arguments=-e postgresql_major=${POSTGRES_MAJOR_VERSION}" amazon-arm64-nix.pkr.hcl
- name: Build AMI stage 2
run: |
packer init stage2-nix-psql.pkr.hcl
GIT_SHA=${{github.sha}}
- packer build -var "git-head-version=${GIT_SHA}" -var "packer-execution-id=${GITHUB_RUN_ID}" -var-file="development-arm.vars.pkr.hcl" -var-file="common-nix.vars.pkr.hcl" -var "postgres-version=${{ steps.random.outputs.random_string }}" -var "region=ap-southeast-1" -var 'ami_regions=["ap-southeast-1"]' -var "force-deregister=true" -var "git_sha=${GITHUB_SHA}" stage2-nix-psql.pkr.hcl
+ packer build -var "git-head-version=${GIT_SHA}" -var "packer-execution-id=${GITHUB_RUN_ID}" -var "postgres_major_version=${POSTGRES_MAJOR_VERSION}" -var-file="development-arm.vars.pkr.hcl" -var-file="common-nix.vars.pkr.hcl" -var "postgres-version=${{ steps.random.outputs.random_string }}" -var "region=ap-southeast-1" -var 'ami_regions=["ap-southeast-1"]' -var "force-deregister=true" -var "git_sha=${GITHUB_SHA}" stage2-nix-psql.pkr.hcl
- name: Run tests
timeout-minutes: 10
@@ -91,4 +120,4 @@ jobs:
# Deregister AMIs
deregister_ami_by_name "$STAGE1_AMI_NAME"
- deregister_ami_by_name "$STAGE2_AMI_NAME"
\ No newline at end of file
+ deregister_ami_by_name "$STAGE2_AMI_NAME"
diff --git a/Dockerfile-15 b/Dockerfile-15
new file mode 100644
index 00000000..ce83237f
--- /dev/null
+++ b/Dockerfile-15
@@ -0,0 +1,223 @@
+# syntax=docker/dockerfile:1.6
+ARG postgresql_major=15
+ARG postgresql_release=${postgresql_major}.1
+
+# Bump default build arg to build a package from source
+# Bump vars.yml to specify runtime package version
+ARG sfcgal_release=1.3.10
+ARG postgis_release=3.3.2
+ARG pgrouting_release=3.4.1
+ARG pgtap_release=1.2.0
+ARG pg_cron_release=1.6.2
+ARG pgaudit_release=1.7.0
+ARG pgjwt_release=9742dab1b2f297ad3811120db7b21451bca2d3c9
+ARG pgsql_http_release=1.5.0
+ARG plpgsql_check_release=2.2.5
+ARG pg_safeupdate_release=1.4
+ARG timescaledb_release=2.9.1
+ARG wal2json_release=2_5
+ARG pljava_release=1.6.4
+ARG plv8_release=3.1.5
+ARG pg_plan_filter_release=5081a7b5cb890876e67d8e7486b6a64c38c9a492
+ARG pg_net_release=0.7.1
+ARG rum_release=1.3.13
+ARG pg_hashids_release=cd0e1b31d52b394a0df64079406a14a4f7387cd6
+ARG libsodium_release=1.0.18
+ARG pgsodium_release=3.1.6
+ARG pg_graphql_release=1.5.1
+ARG pg_stat_monitor_release=1.1.1
+ARG pg_jsonschema_release=0.1.4
+ARG pg_repack_release=1.4.8
+ARG vault_release=0.2.8
+ARG groonga_release=12.0.8
+ARG pgroonga_release=2.4.0
+ARG wrappers_release=0.3.0
+ARG hypopg_release=1.3.1
+ARG pgvector_release=0.4.0
+ARG pg_tle_release=1.3.2
+ARG index_advisor_release=0.2.0
+ARG supautils_release=2.2.0
+ARG wal_g_release=2.0.1
+
+FROM ubuntu:focal as base
+
+RUN apt update -y && apt install -y \
+ curl \
+ gnupg \
+ lsb-release \
+ software-properties-common \
+ wget \
+ sudo \
+ && apt clean
+
+
+RUN adduser --system --home /var/lib/postgresql --no-create-home --shell /bin/bash --group --gecos "PostgreSQL administrator" postgres
+RUN adduser --system --no-create-home --shell /bin/bash --group wal-g
+RUN curl --proto '=https' --tlsv1.2 -sSf -L https://install.determinate.systems/nix | sh -s -- install linux \
+--init none \
+--no-confirm \
+--extra-conf "substituters = https://cache.nixos.org https://nix-postgres-artifacts.s3.amazonaws.com" \
+--extra-conf "trusted-public-keys = nix-postgres-artifacts:dGZlQOvKcNEjvT7QEAJbcV6b6uk7VF/hWMjhYleiaLI=% cache.nixos.org-1:6NCHdD59X431o0gWypbMrAURkbJ16ZPMQFGspcDShjY="
+
+ENV PATH="${PATH}:/nix/var/nix/profiles/default/bin"
+
+COPY . /nixpg
+
+WORKDIR /nixpg
+
+RUN nix profile install .#psql_15/bin
+
+
+
+WORKDIR /
+
+
+RUN mkdir -p /usr/lib/postgresql/bin \
+ /usr/lib/postgresql/share/postgresql \
+ /usr/share/postgresql \
+ # /usr/lib/postgresql/share/postgresql/contrib \
+ #/usr/lib/postgresql/share/postgresql/timezonesets \
+ #/usr/lib/postgresql/share/postgresql/tsearch_data \
+ # /usr/lib/postgresql/share/postgresql/extension \
+ && chown -R postgres:postgres /usr/lib/postgresql \
+ && chown -R postgres:postgres /usr/share/postgresql
+
+# Create symbolic links
+RUN ln -s /nix/var/nix/profiles/default/bin/* /usr/lib/postgresql/bin/ \
+ && ln -s /nix/var/nix/profiles/default/bin/* /usr/bin/ \
+ && chown -R postgres:postgres /usr/bin
+
+# Create symbolic links for PostgreSQL shares
+RUN ln -s /nix/var/nix/profiles/default/share/postgresql/* /usr/lib/postgresql/share/postgresql/
+RUN ln -s /nix/var/nix/profiles/default/share/postgresql/* /usr/share/postgresql/
+RUN chown -R postgres:postgres /usr/lib/postgresql/share/postgresql/
+RUN chown -R postgres:postgres /usr/share/postgresql/
+# Create symbolic links for contrib directory
+RUN mkdir -p /usr/lib/postgresql/share/postgresql/contrib \
+ && find /nix/var/nix/profiles/default/share/postgresql/contrib/ -mindepth 1 -type d -exec sh -c 'for dir do ln -s "$dir" "/usr/lib/postgresql/share/postgresql/contrib/$(basename "$dir")"; done' sh {} + \
+ && chown -R postgres:postgres /usr/lib/postgresql/share/postgresql/contrib/
+
+RUN chown -R postgres:postgres /usr/lib/postgresql
+
+RUN ln -sf /usr/lib/postgresql/share/postgresql/timezonesets /usr/share/postgresql/timezonesets
+
+
+RUN apt-get update && \
+ apt-get install -y --no-install-recommends tzdata
+
+RUN ln -fs /usr/share/zoneinfo/Etc/UTC /etc/localtime && \
+ dpkg-reconfigure --frontend noninteractive tzdata
+
+RUN apt-get update && \
+ apt-get install -y --no-install-recommends \
+ build-essential \
+ checkinstall \
+ cmake
+
+ENV PGDATA=/var/lib/postgresql/data
+
+####################
+# setup-wal-g.yml
+####################
+FROM base as walg
+ARG wal_g_release
+# ADD "https://github.com/wal-g/wal-g/releases/download/v${wal_g_release}/wal-g-pg-ubuntu-20.04-${TARGETARCH}.tar.gz" /tmp/wal-g.tar.gz
+RUN arch=$([ "$TARGETARCH" = "arm64" ] && echo "aarch64" || echo "$TARGETARCH") && \
+ apt-get update && apt-get install -y --no-install-recommends curl && \
+ curl -kL "https://github.com/wal-g/wal-g/releases/download/v${wal_g_release}/wal-g-pg-ubuntu-20.04-aarch64.tar.gz" -o /tmp/wal-g.tar.gz && \
+ tar -xvf /tmp/wal-g.tar.gz -C /tmp && \
+ rm -rf /tmp/wal-g.tar.gz && \
+ mv /tmp/wal-g-pg-ubuntu*20.04-aarch64 /tmp/wal-g
+
+# ####################
+# # Download gosu for easy step-down from root
+# ####################
+FROM base as gosu
+ARG TARGETARCH
+# Install dependencies
+RUN apt-get update && apt-get install -y --no-install-recommends \
+ gnupg \
+ ca-certificates \
+ && rm -rf /var/lib/apt/lists/*
+# Download binary
+ARG GOSU_VERSION=1.16
+ARG GOSU_GPG_KEY=B42F6819007F00F88E364FD4036A9C25BF357DD4
+ADD https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$TARGETARCH \
+ /usr/local/bin/gosu
+ADD https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$TARGETARCH.asc \
+ /usr/local/bin/gosu.asc
+# Verify checksum
+RUN gpg --batch --keyserver hkps://keys.openpgp.org --recv-keys $GOSU_GPG_KEY && \
+ gpg --batch --verify /usr/local/bin/gosu.asc /usr/local/bin/gosu && \
+ gpgconf --kill all && \
+ chmod +x /usr/local/bin/gosu
+
+# ####################
+# # Build final image
+# ####################
+FROM gosu as production
+RUN id postgres || (echo "postgres user does not exist" && exit 1)
+# # Setup extensions
+COPY --from=walg /tmp/wal-g /usr/local/bin/
+
+# # Initialise configs
+COPY --chown=postgres:postgres ansible/files/postgresql_config/postgresql.conf.j2 /etc/postgresql/postgresql.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/pg_hba.conf.j2 /etc/postgresql/pg_hba.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/pg_ident.conf.j2 /etc/postgresql/pg_ident.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/postgresql-stdout-log.conf /etc/postgresql/logging.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/supautils.conf.j2 /etc/postgresql-custom/supautils.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_extension_custom_scripts /etc/postgresql-custom/extension-custom-scripts
+COPY --chown=postgres:postgres ansible/files/pgsodium_getkey_urandom.sh.j2 /usr/lib/postgresql/bin/pgsodium_getkey.sh
+COPY --chown=postgres:postgres ansible/files/postgresql_config/custom_read_replica.conf.j2 /etc/postgresql-custom/read-replica.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/custom_walg.conf.j2 /etc/postgresql-custom/wal-g.conf
+COPY --chown=postgres:postgres ansible/files/walg_helper_scripts/wal_fetch.sh /home/postgres/wal_fetch.sh
+COPY ansible/files/walg_helper_scripts/wal_change_ownership.sh /root/wal_change_ownership.sh
+
+RUN sed -i \
+ -e "s|#unix_socket_directories = '/tmp'|unix_socket_directories = '/var/run/postgresql'|g" \
+ -e "s|#session_preload_libraries = ''|session_preload_libraries = 'supautils'|g" \
+ -e "s|#include = '/etc/postgresql-custom/supautils.conf'|include = '/etc/postgresql-custom/supautils.conf'|g" \
+ -e "s|#include = '/etc/postgresql-custom/wal-g.conf'|include = '/etc/postgresql-custom/wal-g.conf'|g" /etc/postgresql/postgresql.conf && \
+ echo "cron.database_name = 'postgres'" >> /etc/postgresql/postgresql.conf && \
+ #echo "pljava.libjvm_location = '/usr/lib/jvm/java-11-openjdk-${TARGETARCH}/lib/server/libjvm.so'" >> /etc/postgresql/postgresql.conf && \
+ echo "pgsodium.getkey_script= '/usr/lib/postgresql/bin/pgsodium_getkey.sh'" >> /etc/postgresql/postgresql.conf && \
+ echo 'auto_explain.log_min_duration = 10s' >> /etc/postgresql/postgresql.conf && \
+ usermod -aG postgres wal-g && \
+ mkdir -p /etc/postgresql-custom && \
+ chown postgres:postgres /etc/postgresql-custom
+
+# # Include schema migrations
+COPY migrations/db /docker-entrypoint-initdb.d/
+COPY ansible/files/pgbouncer_config/pgbouncer_auth_schema.sql /docker-entrypoint-initdb.d/init-scripts/00-schema.sql
+COPY ansible/files/stat_extension.sql /docker-entrypoint-initdb.d/migrations/00-extension.sql
+
+# # Add upstream entrypoint script
+COPY --from=gosu /usr/local/bin/gosu /usr/local/bin/gosu
+ADD --chmod=0755 \
+ https://github.com/docker-library/postgres/raw/master/15/bullseye/docker-entrypoint.sh \
+ /usr/local/bin/
+
+RUN mkdir -p /var/run/postgresql && chown postgres:postgres /var/run/postgresql
+
+ENTRYPOINT ["docker-entrypoint.sh"]
+
+HEALTHCHECK --interval=2s --timeout=2s --retries=10 CMD pg_isready -U postgres -h localhost
+STOPSIGNAL SIGINT
+EXPOSE 5432
+
+ENV POSTGRES_HOST=/var/run/postgresql
+ENV POSTGRES_USER=supabase_admin
+ENV POSTGRES_DB=postgres
+RUN apt-get update && apt-get install -y --no-install-recommends \
+ locales \
+ && rm -rf /var/lib/apt/lists/* && \
+ localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8 \
+ && localedef -i C -c -f UTF-8 -A /usr/share/locale/locale.alias C.UTF-8
+RUN echo "C.UTF-8 UTF-8" > /etc/locale.gen && echo "en_US.UTF-8 UTF-8" >> /etc/locale.gen && locale-gen
+ENV LANG en_US.UTF-8
+ENV LANGUAGE en_US:en
+ENV LC_ALL en_US.UTF-8
+ENV LC_CTYPE=C.UTF-8
+ENV LC_COLLATE=C.UTF-8
+ENV LOCALE_ARCHIVE /usr/lib/locale/locale-archive
+CMD ["postgres", "-D", "/etc/postgresql"]
diff --git a/Dockerfile-16 b/Dockerfile-16
new file mode 100644
index 00000000..db5772ce
--- /dev/null
+++ b/Dockerfile-16
@@ -0,0 +1,223 @@
+# syntax=docker/dockerfile:1.6
+ARG postgresql_major=15
+ARG postgresql_release=${postgresql_major}.1
+
+# Bump default build arg to build a package from source
+# Bump vars.yml to specify runtime package version
+ARG sfcgal_release=1.3.10
+ARG postgis_release=3.3.2
+ARG pgrouting_release=3.4.1
+ARG pgtap_release=1.2.0
+ARG pg_cron_release=1.6.2
+ARG pgaudit_release=1.7.0
+ARG pgjwt_release=9742dab1b2f297ad3811120db7b21451bca2d3c9
+ARG pgsql_http_release=1.5.0
+ARG plpgsql_check_release=2.2.5
+ARG pg_safeupdate_release=1.4
+ARG timescaledb_release=2.9.1
+ARG wal2json_release=2_5
+ARG pljava_release=1.6.4
+ARG plv8_release=3.1.5
+ARG pg_plan_filter_release=5081a7b5cb890876e67d8e7486b6a64c38c9a492
+ARG pg_net_release=0.7.1
+ARG rum_release=1.3.13
+ARG pg_hashids_release=cd0e1b31d52b394a0df64079406a14a4f7387cd6
+ARG libsodium_release=1.0.18
+ARG pgsodium_release=3.1.6
+ARG pg_graphql_release=1.5.1
+ARG pg_stat_monitor_release=1.1.1
+ARG pg_jsonschema_release=0.1.4
+ARG pg_repack_release=1.4.8
+ARG vault_release=0.2.8
+ARG groonga_release=12.0.8
+ARG pgroonga_release=2.4.0
+ARG wrappers_release=0.3.0
+ARG hypopg_release=1.3.1
+ARG pgvector_release=0.4.0
+ARG pg_tle_release=1.3.2
+ARG index_advisor_release=0.2.0
+ARG supautils_release=2.2.0
+ARG wal_g_release=2.0.1
+
+FROM ubuntu:focal as base
+
+RUN apt update -y && apt install -y \
+ curl \
+ gnupg \
+ lsb-release \
+ software-properties-common \
+ wget \
+ sudo \
+ && apt clean
+
+
+RUN adduser --system --home /var/lib/postgresql --no-create-home --shell /bin/bash --group --gecos "PostgreSQL administrator" postgres
+RUN adduser --system --no-create-home --shell /bin/bash --group wal-g
+RUN curl --proto '=https' --tlsv1.2 -sSf -L https://install.determinate.systems/nix | sh -s -- install linux \
+--init none \
+--no-confirm \
+--extra-conf "substituters = https://cache.nixos.org https://nix-postgres-artifacts.s3.amazonaws.com" \
+--extra-conf "trusted-public-keys = nix-postgres-artifacts:dGZlQOvKcNEjvT7QEAJbcV6b6uk7VF/hWMjhYleiaLI=% cache.nixos.org-1:6NCHdD59X431o0gWypbMrAURkbJ16ZPMQFGspcDShjY="
+
+ENV PATH="${PATH}:/nix/var/nix/profiles/default/bin"
+
+COPY . /nixpg
+
+WORKDIR /nixpg
+
+RUN nix profile install .#psql_16/bin
+
+
+
+WORKDIR /
+
+
+RUN mkdir -p /usr/lib/postgresql/bin \
+ /usr/lib/postgresql/share/postgresql \
+ /usr/share/postgresql \
+ # /usr/lib/postgresql/share/postgresql/contrib \
+ #/usr/lib/postgresql/share/postgresql/timezonesets \
+ #/usr/lib/postgresql/share/postgresql/tsearch_data \
+ # /usr/lib/postgresql/share/postgresql/extension \
+ && chown -R postgres:postgres /usr/lib/postgresql \
+ && chown -R postgres:postgres /usr/share/postgresql
+
+# Create symbolic links
+RUN ln -s /nix/var/nix/profiles/default/bin/* /usr/lib/postgresql/bin/ \
+ && ln -s /nix/var/nix/profiles/default/bin/* /usr/bin/ \
+ && chown -R postgres:postgres /usr/bin
+
+# Create symbolic links for PostgreSQL shares
+RUN ln -s /nix/var/nix/profiles/default/share/postgresql/* /usr/lib/postgresql/share/postgresql/
+RUN ln -s /nix/var/nix/profiles/default/share/postgresql/* /usr/share/postgresql/
+RUN chown -R postgres:postgres /usr/lib/postgresql/share/postgresql/
+RUN chown -R postgres:postgres /usr/share/postgresql/
+# Create symbolic links for contrib directory
+RUN mkdir -p /usr/lib/postgresql/share/postgresql/contrib \
+ && find /nix/var/nix/profiles/default/share/postgresql/contrib/ -mindepth 1 -type d -exec sh -c 'for dir do ln -s "$dir" "/usr/lib/postgresql/share/postgresql/contrib/$(basename "$dir")"; done' sh {} + \
+ && chown -R postgres:postgres /usr/lib/postgresql/share/postgresql/contrib/
+
+RUN chown -R postgres:postgres /usr/lib/postgresql
+
+RUN ln -sf /usr/lib/postgresql/share/postgresql/timezonesets /usr/share/postgresql/timezonesets
+
+
+RUN apt-get update && \
+ apt-get install -y --no-install-recommends tzdata
+
+RUN ln -fs /usr/share/zoneinfo/Etc/UTC /etc/localtime && \
+ dpkg-reconfigure --frontend noninteractive tzdata
+
+RUN apt-get update && \
+ apt-get install -y --no-install-recommends \
+ build-essential \
+ checkinstall \
+ cmake
+
+ENV PGDATA=/var/lib/postgresql/data
+
+####################
+# setup-wal-g.yml
+####################
+FROM base as walg
+ARG wal_g_release
+# ADD "https://github.com/wal-g/wal-g/releases/download/v${wal_g_release}/wal-g-pg-ubuntu-20.04-${TARGETARCH}.tar.gz" /tmp/wal-g.tar.gz
+RUN arch=$([ "$TARGETARCH" = "arm64" ] && echo "aarch64" || echo "$TARGETARCH") && \
+ apt-get update && apt-get install -y --no-install-recommends curl && \
+ curl -kL "https://github.com/wal-g/wal-g/releases/download/v${wal_g_release}/wal-g-pg-ubuntu-20.04-aarch64.tar.gz" -o /tmp/wal-g.tar.gz && \
+ tar -xvf /tmp/wal-g.tar.gz -C /tmp && \
+ rm -rf /tmp/wal-g.tar.gz && \
+ mv /tmp/wal-g-pg-ubuntu*20.04-aarch64 /tmp/wal-g
+
+# ####################
+# # Download gosu for easy step-down from root
+# ####################
+FROM base as gosu
+ARG TARGETARCH
+# Install dependencies
+RUN apt-get update && apt-get install -y --no-install-recommends \
+ gnupg \
+ ca-certificates \
+ && rm -rf /var/lib/apt/lists/*
+# Download binary
+ARG GOSU_VERSION=1.16
+ARG GOSU_GPG_KEY=B42F6819007F00F88E364FD4036A9C25BF357DD4
+ADD https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$TARGETARCH \
+ /usr/local/bin/gosu
+ADD https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$TARGETARCH.asc \
+ /usr/local/bin/gosu.asc
+# Verify checksum
+RUN gpg --batch --keyserver hkps://keys.openpgp.org --recv-keys $GOSU_GPG_KEY && \
+ gpg --batch --verify /usr/local/bin/gosu.asc /usr/local/bin/gosu && \
+ gpgconf --kill all && \
+ chmod +x /usr/local/bin/gosu
+
+# ####################
+# # Build final image
+# ####################
+FROM gosu as production
+RUN id postgres || (echo "postgres user does not exist" && exit 1)
+# # Setup extensions
+COPY --from=walg /tmp/wal-g /usr/local/bin/
+
+# # Initialise configs
+COPY --chown=postgres:postgres ansible/files/postgresql_config/postgresql.conf.j2 /etc/postgresql/postgresql.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/pg_hba.conf.j2 /etc/postgresql/pg_hba.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/pg_ident.conf.j2 /etc/postgresql/pg_ident.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/postgresql-stdout-log.conf /etc/postgresql/logging.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/supautils.conf.j2 /etc/postgresql-custom/supautils.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_extension_custom_scripts /etc/postgresql-custom/extension-custom-scripts
+COPY --chown=postgres:postgres ansible/files/pgsodium_getkey_urandom.sh.j2 /usr/lib/postgresql/bin/pgsodium_getkey.sh
+COPY --chown=postgres:postgres ansible/files/postgresql_config/custom_read_replica.conf.j2 /etc/postgresql-custom/read-replica.conf
+COPY --chown=postgres:postgres ansible/files/postgresql_config/custom_walg.conf.j2 /etc/postgresql-custom/wal-g.conf
+COPY --chown=postgres:postgres ansible/files/walg_helper_scripts/wal_fetch.sh /home/postgres/wal_fetch.sh
+COPY ansible/files/walg_helper_scripts/wal_change_ownership.sh /root/wal_change_ownership.sh
+
+RUN sed -i \
+ -e "s|#unix_socket_directories = '/tmp'|unix_socket_directories = '/var/run/postgresql'|g" \
+ -e "s|#session_preload_libraries = ''|session_preload_libraries = 'supautils'|g" \
+ -e "s|#include = '/etc/postgresql-custom/supautils.conf'|include = '/etc/postgresql-custom/supautils.conf'|g" \
+ -e "s|#include = '/etc/postgresql-custom/wal-g.conf'|include = '/etc/postgresql-custom/wal-g.conf'|g" /etc/postgresql/postgresql.conf && \
+ echo "cron.database_name = 'postgres'" >> /etc/postgresql/postgresql.conf && \
+ #echo "pljava.libjvm_location = '/usr/lib/jvm/java-11-openjdk-${TARGETARCH}/lib/server/libjvm.so'" >> /etc/postgresql/postgresql.conf && \
+ echo "pgsodium.getkey_script= '/usr/lib/postgresql/bin/pgsodium_getkey.sh'" >> /etc/postgresql/postgresql.conf && \
+ echo 'auto_explain.log_min_duration = 10s' >> /etc/postgresql/postgresql.conf && \
+ usermod -aG postgres wal-g && \
+ mkdir -p /etc/postgresql-custom && \
+ chown postgres:postgres /etc/postgresql-custom
+
+# # Include schema migrations
+COPY migrations/db /docker-entrypoint-initdb.d/
+COPY ansible/files/pgbouncer_config/pgbouncer_auth_schema.sql /docker-entrypoint-initdb.d/init-scripts/00-schema.sql
+COPY ansible/files/stat_extension.sql /docker-entrypoint-initdb.d/migrations/00-extension.sql
+
+# # Add upstream entrypoint script
+COPY --from=gosu /usr/local/bin/gosu /usr/local/bin/gosu
+ADD --chmod=0755 \
+ https://github.com/docker-library/postgres/raw/master/15/bullseye/docker-entrypoint.sh \
+ /usr/local/bin/
+
+RUN mkdir -p /var/run/postgresql && chown postgres:postgres /var/run/postgresql
+
+ENTRYPOINT ["docker-entrypoint.sh"]
+
+HEALTHCHECK --interval=2s --timeout=2s --retries=10 CMD pg_isready -U postgres -h localhost
+STOPSIGNAL SIGINT
+EXPOSE 5432
+
+ENV POSTGRES_HOST=/var/run/postgresql
+ENV POSTGRES_USER=supabase_admin
+ENV POSTGRES_DB=postgres
+RUN apt-get update && apt-get install -y --no-install-recommends \
+ locales \
+ && rm -rf /var/lib/apt/lists/* && \
+ localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8 \
+ && localedef -i C -c -f UTF-8 -A /usr/share/locale/locale.alias C.UTF-8
+RUN echo "C.UTF-8 UTF-8" > /etc/locale.gen && echo "en_US.UTF-8 UTF-8" >> /etc/locale.gen && locale-gen
+ENV LANG en_US.UTF-8
+ENV LANGUAGE en_US:en
+ENV LC_ALL en_US.UTF-8
+ENV LC_CTYPE=C.UTF-8
+ENV LC_COLLATE=C.UTF-8
+ENV LOCALE_ARCHIVE /usr/lib/locale/locale-archive
+CMD ["postgres", "-D", "/etc/postgresql"]
diff --git a/ansible/tasks/stage2-setup-postgres.yml b/ansible/tasks/stage2-setup-postgres.yml
index 20ad069e..e22cbe67 100644
--- a/ansible/tasks/stage2-setup-postgres.yml
+++ b/ansible/tasks/stage2-setup-postgres.yml
@@ -6,7 +6,7 @@
- name: Install Postgres from nix binary cache
become: yes
shell: |
- sudo -u postgres bash -c ". /nix/var/nix/profiles/default/etc/profile.d/nix-daemon.sh && nix profile install github:supabase/postgres/{{ git_commit_sha }}#psql_15/bin"
+ sudo -u postgres bash -c ". /nix/var/nix/profiles/default/etc/profile.d/nix-daemon.sh && nix profile install github:supabase/postgres/{{ git_commit_sha }}#{{psql_version}}/bin"
#TODO (samrose) switch pg_prove sourcing to develop branch once PR is merged
when: stage2_nix
@@ -22,6 +22,18 @@
sudo -u postgres bash -c ". /nix/var/nix/profiles/default/etc/profile.d/nix-daemon.sh && nix profile install github:supabase/postgres/{{ git_commit_sha }}#supabase-groonga"
when: stage2_nix
+- name: Install debug symbols for postgres version
+ become: yes
+ shell: |
+ sudo -u postgres bash -c ". /nix/var/nix/profiles/default/etc/profile.d/nix-daemon.sh && nix profile install github:supabase/postgres/{{ git_commit_sha }}#{{postgresql_version}}_debug"
+ when: stage2_nix
+
+- name: Install source files for postgresql version
+ become: yes
+ shell: |
+ sudo -u postgres bash -c ". /nix/var/nix/profiles/default/etc/profile.d/nix-daemon.sh && nix profile install github:supabase/postgres/{{ git_commit_sha }}#{{postgresql_version}}_src"
+ when: stage2_nix
+
- name: Set ownership and permissions for /etc/ssl/private
become: yes
file:
diff --git a/ansible/vars.yml b/ansible/vars.yml
index 3c5ef217..f78b56c9 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -2,9 +2,17 @@ supabase_internal: true
ebssurrogate_mode: true
async_mode: true
-postgresql_major: "15"
-postgresql_release: "15.1"
-postgresql_release_checksum: sha256:ea2cf059a85882654b989acd07edc121833164a30340faee0d3615cf7058e66c
+# postgresql_major: "15"
+# postgresql_release: "15.1"
+# postgresql_release_checksum: sha256:ea2cf059a85882654b989acd07edc121833164a30340faee0d3615cf7058e66c
+postgres_major:
+ - "15"
+ - "16"
+
+# Full version strings for each major version
+postgres_release:
+ postgres15: "15.8.1.003-staging-5"
+ postgres16: "16.3.1.000-staging-5"
# Non Postgres Extensions
pgbouncer_release: "1.19.0"
diff --git a/common-nix.vars.pkr.hcl b/common-nix.vars.pkr.hcl
deleted file mode 100644
index 8419992a..00000000
--- a/common-nix.vars.pkr.hcl
+++ /dev/null
@@ -1 +0,0 @@
-postgres-version = "15.8.1.003"
diff --git a/docker/nix/build_nix.sh b/docker/nix/build_nix.sh
index a8a8639e..3cf8505c 100644
--- a/docker/nix/build_nix.sh
+++ b/docker/nix/build_nix.sh
@@ -5,6 +5,9 @@ nix --version
if [ -d "/workspace" ]; then
cd /workspace
fi
+nix build .#checks.$(nix-instantiate --eval -E builtins.currentSystem | tr -d '"').psql_15 -L --no-link
+nix build .#checks.$(nix-instantiate --eval -E builtins.currentSystem | tr -d '"').psql_16 -L --no-link
nix build .#psql_15/bin -o psql_15
-nix flake check -L
+nix build .#psql_16/bin -o psql_16
nix copy --to s3://nix-postgres-artifacts?secret-key=nix-secret-key ./psql_15
+nix copy --to s3://nix-postgres-artifacts?secret-key=nix-secret-key ./psql_16
diff --git a/flake.nix b/flake.nix
index 15f1a585..f3d724dc 100644
--- a/flake.nix
+++ b/flake.nix
@@ -141,8 +141,6 @@
#postgis_override = pkgs.postgis_override;
getPostgresqlPackage = version:
pkgs.postgresql."postgresql_${version}";
- #we will add supported versions to this list in the future
- supportedVersions = [ "15" ];
# Create a 'receipt' file for a given postgresql package. This is a way
# of adding a bit of metadata to the package, which can be used by other
# tools to inspect what the contents of the install are: the PSQL
@@ -290,8 +288,7 @@
# Define the available PostgreSQL versions
postgresVersions = {
psql_15 = makePostgres "15";
- # Uncomment the line below to enable PostgreSQL 16
- # psql_16 = makePostgres "16";
+ psql_16 = makePostgres "16";
# psql_orioledb_16 = makeOrioleDbPostgres "16_23" postgresql_orioledb_16;
};
@@ -307,16 +304,19 @@
postgresql = postgresqlPackage;
};
postgresql_15 = getPostgresqlPackage "15";
+ postgresql_16 = getPostgresqlPackage "16";
in
postgresVersions //{
supabase-groonga = supabase-groonga;
# PostgreSQL versions.
- psql_15 = makePostgres "15";
+ psql_15 = postgresVersions.psql_15;
+ psql_16 = postgresVersions.psql_16;
#psql_orioledb_16 = makeOrioleDbPostgres "16_23" postgresql_orioledb_16;
sfcgal = sfcgal;
pg_prove = pkgs.perlPackages.TAPParserSourceHandlerpgTAP;
- inherit postgresql_15;
+ inherit postgresql_15 postgresql_16;
postgresql_15_debug = if pkgs.stdenv.isLinux then postgresql_15.debug else null;
+ postgresql_16_debug = if pkgs.stdenv.isLinux then postgresql_16.debug else null;
postgresql_15_src = pkgs.stdenv.mkDerivation {
pname = "postgresql-15-src";
version = postgresql_15.version;
@@ -339,6 +339,28 @@
platforms = platforms.all;
};
};
+ postgresql_16_src = pkgs.stdenv.mkDerivation {
+ pname = "postgresql-16-src";
+ version = postgresql_16.version;
+
+ src = postgresql_16.src;
+
+ nativeBuildInputs = [ pkgs.bzip2 ];
+
+ phases = [ "unpackPhase" "installPhase" ];
+
+ installPhase = ''
+ mkdir -p $out
+ cp -r . $out
+ '';
+
+ meta = with pkgs.lib; {
+ description = "PostgreSQL 15 source files";
+ homepage = "https://www.postgresql.org/";
+ license = licenses.postgresql;
+ platforms = platforms.all;
+ };
+ };
mecab_naist_jdic = mecab-naist-jdic;
supabase_groonga = supabase-groonga;
pg_regress = makePgRegress activeVersion;
@@ -397,6 +419,7 @@
--subst-var-by 'PGSQL_SUPERUSER' '${pgsqlSuperuser}' \
--subst-var-by 'PSQL15_BINDIR' '${basePackages.psql_15.bin}' \
--subst-var-by 'PSQL_CONF_FILE' $out/etc/postgresql/postgresql.conf \
+ --subst-var-by 'PSQL16_BINDIR' '${basePackages.psql_16.bin}' \
--subst-var-by 'PGSODIUM_GETKEY' '${getkeyScript}' \
--subst-var-by 'READREPL_CONF_FILE' "$out/etc/postgresql-custom/read-replica.conf" \
--subst-var-by 'LOGGING_CONF_FILE' "$out/etc/postgresql-custom/logging.conf" \
@@ -425,6 +448,7 @@
--subst-var-by 'PGSQL_DEFAULT_PORT' '${pgsqlDefaultPort}' \
--subst-var-by 'PGSQL_SUPERUSER' '${pgsqlSuperuser}' \
--subst-var-by 'PSQL15_BINDIR' '${basePackages.psql_15.bin}' \
+ --subst-var-by 'PSQL16_BINDIR' '${basePackages.psql_16.bin}' \
--subst-var-by 'MIGRATIONS_DIR' '${migrationsDir}' \
--subst-var-by 'POSTGRESQL_SCHEMA_SQL' '${postgresqlSchemaSql}' \
--subst-var-by 'PGBOUNCER_AUTH_SCHEMA_SQL' '${pgbouncerAuthSchemaSql}' \
@@ -576,7 +600,7 @@
# flake check'. This is run in the CI system, as well.
checks = {
psql_15 = makeCheckHarness basePackages.psql_15.bin;
- #psql_16 = makeCheckHarness basePackages.psql_16.bin;
+ psql_16 = makeCheckHarness basePackages.psql_16.bin;
#psql_orioledb_16 = makeCheckHarness basePackages.psql_orioledb_16.bin;
};
diff --git a/migrations/Dockerfile.dbmate b/migrations/Dockerfile.dbmate
new file mode 100644
index 00000000..29c80e6e
--- /dev/null
+++ b/migrations/Dockerfile.dbmate
@@ -0,0 +1,23 @@
+FROM debian:bullseye-slim
+
+RUN apt-get update && apt-get install -y curl wget gnupg2 lsb-release
+
+RUN ARCH=$(dpkg --print-architecture); \
+ case ${ARCH} in \
+ amd64) DBMATE_ARCH="linux-amd64" ;; \
+ arm64) DBMATE_ARCH="linux-arm64" ;; \
+ *) echo "Unsupported architecture: ${ARCH}"; exit 1 ;; \
+ esac && \
+ curl -fsSL -o /usr/local/bin/dbmate \
+ https://github.com/amacneil/dbmate/releases/latest/download/dbmate-${DBMATE_ARCH} && \
+ chmod +x /usr/local/bin/dbmate
+
+RUN wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
+RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list
+RUN apt-get update && apt-get install -y postgresql-client-%VERSION%
+
+ENV PATH="/usr/lib/postgresql/%VERSION%/bin:${PATH}"
+
+RUN dbmate --version
+
+ENTRYPOINT ["dbmate"]
diff --git a/migrations/docker-compose.yaml b/migrations/docker-compose.yaml
index 2609d600..1b3e8b14 100644
--- a/migrations/docker-compose.yaml
+++ b/migrations/docker-compose.yaml
@@ -33,7 +33,9 @@ services:
command: pg_prove /tests/test.sql
dbmate:
- image: amacneil/dbmate:1.16.2
+ build:
+ context: .
+ dockerfile: Dockerfile.dbmate
depends_on:
db:
condition: service_healthy
diff --git a/migrations/schema-15.sql b/migrations/schema-15.sql
new file mode 100644
index 00000000..1bff8b9d
--- /dev/null
+++ b/migrations/schema-15.sql
@@ -0,0 +1,1064 @@
+SET statement_timeout = 0;
+SET lock_timeout = 0;
+SET idle_in_transaction_session_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SELECT pg_catalog.set_config('search_path', '', false);
+SET check_function_bodies = false;
+SET xmloption = content;
+SET client_min_messages = warning;
+SET row_security = off;
+
+--
+-- Name: auth; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA auth;
+
+
+--
+-- Name: extensions; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA extensions;
+
+
+--
+-- Name: graphql; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA graphql;
+
+
+--
+-- Name: graphql_public; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA graphql_public;
+
+
+--
+-- Name: pgbouncer; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA pgbouncer;
+
+
+--
+-- Name: pgsodium; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA pgsodium;
+
+
+--
+-- Name: pgsodium; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pgsodium WITH SCHEMA pgsodium;
+
+
+--
+-- Name: EXTENSION pgsodium; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pgsodium IS 'Pgsodium is a modern cryptography library for Postgres.';
+
+
+--
+-- Name: realtime; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA realtime;
+
+
+--
+-- Name: storage; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA storage;
+
+
+--
+-- Name: vault; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA vault;
+
+
+--
+-- Name: pg_graphql; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pg_graphql WITH SCHEMA graphql;
+
+
+--
+-- Name: EXTENSION pg_graphql; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pg_graphql IS 'pg_graphql: GraphQL support';
+
+
+--
+-- Name: pg_stat_statements; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA extensions;
+
+
+--
+-- Name: EXTENSION pg_stat_statements; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pg_stat_statements IS 'track planning and execution statistics of all SQL statements executed';
+
+
+--
+-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions;
+
+
+--
+-- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';
+
+
+--
+-- Name: pgjwt; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pgjwt WITH SCHEMA extensions;
+
+
+--
+-- Name: EXTENSION pgjwt; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pgjwt IS 'JSON Web Token API for Postgresql';
+
+
+--
+-- Name: supabase_vault; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS supabase_vault WITH SCHEMA vault;
+
+
+--
+-- Name: EXTENSION supabase_vault; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION supabase_vault IS 'Supabase Vault Extension';
+
+
+--
+-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA extensions;
+
+
+--
+-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
+
+
+--
+-- Name: email(); Type: FUNCTION; Schema: auth; Owner: -
+--
+
+CREATE FUNCTION auth.email() RETURNS text
+ LANGUAGE sql STABLE
+ AS $$
+ select nullif(current_setting('request.jwt.claim.email', true), '')::text;
+$$;
+
+
+--
+-- Name: role(); Type: FUNCTION; Schema: auth; Owner: -
+--
+
+CREATE FUNCTION auth.role() RETURNS text
+ LANGUAGE sql STABLE
+ AS $$
+ select nullif(current_setting('request.jwt.claim.role', true), '')::text;
+$$;
+
+
+--
+-- Name: uid(); Type: FUNCTION; Schema: auth; Owner: -
+--
+
+CREATE FUNCTION auth.uid() RETURNS uuid
+ LANGUAGE sql STABLE
+ AS $$
+ select nullif(current_setting('request.jwt.claim.sub', true), '')::uuid;
+$$;
+
+
+--
+-- Name: grant_pg_cron_access(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.grant_pg_cron_access() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ IF EXISTS (
+ SELECT
+ FROM pg_event_trigger_ddl_commands() AS ev
+ JOIN pg_extension AS ext
+ ON ev.objid = ext.oid
+ WHERE ext.extname = 'pg_cron'
+ )
+ THEN
+ grant usage on schema cron to postgres with grant option;
+
+ alter default privileges in schema cron grant all on tables to postgres with grant option;
+ alter default privileges in schema cron grant all on functions to postgres with grant option;
+ alter default privileges in schema cron grant all on sequences to postgres with grant option;
+
+ alter default privileges for user supabase_admin in schema cron grant all
+ on sequences to postgres with grant option;
+ alter default privileges for user supabase_admin in schema cron grant all
+ on tables to postgres with grant option;
+ alter default privileges for user supabase_admin in schema cron grant all
+ on functions to postgres with grant option;
+
+ grant all privileges on all tables in schema cron to postgres with grant option;
+ revoke all on table cron.job from postgres;
+ grant select on table cron.job to postgres with grant option;
+ END IF;
+END;
+$$;
+
+
+--
+-- Name: FUNCTION grant_pg_cron_access(); Type: COMMENT; Schema: extensions; Owner: -
+--
+
+COMMENT ON FUNCTION extensions.grant_pg_cron_access() IS 'Grants access to pg_cron';
+
+
+--
+-- Name: grant_pg_graphql_access(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.grant_pg_graphql_access() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $_$
+DECLARE
+ func_is_graphql_resolve bool;
+BEGIN
+ func_is_graphql_resolve = (
+ SELECT n.proname = 'resolve'
+ FROM pg_event_trigger_ddl_commands() AS ev
+ LEFT JOIN pg_catalog.pg_proc AS n
+ ON ev.objid = n.oid
+ );
+
+ IF func_is_graphql_resolve
+ THEN
+ -- Update public wrapper to pass all arguments through to the pg_graphql resolve func
+ DROP FUNCTION IF EXISTS graphql_public.graphql;
+ create or replace function graphql_public.graphql(
+ "operationName" text default null,
+ query text default null,
+ variables jsonb default null,
+ extensions jsonb default null
+ )
+ returns jsonb
+ language sql
+ as $$
+ select graphql.resolve(
+ query := query,
+ variables := coalesce(variables, '{}'),
+ "operationName" := "operationName",
+ extensions := extensions
+ );
+ $$;
+
+ -- This hook executes when `graphql.resolve` is created. That is not necessarily the last
+ -- function in the extension so we need to grant permissions on existing entities AND
+ -- update default permissions to any others that are created after `graphql.resolve`
+ grant usage on schema graphql to postgres, anon, authenticated, service_role;
+ grant select on all tables in schema graphql to postgres, anon, authenticated, service_role;
+ grant execute on all functions in schema graphql to postgres, anon, authenticated, service_role;
+ grant all on all sequences in schema graphql to postgres, anon, authenticated, service_role;
+ alter default privileges in schema graphql grant all on tables to postgres, anon, authenticated, service_role;
+ alter default privileges in schema graphql grant all on functions to postgres, anon, authenticated, service_role;
+ alter default privileges in schema graphql grant all on sequences to postgres, anon, authenticated, service_role;
+
+ -- Allow postgres role to allow granting usage on graphql and graphql_public schemas to custom roles
+ grant usage on schema graphql_public to postgres with grant option;
+ grant usage on schema graphql to postgres with grant option;
+ END IF;
+
+END;
+$_$;
+
+
+--
+-- Name: FUNCTION grant_pg_graphql_access(); Type: COMMENT; Schema: extensions; Owner: -
+--
+
+COMMENT ON FUNCTION extensions.grant_pg_graphql_access() IS 'Grants access to pg_graphql';
+
+
+--
+-- Name: grant_pg_net_access(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.grant_pg_net_access() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ IF EXISTS (
+ SELECT 1
+ FROM pg_event_trigger_ddl_commands() AS ev
+ JOIN pg_extension AS ext
+ ON ev.objid = ext.oid
+ WHERE ext.extname = 'pg_net'
+ )
+ THEN
+ IF NOT EXISTS (
+ SELECT 1
+ FROM pg_roles
+ WHERE rolname = 'supabase_functions_admin'
+ )
+ THEN
+ CREATE USER supabase_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
+ END IF;
+
+ GRANT USAGE ON SCHEMA net TO supabase_functions_admin, postgres, anon, authenticated, service_role;
+
+ ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
+ ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
+
+ ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
+ ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
+
+ REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
+ REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
+
+ GRANT EXECUTE ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
+ GRANT EXECUTE ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
+ END IF;
+END;
+$$;
+
+
+--
+-- Name: FUNCTION grant_pg_net_access(); Type: COMMENT; Schema: extensions; Owner: -
+--
+
+COMMENT ON FUNCTION extensions.grant_pg_net_access() IS 'Grants access to pg_net';
+
+
+--
+-- Name: pgrst_ddl_watch(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.pgrst_ddl_watch() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ cmd record;
+BEGIN
+ FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands()
+ LOOP
+ IF cmd.command_tag IN (
+ 'CREATE SCHEMA', 'ALTER SCHEMA'
+ , 'CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO', 'ALTER TABLE'
+ , 'CREATE FOREIGN TABLE', 'ALTER FOREIGN TABLE'
+ , 'CREATE VIEW', 'ALTER VIEW'
+ , 'CREATE MATERIALIZED VIEW', 'ALTER MATERIALIZED VIEW'
+ , 'CREATE FUNCTION', 'ALTER FUNCTION'
+ , 'CREATE TRIGGER'
+ , 'CREATE TYPE', 'ALTER TYPE'
+ , 'CREATE RULE'
+ , 'COMMENT'
+ )
+ -- don't notify in case of CREATE TEMP table or other objects created on pg_temp
+ AND cmd.schema_name is distinct from 'pg_temp'
+ THEN
+ NOTIFY pgrst, 'reload schema';
+ END IF;
+ END LOOP;
+END; $$;
+
+
+--
+-- Name: pgrst_drop_watch(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.pgrst_drop_watch() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ obj record;
+BEGIN
+ FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
+ LOOP
+ IF obj.object_type IN (
+ 'schema'
+ , 'table'
+ , 'foreign table'
+ , 'view'
+ , 'materialized view'
+ , 'function'
+ , 'trigger'
+ , 'type'
+ , 'rule'
+ )
+ AND obj.is_temporary IS false -- no pg_temp objects
+ THEN
+ NOTIFY pgrst, 'reload schema';
+ END IF;
+ END LOOP;
+END; $$;
+
+
+--
+-- Name: set_graphql_placeholder(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.set_graphql_placeholder() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $_$
+ DECLARE
+ graphql_is_dropped bool;
+ BEGIN
+ graphql_is_dropped = (
+ SELECT ev.schema_name = 'graphql_public'
+ FROM pg_event_trigger_dropped_objects() AS ev
+ WHERE ev.schema_name = 'graphql_public'
+ );
+
+ IF graphql_is_dropped
+ THEN
+ create or replace function graphql_public.graphql(
+ "operationName" text default null,
+ query text default null,
+ variables jsonb default null,
+ extensions jsonb default null
+ )
+ returns jsonb
+ language plpgsql
+ as $$
+ DECLARE
+ server_version float;
+ BEGIN
+ server_version = (SELECT (SPLIT_PART((select version()), ' ', 2))::float);
+
+ IF server_version >= 14 THEN
+ RETURN jsonb_build_object(
+ 'errors', jsonb_build_array(
+ jsonb_build_object(
+ 'message', 'pg_graphql extension is not enabled.'
+ )
+ )
+ );
+ ELSE
+ RETURN jsonb_build_object(
+ 'errors', jsonb_build_array(
+ jsonb_build_object(
+ 'message', 'pg_graphql is only available on projects running Postgres 14 onwards.'
+ )
+ )
+ );
+ END IF;
+ END;
+ $$;
+ END IF;
+
+ END;
+$_$;
+
+
+--
+-- Name: FUNCTION set_graphql_placeholder(); Type: COMMENT; Schema: extensions; Owner: -
+--
+
+COMMENT ON FUNCTION extensions.set_graphql_placeholder() IS 'Reintroduces placeholder function for graphql_public.graphql';
+
+
+--
+-- Name: get_auth(text); Type: FUNCTION; Schema: pgbouncer; Owner: -
+--
+
+CREATE FUNCTION pgbouncer.get_auth(p_usename text) RETURNS TABLE(username text, password text)
+ LANGUAGE plpgsql SECURITY DEFINER
+ AS $$
+BEGIN
+ RAISE WARNING 'PgBouncer auth request: %', p_usename;
+
+ RETURN QUERY
+ SELECT usename::TEXT, passwd::TEXT FROM pg_catalog.pg_shadow
+ WHERE usename = p_usename;
+END;
+$$;
+
+
+--
+-- Name: extension(text); Type: FUNCTION; Schema: storage; Owner: -
+--
+
+CREATE FUNCTION storage.extension(name text) RETURNS text
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+_parts text[];
+_filename text;
+BEGIN
+ select string_to_array(name, '/') into _parts;
+ select _parts[array_length(_parts,1)] into _filename;
+ -- @todo return the last part instead of 2
+ return split_part(_filename, '.', 2);
+END
+$$;
+
+
+--
+-- Name: filename(text); Type: FUNCTION; Schema: storage; Owner: -
+--
+
+CREATE FUNCTION storage.filename(name text) RETURNS text
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+_parts text[];
+BEGIN
+ select string_to_array(name, '/') into _parts;
+ return _parts[array_length(_parts,1)];
+END
+$$;
+
+
+--
+-- Name: foldername(text); Type: FUNCTION; Schema: storage; Owner: -
+--
+
+CREATE FUNCTION storage.foldername(name text) RETURNS text[]
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+_parts text[];
+BEGIN
+ select string_to_array(name, '/') into _parts;
+ return _parts[1:array_length(_parts,1)-1];
+END
+$$;
+
+
+--
+-- Name: search(text, text, integer, integer, integer); Type: FUNCTION; Schema: storage; Owner: -
+--
+
+CREATE FUNCTION storage.search(prefix text, bucketname text, limits integer DEFAULT 100, levels integer DEFAULT 1, offsets integer DEFAULT 0) RETURNS TABLE(name text, id uuid, updated_at timestamp with time zone, created_at timestamp with time zone, last_accessed_at timestamp with time zone, metadata jsonb)
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+_bucketId text;
+BEGIN
+ -- will be replaced by migrations when server starts
+ -- saving space for cloud-init
+END
+$$;
+
+
+--
+-- Name: secrets_encrypt_secret_secret(); Type: FUNCTION; Schema: vault; Owner: -
+--
+
+CREATE FUNCTION vault.secrets_encrypt_secret_secret() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+ BEGIN
+ new.secret = CASE WHEN new.secret IS NULL THEN NULL ELSE
+ CASE WHEN new.key_id IS NULL THEN NULL ELSE pg_catalog.encode(
+ pgsodium.crypto_aead_det_encrypt(
+ pg_catalog.convert_to(new.secret, 'utf8'),
+ pg_catalog.convert_to((new.id::text || new.description::text || new.created_at::text || new.updated_at::text)::text, 'utf8'),
+ new.key_id::uuid,
+ new.nonce
+ ),
+ 'base64') END END;
+ RETURN new;
+ END;
+ $$;
+
+
+SET default_tablespace = '';
+
+SET default_table_access_method = heap;
+
+--
+-- Name: audit_log_entries; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.audit_log_entries (
+ instance_id uuid,
+ id uuid NOT NULL,
+ payload json,
+ created_at timestamp with time zone
+);
+
+
+--
+-- Name: TABLE audit_log_entries; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.audit_log_entries IS 'Auth: Audit trail for user actions.';
+
+
+--
+-- Name: instances; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.instances (
+ id uuid NOT NULL,
+ uuid uuid,
+ raw_base_config text,
+ created_at timestamp with time zone,
+ updated_at timestamp with time zone
+);
+
+
+--
+-- Name: TABLE instances; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.instances IS 'Auth: Manages users across multiple sites.';
+
+
+--
+-- Name: refresh_tokens; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.refresh_tokens (
+ instance_id uuid,
+ id bigint NOT NULL,
+ token character varying(255),
+ user_id character varying(255),
+ revoked boolean,
+ created_at timestamp with time zone,
+ updated_at timestamp with time zone
+);
+
+
+--
+-- Name: TABLE refresh_tokens; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.refresh_tokens IS 'Auth: Store of tokens used to refresh JWT tokens once they expire.';
+
+
+--
+-- Name: refresh_tokens_id_seq; Type: SEQUENCE; Schema: auth; Owner: -
+--
+
+CREATE SEQUENCE auth.refresh_tokens_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+--
+-- Name: refresh_tokens_id_seq; Type: SEQUENCE OWNED BY; Schema: auth; Owner: -
+--
+
+ALTER SEQUENCE auth.refresh_tokens_id_seq OWNED BY auth.refresh_tokens.id;
+
+
+--
+-- Name: schema_migrations; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.schema_migrations (
+ version character varying(255) NOT NULL
+);
+
+
+--
+-- Name: TABLE schema_migrations; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.schema_migrations IS 'Auth: Manages updates to the auth system.';
+
+
+--
+-- Name: users; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.users (
+ instance_id uuid,
+ id uuid NOT NULL,
+ aud character varying(255),
+ role character varying(255),
+ email character varying(255),
+ encrypted_password character varying(255),
+ confirmed_at timestamp with time zone,
+ invited_at timestamp with time zone,
+ confirmation_token character varying(255),
+ confirmation_sent_at timestamp with time zone,
+ recovery_token character varying(255),
+ recovery_sent_at timestamp with time zone,
+ email_change_token character varying(255),
+ email_change character varying(255),
+ email_change_sent_at timestamp with time zone,
+ last_sign_in_at timestamp with time zone,
+ raw_app_meta_data jsonb,
+ raw_user_meta_data jsonb,
+ is_super_admin boolean,
+ created_at timestamp with time zone,
+ updated_at timestamp with time zone
+);
+
+
+--
+-- Name: TABLE users; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.users IS 'Auth: Stores user login data within a secure schema.';
+
+
+--
+-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.schema_migrations (
+ version character varying(128) NOT NULL
+);
+
+
+--
+-- Name: buckets; Type: TABLE; Schema: storage; Owner: -
+--
+
+CREATE TABLE storage.buckets (
+ id text NOT NULL,
+ name text NOT NULL,
+ owner uuid,
+ created_at timestamp with time zone DEFAULT now(),
+ updated_at timestamp with time zone DEFAULT now()
+);
+
+
+--
+-- Name: migrations; Type: TABLE; Schema: storage; Owner: -
+--
+
+CREATE TABLE storage.migrations (
+ id integer NOT NULL,
+ name character varying(100) NOT NULL,
+ hash character varying(40) NOT NULL,
+ executed_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
+);
+
+
+--
+-- Name: objects; Type: TABLE; Schema: storage; Owner: -
+--
+
+CREATE TABLE storage.objects (
+ id uuid DEFAULT extensions.uuid_generate_v4() NOT NULL,
+ bucket_id text,
+ name text,
+ owner uuid,
+ created_at timestamp with time zone DEFAULT now(),
+ updated_at timestamp with time zone DEFAULT now(),
+ last_accessed_at timestamp with time zone DEFAULT now(),
+ metadata jsonb
+);
+
+
+--
+-- Name: decrypted_secrets; Type: VIEW; Schema: vault; Owner: -
+--
+
+CREATE VIEW vault.decrypted_secrets AS
+ SELECT secrets.id,
+ secrets.name,
+ secrets.description,
+ secrets.secret,
+ CASE
+ WHEN (secrets.secret IS NULL) THEN NULL::text
+ ELSE
+ CASE
+ WHEN (secrets.key_id IS NULL) THEN NULL::text
+ ELSE convert_from(pgsodium.crypto_aead_det_decrypt(decode(secrets.secret, 'base64'::text), convert_to(((((secrets.id)::text || secrets.description) || (secrets.created_at)::text) || (secrets.updated_at)::text), 'utf8'::name), secrets.key_id, secrets.nonce), 'utf8'::name)
+ END
+ END AS decrypted_secret,
+ secrets.key_id,
+ secrets.nonce,
+ secrets.created_at,
+ secrets.updated_at
+ FROM vault.secrets;
+
+
+--
+-- Name: refresh_tokens id; Type: DEFAULT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.refresh_tokens ALTER COLUMN id SET DEFAULT nextval('auth.refresh_tokens_id_seq'::regclass);
+
+
+--
+-- Name: audit_log_entries audit_log_entries_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.audit_log_entries
+ ADD CONSTRAINT audit_log_entries_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: instances instances_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.instances
+ ADD CONSTRAINT instances_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: refresh_tokens refresh_tokens_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.refresh_tokens
+ ADD CONSTRAINT refresh_tokens_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.schema_migrations
+ ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
+
+
+--
+-- Name: users users_email_key; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.users
+ ADD CONSTRAINT users_email_key UNIQUE (email);
+
+
+--
+-- Name: users users_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.users
+ ADD CONSTRAINT users_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.schema_migrations
+ ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
+
+
+--
+-- Name: buckets buckets_pkey; Type: CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.buckets
+ ADD CONSTRAINT buckets_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: migrations migrations_name_key; Type: CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.migrations
+ ADD CONSTRAINT migrations_name_key UNIQUE (name);
+
+
+--
+-- Name: migrations migrations_pkey; Type: CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.migrations
+ ADD CONSTRAINT migrations_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: objects objects_pkey; Type: CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.objects
+ ADD CONSTRAINT objects_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: audit_logs_instance_id_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX audit_logs_instance_id_idx ON auth.audit_log_entries USING btree (instance_id);
+
+
+--
+-- Name: refresh_tokens_instance_id_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX refresh_tokens_instance_id_idx ON auth.refresh_tokens USING btree (instance_id);
+
+
+--
+-- Name: refresh_tokens_instance_id_user_id_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX refresh_tokens_instance_id_user_id_idx ON auth.refresh_tokens USING btree (instance_id, user_id);
+
+
+--
+-- Name: refresh_tokens_token_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX refresh_tokens_token_idx ON auth.refresh_tokens USING btree (token);
+
+
+--
+-- Name: users_instance_id_email_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX users_instance_id_email_idx ON auth.users USING btree (instance_id, email);
+
+
+--
+-- Name: users_instance_id_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX users_instance_id_idx ON auth.users USING btree (instance_id);
+
+
+--
+-- Name: bname; Type: INDEX; Schema: storage; Owner: -
+--
+
+CREATE UNIQUE INDEX bname ON storage.buckets USING btree (name);
+
+
+--
+-- Name: bucketid_objname; Type: INDEX; Schema: storage; Owner: -
+--
+
+CREATE UNIQUE INDEX bucketid_objname ON storage.objects USING btree (bucket_id, name);
+
+
+--
+-- Name: name_prefix_search; Type: INDEX; Schema: storage; Owner: -
+--
+
+CREATE INDEX name_prefix_search ON storage.objects USING btree (name text_pattern_ops);
+
+
+--
+-- Name: buckets buckets_owner_fkey; Type: FK CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.buckets
+ ADD CONSTRAINT buckets_owner_fkey FOREIGN KEY (owner) REFERENCES auth.users(id);
+
+
+--
+-- Name: objects objects_bucketId_fkey; Type: FK CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.objects
+ ADD CONSTRAINT "objects_bucketId_fkey" FOREIGN KEY (bucket_id) REFERENCES storage.buckets(id);
+
+
+--
+-- Name: objects objects_owner_fkey; Type: FK CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.objects
+ ADD CONSTRAINT objects_owner_fkey FOREIGN KEY (owner) REFERENCES auth.users(id);
+
+
+--
+-- Name: objects; Type: ROW SECURITY; Schema: storage; Owner: -
+--
+
+ALTER TABLE storage.objects ENABLE ROW LEVEL SECURITY;
+
+--
+-- Name: supabase_realtime; Type: PUBLICATION; Schema: -; Owner: -
+--
+
+CREATE PUBLICATION supabase_realtime WITH (publish = 'insert, update, delete, truncate');
+
+
+--
+-- Name: issue_graphql_placeholder; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER issue_graphql_placeholder ON sql_drop
+ WHEN TAG IN ('DROP EXTENSION')
+ EXECUTE FUNCTION extensions.set_graphql_placeholder();
+
+
+--
+-- Name: issue_pg_cron_access; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER issue_pg_cron_access ON ddl_command_end
+ WHEN TAG IN ('CREATE EXTENSION')
+ EXECUTE FUNCTION extensions.grant_pg_cron_access();
+
+
+--
+-- Name: issue_pg_graphql_access; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER issue_pg_graphql_access ON ddl_command_end
+ WHEN TAG IN ('CREATE FUNCTION')
+ EXECUTE FUNCTION extensions.grant_pg_graphql_access();
+
+
+--
+-- Name: issue_pg_net_access; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER issue_pg_net_access ON ddl_command_end
+ WHEN TAG IN ('CREATE EXTENSION')
+ EXECUTE FUNCTION extensions.grant_pg_net_access();
+
+
+--
+-- Name: pgrst_ddl_watch; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER pgrst_ddl_watch ON ddl_command_end
+ EXECUTE FUNCTION extensions.pgrst_ddl_watch();
+
+
+--
+-- Name: pgrst_drop_watch; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER pgrst_drop_watch ON sql_drop
+ EXECUTE FUNCTION extensions.pgrst_drop_watch();
+
+
+--
+-- PostgreSQL database dump complete
+--
+
+
+--
+-- Dbmate schema migrations
+--
+
diff --git a/migrations/schema-16.sql b/migrations/schema-16.sql
new file mode 100644
index 00000000..49873a10
--- /dev/null
+++ b/migrations/schema-16.sql
@@ -0,0 +1,1064 @@
+SET statement_timeout = 0;
+SET lock_timeout = 0;
+SET idle_in_transaction_session_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SELECT pg_catalog.set_config('search_path', '', false);
+SET check_function_bodies = false;
+SET xmloption = content;
+SET client_min_messages = warning;
+SET row_security = off;
+
+--
+-- Name: auth; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA auth;
+
+
+--
+-- Name: extensions; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA extensions;
+
+
+--
+-- Name: graphql; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA graphql;
+
+
+--
+-- Name: graphql_public; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA graphql_public;
+
+
+--
+-- Name: pgbouncer; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA pgbouncer;
+
+
+--
+-- Name: pgsodium; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA pgsodium;
+
+
+--
+-- Name: pgsodium; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pgsodium WITH SCHEMA pgsodium;
+
+
+--
+-- Name: EXTENSION pgsodium; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pgsodium IS 'Pgsodium is a modern cryptography library for Postgres.';
+
+
+--
+-- Name: realtime; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA realtime;
+
+
+--
+-- Name: storage; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA storage;
+
+
+--
+-- Name: vault; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA vault;
+
+
+--
+-- Name: pg_graphql; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pg_graphql WITH SCHEMA graphql;
+
+
+--
+-- Name: EXTENSION pg_graphql; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pg_graphql IS 'pg_graphql: GraphQL support';
+
+
+--
+-- Name: pg_stat_statements; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA extensions;
+
+
+--
+-- Name: EXTENSION pg_stat_statements; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pg_stat_statements IS 'track planning and execution statistics of all SQL statements executed';
+
+
+--
+-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions;
+
+
+--
+-- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';
+
+
+--
+-- Name: pgjwt; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS pgjwt WITH SCHEMA extensions;
+
+
+--
+-- Name: EXTENSION pgjwt; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION pgjwt IS 'JSON Web Token API for Postgresql';
+
+
+--
+-- Name: supabase_vault; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS supabase_vault WITH SCHEMA vault;
+
+
+--
+-- Name: EXTENSION supabase_vault; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION supabase_vault IS 'Supabase Vault Extension';
+
+
+--
+-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
+--
+
+CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA extensions;
+
+
+--
+-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
+
+
+--
+-- Name: email(); Type: FUNCTION; Schema: auth; Owner: -
+--
+
+CREATE FUNCTION auth.email() RETURNS text
+ LANGUAGE sql STABLE
+ AS $$
+ select nullif(current_setting('request.jwt.claim.email', true), '')::text;
+$$;
+
+
+--
+-- Name: role(); Type: FUNCTION; Schema: auth; Owner: -
+--
+
+CREATE FUNCTION auth.role() RETURNS text
+ LANGUAGE sql STABLE
+ AS $$
+ select nullif(current_setting('request.jwt.claim.role', true), '')::text;
+$$;
+
+
+--
+-- Name: uid(); Type: FUNCTION; Schema: auth; Owner: -
+--
+
+CREATE FUNCTION auth.uid() RETURNS uuid
+ LANGUAGE sql STABLE
+ AS $$
+ select nullif(current_setting('request.jwt.claim.sub', true), '')::uuid;
+$$;
+
+
+--
+-- Name: grant_pg_cron_access(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.grant_pg_cron_access() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ IF EXISTS (
+ SELECT
+ FROM pg_event_trigger_ddl_commands() AS ev
+ JOIN pg_extension AS ext
+ ON ev.objid = ext.oid
+ WHERE ext.extname = 'pg_cron'
+ )
+ THEN
+ grant usage on schema cron to postgres with grant option;
+
+ alter default privileges in schema cron grant all on tables to postgres with grant option;
+ alter default privileges in schema cron grant all on functions to postgres with grant option;
+ alter default privileges in schema cron grant all on sequences to postgres with grant option;
+
+ alter default privileges for user supabase_admin in schema cron grant all
+ on sequences to postgres with grant option;
+ alter default privileges for user supabase_admin in schema cron grant all
+ on tables to postgres with grant option;
+ alter default privileges for user supabase_admin in schema cron grant all
+ on functions to postgres with grant option;
+
+ grant all privileges on all tables in schema cron to postgres with grant option;
+ revoke all on table cron.job from postgres;
+ grant select on table cron.job to postgres with grant option;
+ END IF;
+END;
+$$;
+
+
+--
+-- Name: FUNCTION grant_pg_cron_access(); Type: COMMENT; Schema: extensions; Owner: -
+--
+
+COMMENT ON FUNCTION extensions.grant_pg_cron_access() IS 'Grants access to pg_cron';
+
+
+--
+-- Name: grant_pg_graphql_access(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.grant_pg_graphql_access() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $_$
+DECLARE
+ func_is_graphql_resolve bool;
+BEGIN
+ func_is_graphql_resolve = (
+ SELECT n.proname = 'resolve'
+ FROM pg_event_trigger_ddl_commands() AS ev
+ LEFT JOIN pg_catalog.pg_proc AS n
+ ON ev.objid = n.oid
+ );
+
+ IF func_is_graphql_resolve
+ THEN
+ -- Update public wrapper to pass all arguments through to the pg_graphql resolve func
+ DROP FUNCTION IF EXISTS graphql_public.graphql;
+ create or replace function graphql_public.graphql(
+ "operationName" text default null,
+ query text default null,
+ variables jsonb default null,
+ extensions jsonb default null
+ )
+ returns jsonb
+ language sql
+ as $$
+ select graphql.resolve(
+ query := query,
+ variables := coalesce(variables, '{}'),
+ "operationName" := "operationName",
+ extensions := extensions
+ );
+ $$;
+
+ -- This hook executes when `graphql.resolve` is created. That is not necessarily the last
+ -- function in the extension so we need to grant permissions on existing entities AND
+ -- update default permissions to any others that are created after `graphql.resolve`
+ grant usage on schema graphql to postgres, anon, authenticated, service_role;
+ grant select on all tables in schema graphql to postgres, anon, authenticated, service_role;
+ grant execute on all functions in schema graphql to postgres, anon, authenticated, service_role;
+ grant all on all sequences in schema graphql to postgres, anon, authenticated, service_role;
+ alter default privileges in schema graphql grant all on tables to postgres, anon, authenticated, service_role;
+ alter default privileges in schema graphql grant all on functions to postgres, anon, authenticated, service_role;
+ alter default privileges in schema graphql grant all on sequences to postgres, anon, authenticated, service_role;
+
+ -- Allow postgres role to allow granting usage on graphql and graphql_public schemas to custom roles
+ grant usage on schema graphql_public to postgres with grant option;
+ grant usage on schema graphql to postgres with grant option;
+ END IF;
+
+END;
+$_$;
+
+
+--
+-- Name: FUNCTION grant_pg_graphql_access(); Type: COMMENT; Schema: extensions; Owner: -
+--
+
+COMMENT ON FUNCTION extensions.grant_pg_graphql_access() IS 'Grants access to pg_graphql';
+
+
+--
+-- Name: grant_pg_net_access(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.grant_pg_net_access() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ IF EXISTS (
+ SELECT 1
+ FROM pg_event_trigger_ddl_commands() AS ev
+ JOIN pg_extension AS ext
+ ON ev.objid = ext.oid
+ WHERE ext.extname = 'pg_net'
+ )
+ THEN
+ IF NOT EXISTS (
+ SELECT 1
+ FROM pg_roles
+ WHERE rolname = 'supabase_functions_admin'
+ )
+ THEN
+ CREATE USER supabase_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
+ END IF;
+
+ GRANT USAGE ON SCHEMA net TO supabase_functions_admin, postgres, anon, authenticated, service_role;
+
+ ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
+ ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
+
+ ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
+ ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
+
+ REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
+ REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
+
+ GRANT EXECUTE ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
+ GRANT EXECUTE ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
+ END IF;
+END;
+$$;
+
+
+--
+-- Name: FUNCTION grant_pg_net_access(); Type: COMMENT; Schema: extensions; Owner: -
+--
+
+COMMENT ON FUNCTION extensions.grant_pg_net_access() IS 'Grants access to pg_net';
+
+
+--
+-- Name: pgrst_ddl_watch(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.pgrst_ddl_watch() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ cmd record;
+BEGIN
+ FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands()
+ LOOP
+ IF cmd.command_tag IN (
+ 'CREATE SCHEMA', 'ALTER SCHEMA'
+ , 'CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO', 'ALTER TABLE'
+ , 'CREATE FOREIGN TABLE', 'ALTER FOREIGN TABLE'
+ , 'CREATE VIEW', 'ALTER VIEW'
+ , 'CREATE MATERIALIZED VIEW', 'ALTER MATERIALIZED VIEW'
+ , 'CREATE FUNCTION', 'ALTER FUNCTION'
+ , 'CREATE TRIGGER'
+ , 'CREATE TYPE', 'ALTER TYPE'
+ , 'CREATE RULE'
+ , 'COMMENT'
+ )
+ -- don't notify in case of CREATE TEMP table or other objects created on pg_temp
+ AND cmd.schema_name is distinct from 'pg_temp'
+ THEN
+ NOTIFY pgrst, 'reload schema';
+ END IF;
+ END LOOP;
+END; $$;
+
+
+--
+-- Name: pgrst_drop_watch(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.pgrst_drop_watch() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ obj record;
+BEGIN
+ FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
+ LOOP
+ IF obj.object_type IN (
+ 'schema'
+ , 'table'
+ , 'foreign table'
+ , 'view'
+ , 'materialized view'
+ , 'function'
+ , 'trigger'
+ , 'type'
+ , 'rule'
+ )
+ AND obj.is_temporary IS false -- no pg_temp objects
+ THEN
+ NOTIFY pgrst, 'reload schema';
+ END IF;
+ END LOOP;
+END; $$;
+
+
+--
+-- Name: set_graphql_placeholder(); Type: FUNCTION; Schema: extensions; Owner: -
+--
+
+CREATE FUNCTION extensions.set_graphql_placeholder() RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $_$
+ DECLARE
+ graphql_is_dropped bool;
+ BEGIN
+ graphql_is_dropped = (
+ SELECT ev.schema_name = 'graphql_public'
+ FROM pg_event_trigger_dropped_objects() AS ev
+ WHERE ev.schema_name = 'graphql_public'
+ );
+
+ IF graphql_is_dropped
+ THEN
+ create or replace function graphql_public.graphql(
+ "operationName" text default null,
+ query text default null,
+ variables jsonb default null,
+ extensions jsonb default null
+ )
+ returns jsonb
+ language plpgsql
+ as $$
+ DECLARE
+ server_version float;
+ BEGIN
+ server_version = (SELECT (SPLIT_PART((select version()), ' ', 2))::float);
+
+ IF server_version >= 14 THEN
+ RETURN jsonb_build_object(
+ 'errors', jsonb_build_array(
+ jsonb_build_object(
+ 'message', 'pg_graphql extension is not enabled.'
+ )
+ )
+ );
+ ELSE
+ RETURN jsonb_build_object(
+ 'errors', jsonb_build_array(
+ jsonb_build_object(
+ 'message', 'pg_graphql is only available on projects running Postgres 14 onwards.'
+ )
+ )
+ );
+ END IF;
+ END;
+ $$;
+ END IF;
+
+ END;
+$_$;
+
+
+--
+-- Name: FUNCTION set_graphql_placeholder(); Type: COMMENT; Schema: extensions; Owner: -
+--
+
+COMMENT ON FUNCTION extensions.set_graphql_placeholder() IS 'Reintroduces placeholder function for graphql_public.graphql';
+
+
+--
+-- Name: get_auth(text); Type: FUNCTION; Schema: pgbouncer; Owner: -
+--
+
+CREATE FUNCTION pgbouncer.get_auth(p_usename text) RETURNS TABLE(username text, password text)
+ LANGUAGE plpgsql SECURITY DEFINER
+ AS $$
+BEGIN
+ RAISE WARNING 'PgBouncer auth request: %', p_usename;
+
+ RETURN QUERY
+ SELECT usename::TEXT, passwd::TEXT FROM pg_catalog.pg_shadow
+ WHERE usename = p_usename;
+END;
+$$;
+
+
+--
+-- Name: extension(text); Type: FUNCTION; Schema: storage; Owner: -
+--
+
+CREATE FUNCTION storage.extension(name text) RETURNS text
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+_parts text[];
+_filename text;
+BEGIN
+ select string_to_array(name, '/') into _parts;
+ select _parts[array_length(_parts,1)] into _filename;
+ -- @todo return the last part instead of 2
+ return split_part(_filename, '.', 2);
+END
+$$;
+
+
+--
+-- Name: filename(text); Type: FUNCTION; Schema: storage; Owner: -
+--
+
+CREATE FUNCTION storage.filename(name text) RETURNS text
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+_parts text[];
+BEGIN
+ select string_to_array(name, '/') into _parts;
+ return _parts[array_length(_parts,1)];
+END
+$$;
+
+
+--
+-- Name: foldername(text); Type: FUNCTION; Schema: storage; Owner: -
+--
+
+CREATE FUNCTION storage.foldername(name text) RETURNS text[]
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+_parts text[];
+BEGIN
+ select string_to_array(name, '/') into _parts;
+ return _parts[1:array_length(_parts,1)-1];
+END
+$$;
+
+
+--
+-- Name: search(text, text, integer, integer, integer); Type: FUNCTION; Schema: storage; Owner: -
+--
+
+CREATE FUNCTION storage.search(prefix text, bucketname text, limits integer DEFAULT 100, levels integer DEFAULT 1, offsets integer DEFAULT 0) RETURNS TABLE(name text, id uuid, updated_at timestamp with time zone, created_at timestamp with time zone, last_accessed_at timestamp with time zone, metadata jsonb)
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+_bucketId text;
+BEGIN
+ -- will be replaced by migrations when server starts
+ -- saving space for cloud-init
+END
+$$;
+
+
+--
+-- Name: secrets_encrypt_secret_secret(); Type: FUNCTION; Schema: vault; Owner: -
+--
+
+CREATE FUNCTION vault.secrets_encrypt_secret_secret() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+ BEGIN
+ new.secret = CASE WHEN new.secret IS NULL THEN NULL ELSE
+ CASE WHEN new.key_id IS NULL THEN NULL ELSE pg_catalog.encode(
+ pgsodium.crypto_aead_det_encrypt(
+ pg_catalog.convert_to(new.secret, 'utf8'),
+ pg_catalog.convert_to((new.id::text || new.description::text || new.created_at::text || new.updated_at::text)::text, 'utf8'),
+ new.key_id::uuid,
+ new.nonce
+ ),
+ 'base64') END END;
+ RETURN new;
+ END;
+ $$;
+
+
+SET default_tablespace = '';
+
+SET default_table_access_method = heap;
+
+--
+-- Name: audit_log_entries; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.audit_log_entries (
+ instance_id uuid,
+ id uuid NOT NULL,
+ payload json,
+ created_at timestamp with time zone
+);
+
+
+--
+-- Name: TABLE audit_log_entries; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.audit_log_entries IS 'Auth: Audit trail for user actions.';
+
+
+--
+-- Name: instances; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.instances (
+ id uuid NOT NULL,
+ uuid uuid,
+ raw_base_config text,
+ created_at timestamp with time zone,
+ updated_at timestamp with time zone
+);
+
+
+--
+-- Name: TABLE instances; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.instances IS 'Auth: Manages users across multiple sites.';
+
+
+--
+-- Name: refresh_tokens; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.refresh_tokens (
+ instance_id uuid,
+ id bigint NOT NULL,
+ token character varying(255),
+ user_id character varying(255),
+ revoked boolean,
+ created_at timestamp with time zone,
+ updated_at timestamp with time zone
+);
+
+
+--
+-- Name: TABLE refresh_tokens; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.refresh_tokens IS 'Auth: Store of tokens used to refresh JWT tokens once they expire.';
+
+
+--
+-- Name: refresh_tokens_id_seq; Type: SEQUENCE; Schema: auth; Owner: -
+--
+
+CREATE SEQUENCE auth.refresh_tokens_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+--
+-- Name: refresh_tokens_id_seq; Type: SEQUENCE OWNED BY; Schema: auth; Owner: -
+--
+
+ALTER SEQUENCE auth.refresh_tokens_id_seq OWNED BY auth.refresh_tokens.id;
+
+
+--
+-- Name: schema_migrations; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.schema_migrations (
+ version character varying(255) NOT NULL
+);
+
+
+--
+-- Name: TABLE schema_migrations; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.schema_migrations IS 'Auth: Manages updates to the auth system.';
+
+
+--
+-- Name: users; Type: TABLE; Schema: auth; Owner: -
+--
+
+CREATE TABLE auth.users (
+ instance_id uuid,
+ id uuid NOT NULL,
+ aud character varying(255),
+ role character varying(255),
+ email character varying(255),
+ encrypted_password character varying(255),
+ confirmed_at timestamp with time zone,
+ invited_at timestamp with time zone,
+ confirmation_token character varying(255),
+ confirmation_sent_at timestamp with time zone,
+ recovery_token character varying(255),
+ recovery_sent_at timestamp with time zone,
+ email_change_token character varying(255),
+ email_change character varying(255),
+ email_change_sent_at timestamp with time zone,
+ last_sign_in_at timestamp with time zone,
+ raw_app_meta_data jsonb,
+ raw_user_meta_data jsonb,
+ is_super_admin boolean,
+ created_at timestamp with time zone,
+ updated_at timestamp with time zone
+);
+
+
+--
+-- Name: TABLE users; Type: COMMENT; Schema: auth; Owner: -
+--
+
+COMMENT ON TABLE auth.users IS 'Auth: Stores user login data within a secure schema.';
+
+
+--
+-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.schema_migrations (
+ version character varying(128) NOT NULL
+);
+
+
+--
+-- Name: buckets; Type: TABLE; Schema: storage; Owner: -
+--
+
+CREATE TABLE storage.buckets (
+ id text NOT NULL,
+ name text NOT NULL,
+ owner uuid,
+ created_at timestamp with time zone DEFAULT now(),
+ updated_at timestamp with time zone DEFAULT now()
+);
+
+
+--
+-- Name: migrations; Type: TABLE; Schema: storage; Owner: -
+--
+
+CREATE TABLE storage.migrations (
+ id integer NOT NULL,
+ name character varying(100) NOT NULL,
+ hash character varying(40) NOT NULL,
+ executed_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
+);
+
+
+--
+-- Name: objects; Type: TABLE; Schema: storage; Owner: -
+--
+
+CREATE TABLE storage.objects (
+ id uuid DEFAULT extensions.uuid_generate_v4() NOT NULL,
+ bucket_id text,
+ name text,
+ owner uuid,
+ created_at timestamp with time zone DEFAULT now(),
+ updated_at timestamp with time zone DEFAULT now(),
+ last_accessed_at timestamp with time zone DEFAULT now(),
+ metadata jsonb
+);
+
+
+--
+-- Name: decrypted_secrets; Type: VIEW; Schema: vault; Owner: -
+--
+
+CREATE VIEW vault.decrypted_secrets AS
+ SELECT id,
+ name,
+ description,
+ secret,
+ CASE
+ WHEN (secret IS NULL) THEN NULL::text
+ ELSE
+ CASE
+ WHEN (key_id IS NULL) THEN NULL::text
+ ELSE convert_from(pgsodium.crypto_aead_det_decrypt(decode(secret, 'base64'::text), convert_to(((((id)::text || description) || (created_at)::text) || (updated_at)::text), 'utf8'::name), key_id, nonce), 'utf8'::name)
+ END
+ END AS decrypted_secret,
+ key_id,
+ nonce,
+ created_at,
+ updated_at
+ FROM vault.secrets;
+
+
+--
+-- Name: refresh_tokens id; Type: DEFAULT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.refresh_tokens ALTER COLUMN id SET DEFAULT nextval('auth.refresh_tokens_id_seq'::regclass);
+
+
+--
+-- Name: audit_log_entries audit_log_entries_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.audit_log_entries
+ ADD CONSTRAINT audit_log_entries_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: instances instances_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.instances
+ ADD CONSTRAINT instances_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: refresh_tokens refresh_tokens_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.refresh_tokens
+ ADD CONSTRAINT refresh_tokens_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.schema_migrations
+ ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
+
+
+--
+-- Name: users users_email_key; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.users
+ ADD CONSTRAINT users_email_key UNIQUE (email);
+
+
+--
+-- Name: users users_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
+--
+
+ALTER TABLE ONLY auth.users
+ ADD CONSTRAINT users_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.schema_migrations
+ ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
+
+
+--
+-- Name: buckets buckets_pkey; Type: CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.buckets
+ ADD CONSTRAINT buckets_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: migrations migrations_name_key; Type: CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.migrations
+ ADD CONSTRAINT migrations_name_key UNIQUE (name);
+
+
+--
+-- Name: migrations migrations_pkey; Type: CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.migrations
+ ADD CONSTRAINT migrations_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: objects objects_pkey; Type: CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.objects
+ ADD CONSTRAINT objects_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: audit_logs_instance_id_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX audit_logs_instance_id_idx ON auth.audit_log_entries USING btree (instance_id);
+
+
+--
+-- Name: refresh_tokens_instance_id_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX refresh_tokens_instance_id_idx ON auth.refresh_tokens USING btree (instance_id);
+
+
+--
+-- Name: refresh_tokens_instance_id_user_id_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX refresh_tokens_instance_id_user_id_idx ON auth.refresh_tokens USING btree (instance_id, user_id);
+
+
+--
+-- Name: refresh_tokens_token_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX refresh_tokens_token_idx ON auth.refresh_tokens USING btree (token);
+
+
+--
+-- Name: users_instance_id_email_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX users_instance_id_email_idx ON auth.users USING btree (instance_id, email);
+
+
+--
+-- Name: users_instance_id_idx; Type: INDEX; Schema: auth; Owner: -
+--
+
+CREATE INDEX users_instance_id_idx ON auth.users USING btree (instance_id);
+
+
+--
+-- Name: bname; Type: INDEX; Schema: storage; Owner: -
+--
+
+CREATE UNIQUE INDEX bname ON storage.buckets USING btree (name);
+
+
+--
+-- Name: bucketid_objname; Type: INDEX; Schema: storage; Owner: -
+--
+
+CREATE UNIQUE INDEX bucketid_objname ON storage.objects USING btree (bucket_id, name);
+
+
+--
+-- Name: name_prefix_search; Type: INDEX; Schema: storage; Owner: -
+--
+
+CREATE INDEX name_prefix_search ON storage.objects USING btree (name text_pattern_ops);
+
+
+--
+-- Name: buckets buckets_owner_fkey; Type: FK CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.buckets
+ ADD CONSTRAINT buckets_owner_fkey FOREIGN KEY (owner) REFERENCES auth.users(id);
+
+
+--
+-- Name: objects objects_bucketId_fkey; Type: FK CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.objects
+ ADD CONSTRAINT "objects_bucketId_fkey" FOREIGN KEY (bucket_id) REFERENCES storage.buckets(id);
+
+
+--
+-- Name: objects objects_owner_fkey; Type: FK CONSTRAINT; Schema: storage; Owner: -
+--
+
+ALTER TABLE ONLY storage.objects
+ ADD CONSTRAINT objects_owner_fkey FOREIGN KEY (owner) REFERENCES auth.users(id);
+
+
+--
+-- Name: objects; Type: ROW SECURITY; Schema: storage; Owner: -
+--
+
+ALTER TABLE storage.objects ENABLE ROW LEVEL SECURITY;
+
+--
+-- Name: supabase_realtime; Type: PUBLICATION; Schema: -; Owner: -
+--
+
+CREATE PUBLICATION supabase_realtime WITH (publish = 'insert, update, delete, truncate');
+
+
+--
+-- Name: issue_graphql_placeholder; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER issue_graphql_placeholder ON sql_drop
+ WHEN TAG IN ('DROP EXTENSION')
+ EXECUTE FUNCTION extensions.set_graphql_placeholder();
+
+
+--
+-- Name: issue_pg_cron_access; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER issue_pg_cron_access ON ddl_command_end
+ WHEN TAG IN ('CREATE EXTENSION')
+ EXECUTE FUNCTION extensions.grant_pg_cron_access();
+
+
+--
+-- Name: issue_pg_graphql_access; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER issue_pg_graphql_access ON ddl_command_end
+ WHEN TAG IN ('CREATE FUNCTION')
+ EXECUTE FUNCTION extensions.grant_pg_graphql_access();
+
+
+--
+-- Name: issue_pg_net_access; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER issue_pg_net_access ON ddl_command_end
+ WHEN TAG IN ('CREATE EXTENSION')
+ EXECUTE FUNCTION extensions.grant_pg_net_access();
+
+
+--
+-- Name: pgrst_ddl_watch; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER pgrst_ddl_watch ON ddl_command_end
+ EXECUTE FUNCTION extensions.pgrst_ddl_watch();
+
+
+--
+-- Name: pgrst_drop_watch; Type: EVENT TRIGGER; Schema: -; Owner: -
+--
+
+CREATE EVENT TRIGGER pgrst_drop_watch ON sql_drop
+ EXECUTE FUNCTION extensions.pgrst_drop_watch();
+
+
+--
+-- PostgreSQL database dump complete
+--
+
+
+--
+-- Dbmate schema migrations
+--
+
diff --git a/migrations/schema.sql b/migrations/schema.sql
index f68d131e..1bff8b9d 100644
--- a/migrations/schema.sql
+++ b/migrations/schema.sql
@@ -737,7 +737,7 @@ COMMENT ON TABLE auth.users IS 'Auth: Stores user login data within a secure sch
--
CREATE TABLE public.schema_migrations (
- version character varying(255) NOT NULL
+ version character varying(128) NOT NULL
);
diff --git a/nix/ext/hypopg.nix b/nix/ext/hypopg.nix
index 4fc00a82..300a4490 100644
--- a/nix/ext/hypopg.nix
+++ b/nix/ext/hypopg.nix
@@ -16,7 +16,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp *.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/pg_backtrace.nix b/nix/ext/pg_backtrace.nix
index b0169124..47ede88e 100644
--- a/nix/ext/pg_backtrace.nix
+++ b/nix/ext/pg_backtrace.nix
@@ -18,7 +18,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp *.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/pg_cron.nix b/nix/ext/pg_cron.nix
index 5c546c79..d51254aa 100644
--- a/nix/ext/pg_cron.nix
+++ b/nix/ext/pg_cron.nix
@@ -16,7 +16,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp *.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/pg_hashids.nix b/nix/ext/pg_hashids.nix
index 41c3ba66..50bff95d 100644
--- a/nix/ext/pg_hashids.nix
+++ b/nix/ext/pg_hashids.nix
@@ -16,7 +16,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp *.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/pg_net.nix b/nix/ext/pg_net.nix
index bff869f3..dc923b9a 100644
--- a/nix/ext/pg_net.nix
+++ b/nix/ext/pg_net.nix
@@ -18,7 +18,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp sql/*.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/pg_plan_filter.nix b/nix/ext/pg_plan_filter.nix
index 2d7d224e..0ed52725 100644
--- a/nix/ext/pg_plan_filter.nix
+++ b/nix/ext/pg_plan_filter.nix
@@ -16,7 +16,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp *.sql $out/share/postgresql/extension
'';
diff --git a/nix/ext/pg_stat_monitor.nix b/nix/ext/pg_stat_monitor.nix
index 8784067d..c3e91fe2 100644
--- a/nix/ext/pg_stat_monitor.nix
+++ b/nix/ext/pg_stat_monitor.nix
@@ -33,7 +33,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp *.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/pg_tle.nix b/nix/ext/pg_tle.nix
index 6b1c7b1c..0221d876 100644
--- a/nix/ext/pg_tle.nix
+++ b/nix/ext/pg_tle.nix
@@ -21,7 +21,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp *.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/pgrouting.nix b/nix/ext/pgrouting.nix
index 36edf340..5587566c 100644
--- a/nix/ext/pgrouting.nix
+++ b/nix/ext/pgrouting.nix
@@ -14,9 +14,38 @@ stdenv.mkDerivation rec {
hash = "sha256-QC77AnPGpPQGEWi6JtJdiNsB2su5+aV2pKg5ImR2B0k=";
};
+ #disable compile time warnings for incompatible pointer types only on macos and pg16
+ NIX_CFLAGS_COMPILE = lib.optionalString (stdenv.isDarwin && lib.versionAtLeast postgresql.version "16")
+ "-Wno-error=int-conversion -Wno-error=incompatible-pointer-types";
+
+ cmakeFlags = [
+ "-DPOSTGRESQL_VERSION=${postgresql.version}"
+ ] ++ lib.optionals (stdenv.isDarwin && lib.versionAtLeast postgresql.version "16") [
+ "-DCMAKE_MACOSX_RPATH=ON"
+ "-DCMAKE_SHARED_MODULE_SUFFIX=.dylib"
+ "-DCMAKE_SHARED_LIBRARY_SUFFIX=.dylib"
+ ];
+
+ preConfigure = lib.optionalString (stdenv.isDarwin && lib.versionAtLeast postgresql.version "16") ''
+ export DLSUFFIX=.dylib
+ export CMAKE_SHARED_LIBRARY_SUFFIX=.dylib
+ export CMAKE_SHARED_MODULE_SUFFIX=.dylib
+ export MACOSX_RPATH=ON
+ '';
+
+ postBuild = lib.optionalString (stdenv.isDarwin && lib.versionAtLeast postgresql.version "16") ''
+ shopt -s nullglob
+ for file in lib/libpgrouting-*.so; do
+ if [ -f "$file" ]; then
+ mv "$file" "''${file%.so}.dylib"
+ fi
+ done
+ shopt -u nullglob
+ '';
+
installPhase = ''
- install -D lib/*.so -t $out/lib
- install -D sql/pgrouting--${version}.sql -t $out/share/postgresql/extension
+ install -D lib/*${postgresql.dlSuffix} -t $out/lib
+ install -D sql/pgrouting--*.sql -t $out/share/postgresql/extension
install -D sql/common/pgrouting.control -t $out/share/postgresql/extension
'';
diff --git a/nix/ext/pgsodium.nix b/nix/ext/pgsodium.nix
index e3b0da22..4e184faa 100644
--- a/nix/ext/pgsodium.nix
+++ b/nix/ext/pgsodium.nix
@@ -16,7 +16,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp sql/*.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/pgsql-http.nix b/nix/ext/pgsql-http.nix
index 2edaa9d5..de8fa68c 100644
--- a/nix/ext/pgsql-http.nix
+++ b/nix/ext/pgsql-http.nix
@@ -16,7 +16,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp *.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/pgvector.nix b/nix/ext/pgvector.nix
index 476f7606..aefeeea3 100644
--- a/nix/ext/pgvector.nix
+++ b/nix/ext/pgvector.nix
@@ -16,7 +16,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp *.so $out/lib
+ cp *${postgresql.dlSuffix} $out/lib
cp sql/*.sql $out/share/postgresql/extension
cp *.control $out/share/postgresql/extension
'';
diff --git a/nix/ext/plv8.nix b/nix/ext/plv8.nix
index 5cb4fb67..785f5e60 100644
--- a/nix/ext/plv8.nix
+++ b/nix/ext/plv8.nix
@@ -105,81 +105,17 @@ stdenv.mkDerivation (finalAttrs: {
rmdir "$out/nix/store"/* "$out/nix/store" "$out/nix"
${lib.optionalString stdenv.isDarwin ''
- install_name_tool -add_rpath "${v8}/lib" $out/lib/plv8-${finalAttrs.version}.so
- install_name_tool -add_rpath "${postgresql}/lib" $out/lib/plv8-${finalAttrs.version}.so
- install_name_tool -add_rpath "${stdenv.cc.cc.lib}/lib" $out/lib/plv8-${finalAttrs.version}.so
- install_name_tool -change @rpath/libv8_monolith.dylib ${v8}/lib/libv8_monolith.dylib $out/lib/plv8-${finalAttrs.version}.so
+ install_name_tool -add_rpath "${v8}/lib" $out/lib/plv8-${finalAttrs.version}${postgresql.dlSuffix}
+ install_name_tool -add_rpath "${postgresql}/lib" $out/lib/plv8-${finalAttrs.version}${postgresql.dlSuffix}
+ install_name_tool -add_rpath "${stdenv.cc.cc.lib}/lib" $out/lib/plv8-${finalAttrs.version}${postgresql.dlSuffix}
+ install_name_tool -change @rpath/libv8_monolith.dylib ${v8}/lib/libv8_monolith.dylib $out/lib/plv8-${finalAttrs.version}${postgresql.dlSuffix}
''}
${lib.optionalString (!stdenv.isDarwin) ''
- ${patchelf}/bin/patchelf --set-rpath "${v8}/lib:${postgresql}/lib:${stdenv.cc.cc.lib}/lib" $out/lib/plv8-${finalAttrs.version}.so
+ ${patchelf}/bin/patchelf --set-rpath "${v8}/lib:${postgresql}/lib:${stdenv.cc.cc.lib}/lib" $out/lib/plv8-${finalAttrs.version}${postgresql.dlSuffix}
''}
'';
- passthru = {
- tests =
- let
- postgresqlWithSelf = postgresql.withPackages (_: [
- finalAttrs.finalPackage
- ]);
- in {
- smoke = runCommand "plv8-smoke-test" {} ''
- export PATH=${lib.makeBinPath [
- postgresqlWithSelf
- coreutils
- gnugrep
- ]}
- db="$PWD/testdb"
- initdb "$db"
- postgres -k "$db" -D "$db" &
- pid="$!"
-
- for i in $(seq 1 100); do
- if psql -h "$db" -d postgres -c "" 2>/dev/null; then
- break
- elif ! kill -0 "$pid"; then
- exit 1
- else
- sleep 0.1
- fi
- done
-
- psql -h "$db" -d postgres -c 'CREATE EXTENSION plv8; DO $$ plv8.elog(NOTICE, plv8.version); $$ LANGUAGE plv8;' 2> "$out"
- grep -q "${finalAttrs.version}" "$out"
- kill -0 "$pid"
- '';
-
- regression = stdenv.mkDerivation {
- name = "plv8-regression";
- inherit (finalAttrs) src patches nativeBuildInputs buildInputs dontConfigure;
-
- buildPhase = ''
- runHook preBuild
-
- # The regression tests need to be run in the order specified in the Makefile.
- echo -e "include Makefile\nprint_regress_files:\n\t@echo \$(REGRESS)" > Makefile.regress
- REGRESS_TESTS=$(make -f Makefile.regress print_regress_files)
-
- ${postgresql}/lib/pgxs/src/test/regress/pg_regress \
- --bindir='${postgresqlWithSelf}/bin' \
- --temp-instance=regress-instance \
- --dbname=contrib_regression \
- $REGRESS_TESTS
-
- runHook postBuild
- '';
-
- installPhase = ''
- runHook preInstall
-
- touch "$out"
-
- runHook postInstall
- '';
- };
- };
- };
-
meta = with lib; {
description = "V8 Engine Javascript Procedural Language add-on for PostgreSQL";
homepage = "https://plv8.github.io/";
@@ -187,4 +123,4 @@ stdenv.mkDerivation (finalAttrs: {
platforms = [ "x86_64-linux" "aarch64-linux" "aarch64-darwin" ];
license = licenses.postgresql;
};
-})
\ No newline at end of file
+})
diff --git a/nix/ext/supautils.nix b/nix/ext/supautils.nix
index 9a32062b..ee1d691b 100644
--- a/nix/ext/supautils.nix
+++ b/nix/ext/supautils.nix
@@ -16,7 +16,7 @@ stdenv.mkDerivation rec {
installPhase = ''
mkdir -p $out/lib
- install -D supautils.so -t $out/lib
+ install -D *${postgresql.dlSuffix} -t $out/lib
'';
meta = with lib; {
diff --git a/nix/postgresql/16.nix b/nix/postgresql/16.nix
new file mode 100644
index 00000000..c964fc58
--- /dev/null
+++ b/nix/postgresql/16.nix
@@ -0,0 +1,4 @@
+import ./generic.nix {
+ version = "16.3";
+ hash = "sha256-Mxlj1dPcTK9CFqBJ+kC2bWvLjHMGFYWUEblRh2TmBYU=";
+}
diff --git a/nix/postgresql/default.nix b/nix/postgresql/default.nix
index 6ee0452d..5f92262b 100644
--- a/nix/postgresql/default.nix
+++ b/nix/postgresql/default.nix
@@ -3,6 +3,7 @@ let
#adapted from the postgresql nixpkgs package
versions = {
postgresql_15 = ./15.nix;
+ postgresql_16 = ./16.nix;
};
mkAttributes = jitSupport:
diff --git a/nix/tests/expected/extensions_sql_interface.out b/nix/tests/expected/extensions_sql_interface.out
index 5714fbce..07c0d665 100644
--- a/nix/tests/expected/extensions_sql_interface.out
+++ b/nix/tests/expected/extensions_sql_interface.out
@@ -146,6 +146,18 @@ from
on e.oid = d.refobjid
where
d.deptype = 'e'
+ -- Filter out changes between pg15 and pg16 from extensions that ship with postgres
+ -- new in pg16
+ and not (e.extname = 'fuzzystrmatch' and p.proname = 'daitch_mokotoff')
+ and not (e.extname = 'pageinspect' and p.proname = 'bt_multi_page_stats')
+ and not (e.extname = 'pg_buffercache' and p.proname = 'pg_buffercache_summary')
+ and not (e.extname = 'pg_buffercache' and p.proname = 'pg_buffercache_usage_counts')
+ and not (e.extname = 'pg_walinspect' and p.proname = 'pg_get_wal_block_info')
+ -- removed in pg16
+ and not (e.extname = 'pg_walinspect' and p.proname = 'pg_get_wal_records_info_till_end_of_wal')
+ and not (e.extname = 'pg_walinspect' and p.proname = 'pg_get_wal_stats_till_end_of_wal')
+ -- changed in pg16 - output signature added a column
+ and not (e.extname = 'pageinspect' and p.proname = 'brin_page_items')
order by
e.extname,
n.nspname,
@@ -1076,7 +1088,6 @@ order by
moddatetime | public | moddatetime | | trigger
old_snapshot | public | pg_old_snapshot_time_mapping | OUT array_offset integer, OUT end_timestamp timestamp with time zone, OUT newest_xmin xid | SETOF record
pageinspect | public | brin_metapage_info | page bytea, OUT magic text, OUT version integer, OUT pagesperrange integer, OUT lastrevmappage bigint | record
- pageinspect | public | brin_page_items | page bytea, index_oid regclass, OUT itemoffset integer, OUT blknum bigint, OUT attnum integer, OUT allnulls boolean, OUT hasnulls boolean, OUT placeholder boolean, OUT value text | SETOF record
pageinspect | public | brin_page_type | page bytea | text
pageinspect | public | brin_revmap_data | page bytea, OUT pages tid | SETOF tid
pageinspect | public | bt_metap | relname text, OUT magic integer, OUT version integer, OUT root bigint, OUT level bigint, OUT fastroot bigint, OUT fastlevel bigint, OUT last_cleanup_num_delpages bigint, OUT last_cleanup_num_tuples double precision, OUT allequalimage boolean | record
@@ -1300,9 +1311,7 @@ order by
pg_visibility | public | pg_visibility_map_summary | regclass, OUT all_visible bigint, OUT all_frozen bigint | record
pg_walinspect | public | pg_get_wal_record_info | in_lsn pg_lsn, OUT start_lsn pg_lsn, OUT end_lsn pg_lsn, OUT prev_lsn pg_lsn, OUT xid xid, OUT resource_manager text, OUT record_type text, OUT record_length integer, OUT main_data_length integer, OUT fpi_length integer, OUT description text, OUT block_ref text | record
pg_walinspect | public | pg_get_wal_records_info | start_lsn pg_lsn, end_lsn pg_lsn, OUT start_lsn pg_lsn, OUT end_lsn pg_lsn, OUT prev_lsn pg_lsn, OUT xid xid, OUT resource_manager text, OUT record_type text, OUT record_length integer, OUT main_data_length integer, OUT fpi_length integer, OUT description text, OUT block_ref text | SETOF record
- pg_walinspect | public | pg_get_wal_records_info_till_end_of_wal | start_lsn pg_lsn, OUT start_lsn pg_lsn, OUT end_lsn pg_lsn, OUT prev_lsn pg_lsn, OUT xid xid, OUT resource_manager text, OUT record_type text, OUT record_length integer, OUT main_data_length integer, OUT fpi_length integer, OUT description text, OUT block_ref text | SETOF record
pg_walinspect | public | pg_get_wal_stats | start_lsn pg_lsn, end_lsn pg_lsn, per_record boolean, OUT "resource_manager/record_type" text, OUT count bigint, OUT count_percentage double precision, OUT record_size bigint, OUT record_size_percentage double precision, OUT fpi_size bigint, OUT fpi_size_percentage double precision, OUT combined_size bigint, OUT combined_size_percentage double precision | SETOF record
- pg_walinspect | public | pg_get_wal_stats_till_end_of_wal | start_lsn pg_lsn, per_record boolean, OUT "resource_manager/record_type" text, OUT count bigint, OUT count_percentage double precision, OUT record_size bigint, OUT record_size_percentage double precision, OUT fpi_size bigint, OUT fpi_size_percentage double precision, OUT combined_size bigint, OUT combined_size_percentage double precision | SETOF record
pgaudit | public | pgaudit_ddl_command_end | | event_trigger
pgaudit | public | pgaudit_sql_drop | | event_trigger
pgcrypto | public | armor | bytea | text
@@ -5225,7 +5234,7 @@ order by
xml2 | public | xpath_table | text, text, text, text, text | SETOF record
xml2 | public | xslt_process | text, text | text
xml2 | public | xslt_process | text, text, text | text
-(5072 rows)
+(5069 rows)
/*
diff --git a/nix/tests/expected/pg_partman.out b/nix/tests/expected/pg_partman.out
index 527d58b1..25aa80d2 100644
--- a/nix/tests/expected/pg_partman.out
+++ b/nix/tests/expected/pg_partman.out
@@ -28,7 +28,10 @@ select
from
information_schema.tables
where
- table_schema = 'partman_test';
+ table_schema = 'partman_test'
+order by
+ table_name,
+ table_type;
table_name | table_type
-----------------------------+------------
time_taptest_table | BASE TABLE
diff --git a/nix/tests/sql/extensions_sql_interface.sql b/nix/tests/sql/extensions_sql_interface.sql
index 947a4a61..ad0f63ed 100644
--- a/nix/tests/sql/extensions_sql_interface.sql
+++ b/nix/tests/sql/extensions_sql_interface.sql
@@ -63,6 +63,18 @@ from
on e.oid = d.refobjid
where
d.deptype = 'e'
+ -- Filter out changes between pg15 and pg16 from extensions that ship with postgres
+ -- new in pg16
+ and not (e.extname = 'fuzzystrmatch' and p.proname = 'daitch_mokotoff')
+ and not (e.extname = 'pageinspect' and p.proname = 'bt_multi_page_stats')
+ and not (e.extname = 'pg_buffercache' and p.proname = 'pg_buffercache_summary')
+ and not (e.extname = 'pg_buffercache' and p.proname = 'pg_buffercache_usage_counts')
+ and not (e.extname = 'pg_walinspect' and p.proname = 'pg_get_wal_block_info')
+ -- removed in pg16
+ and not (e.extname = 'pg_walinspect' and p.proname = 'pg_get_wal_records_info_till_end_of_wal')
+ and not (e.extname = 'pg_walinspect' and p.proname = 'pg_get_wal_stats_till_end_of_wal')
+ -- changed in pg16 - output signature added a column
+ and not (e.extname = 'pageinspect' and p.proname = 'brin_page_items')
order by
e.extname,
n.nspname,
diff --git a/nix/tests/sql/pg_partman.sql b/nix/tests/sql/pg_partman.sql
index 621e1a7c..d1fb9f4b 100644
--- a/nix/tests/sql/pg_partman.sql
+++ b/nix/tests/sql/pg_partman.sql
@@ -35,7 +35,10 @@ select
from
information_schema.tables
where
- table_schema = 'partman_test';
+ table_schema = 'partman_test'
+order by
+ table_name,
+ table_type;
select public.create_parent(
diff --git a/scripts/nix-provision.sh b/scripts/nix-provision.sh
index 223e8492..c4776b3a 100644
--- a/scripts/nix-provision.sh
+++ b/scripts/nix-provision.sh
@@ -25,16 +25,23 @@ function install_nix() {
function execute_stage2_playbook {
+ echo "POSTGRES_MAJOR_VERSION: ${POSTGRES_MAJOR_VERSION}"
+ echo "GIT_SHA: ${GIT_SHA}"
sudo tee /etc/ansible/ansible.cfg <<EOF
[defaults]
callbacks_enabled = timer, profile_tasks, profile_roles
EOF
sed -i 's/- hosts: all/- hosts: localhost/' /tmp/ansible-playbook/ansible/playbook.yml
+
# Run Ansible playbook
export ANSIBLE_LOG_PATH=/tmp/ansible.log && export ANSIBLE_REMOTE_TEMP=/tmp
ansible-playbook /tmp/ansible-playbook/ansible/playbook.yml \
--extra-vars '{"nixpkg_mode": false, "stage2_nix": true, "debpkg_mode": false}' \
--extra-vars "git_commit_sha=${GIT_SHA}" \
+ --extra-vars "psql_version=psql_${POSTGRES_MAJOR_VERSION}" \
+ --extra-vars "postgresql_version=postgresql_${POSTGRES_MAJOR_VERSION}" \
+ --extra-vars "nix_secret_key=${NIX_SECRET_KEY}" \
+ --extra-vars "postgresql_major_version=${POSTGRES_MAJOR_VERSION}" \
$ARGS
}
diff --git a/stage2-nix-psql.pkr.hcl b/stage2-nix-psql.pkr.hcl
index 57ddfd9e..2897ffd3 100644
--- a/stage2-nix-psql.pkr.hcl
+++ b/stage2-nix-psql.pkr.hcl
@@ -46,6 +46,11 @@ variable "git_sha" {
default = env("GIT_SHA")
}
+variable "postgres_major_version" {
+ type = string
+ default = ""
+}
+
packer {
required_plugins {
amazon = {
@@ -133,7 +138,8 @@ build {
provisioner "shell" {
environment_vars = [
- "GIT_SHA=${var.git_sha}"
+ "GIT_SHA=${var.git_sha}",
+ "POSTGRES_MAJOR_VERSION=${var.postgres_major_version}"
]
script = "scripts/nix-provision.sh"
}
[parent: 578e2758f13b]