bump version to 14.8

Fixed integer type mapping in OpenAPI 2.0: replaced the invalid integer format with int32/int64 and added the toSwaggerFormat function to map PostgreSQL types to valid OpenAPI 2.0 formats:

smallint -> int32
integer -> int32
bigint -> int64

.github/actions/artifact-from-cirrus/action.yaml

@@ -112,7 +112,7 @@ runs:
         echo "artifacts=${artifacts}" >> "$GITHUB_OUTPUT"
     - name: Save artifact to GitHub Actions
       if: steps.find-task.outputs.task_found
-      uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
+      uses: actions/upload-artifact@bbbca2ddaa5d8feaa63e36b76fdaad77386f024f # v7.0.0
       with:
         name: ${{ inputs.upload }}
         path: ${{ steps.download.outputs.artifacts }}

.github/actions/cache-on-main/action.yaml

@@ -19,14 +19,14 @@ inputs:
 runs:
   using: composite
   steps:
-    - uses: actions/cache@0057852bfaa89a56745cba8c7296529d2fc39830 # v4.3.0
+    - uses: actions/cache@668228422ae6a00e4ad889ee87cd7109ec5666a7 # v5.0.4
       if: ${{ startsWith(github.ref, 'refs/heads/') || (inputs.save-prs && startsWith(github.ref, 'refs/pull/')) }}
       with:
         path: ${{ inputs.path }}
         key: ${{ runner.os }}-${{ inputs.prefix }}-${{ inputs.suffix }}
         restore-keys: |
           ${{ runner.os }}-${{ inputs.prefix }}-
-    - uses: actions/cache/restore@0057852bfaa89a56745cba8c7296529d2fc39830 # v4.3.0
+    - uses: actions/cache/restore@668228422ae6a00e4ad889ee87cd7109ec5666a7 # v5.0.4
       if: ${{ !startsWith(github.ref, 'refs/heads/') && !(inputs.save-prs && startsWith(github.ref, 'refs/pull/'))  }}
       with:
         path: ${{ inputs.path }}

.github/actions/setup-nix/action.yaml

@@ -16,7 +16,7 @@ runs:
         nix_conf: |-
           always-allow-substitutes = true
           max-jobs = auto
-    - uses: cachix/cachix-action@0fc020193b5a1fa3ac4575aa3a7d3aa6a35435ad # v16
+    - uses: cachix/cachix-action@1eb2ef646ac0255473d23a5907ad7b04ce94065c # v17
       with:
         name: postgrest
         authToken: ${{ inputs.authToken }}

.github/workflows/backport.yaml

@@ -28,7 +28,7 @@ jobs:
       # This actions creates the github token using the postgrest app secrets
       - name: Create Github App Token
         id: app-token
-        uses: actions/create-github-app-token@67018539274d69449ef7c02e8e71183d1719ab42 # v2.1.4
+        uses: actions/create-github-app-token@f8d387b68d61c58ab83c6c016672934102569859 # v3.0.0
         with:
           app-id: ${{ vars.POSTGREST_CI_APP_ID }}
           private-key: ${{ secrets.POSTGREST_CI_PRIVATE_KEY }}
@@ -38,14 +38,14 @@ jobs:
 
       # This is required for backport action to cherry-pick the PR
       - name: Fetch PR ref
-        uses: actions/checkout@ff7abcd0c3c05ccf6adc123a8cd1fd4fb30fb493
+        uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
         with:
           ref: ${{ github.event.pull_request.head.sha }}
           token: ${{ steps.app-token.outputs.token }}
 
       # Backport action that creates the PR with given settings
       - name: Create backport PR
-        uses: korthout/backport-action@d07416681cab29bf2661702f925f020aaa962997 # v3.4.1
+        uses: korthout/backport-action@3c06f323a58619da1e8522229ebc8d5de2633e46 # v4.3.0
         with:
           github_token: ${{ steps.app-token.outputs.token }}
           pull_description: 'Backport for #${pull_number}.'

.github/workflows/build.yaml

@@ -33,7 +33,7 @@ jobs:
     name: Nix - Linux x86-64 static
     runs-on: ubuntu-24.04
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -42,7 +42,7 @@ jobs:
       - name: Build static executable
         run: nix-build -A postgrestStatic -A postgrestStatic.tests
       - name: Save built executable as artifact
-        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
+        uses: actions/upload-artifact@bbbca2ddaa5d8feaa63e36b76fdaad77386f024f # v7.0.0
         with:
           name: postgrest-linux-static-x86-64
           path: result/bin/postgrest
@@ -51,7 +51,7 @@ jobs:
       - name: Build Docker image
         run: nix-build -A docker.image --out-link postgrest-docker.tar.gz
       - name: Save built Docker image as artifact
-        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
+        uses: actions/upload-artifact@bbbca2ddaa5d8feaa63e36b76fdaad77386f024f # v7.0.0
         with:
           name: postgrest-docker-x86-64
           path: postgrest-docker.tar.gz
@@ -62,7 +62,7 @@ jobs:
     name: Nix - MacOS
     runs-on: macos-15
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -105,15 +105,6 @@ jobs:
             artifact: postgrest-macos-aarch64
             deps: brew link --force libpq
 
-          - name: MacOS x86-64
-            runs-on: macos-13
-            cache: |
-              ~/.stack/pantry
-              ~/.stack/snapshots
-              ~/.stack/stack.sqlite3
-            artifact: postgrest-macos-x86-64
-            deps: brew link --force libpq
-
           - name: Windows
             runs-on: windows-2022
             cache: |
@@ -126,8 +117,8 @@ jobs:
     name: Stack - ${{ matrix.name }}
     runs-on: ${{ matrix.runs-on }}
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
-      - uses: haskell-actions/setup@82e8b5066385702e477d9dc98f287070493e8abd # v2.8.2
+      - uses: haskell-actions/setup@f9150cb1d140e9a9271700670baa38991e6fa25c # v2.10.3
         with:
           # This must match the version in stack.yaml's resolver
           ghc-version: 9.6.7
@@ -155,7 +146,7 @@ jobs:
       - name: Strip Executable
         run: strip result/postgrest*
       - name: Save built executable as artifact
-        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
+        uses: actions/upload-artifact@bbbca2ddaa5d8feaa63e36b76fdaad77386f024f # v7.0.0
         with:
           name: ${{ matrix.artifact }}
           path: |
@@ -168,7 +159,7 @@ jobs:
     name: Stack - FreeBSD from CirrusCI
     runs-on: ubuntu-24.04
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - uses: ./.github/actions/artifact-from-cirrus
         with:
           token: ${{ github.token }}
@@ -185,8 +176,8 @@ jobs:
     name: Cabal - Linux x86-64 - GHC ${{ matrix.ghc }}
     runs-on: ubuntu-24.04
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
-      - uses: haskell-actions/setup@82e8b5066385702e477d9dc98f287070493e8abd # v2.8.2
+      - uses: haskell-actions/setup@f9150cb1d140e9a9271700670baa38991e6fa25c # v2.10.3
         with:
           ghc-version: ${{ matrix.ghc }}
       - name: Cache .cabal

.github/workflows/check.yaml

@@ -20,7 +20,7 @@ jobs:
     name: Lint & Style
     runs-on: ubuntu-24.04
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -36,7 +36,7 @@ jobs:
     name: Commit
     runs-on: ubuntu-24.04
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
         with:
           fetch-depth: 100  # fetch history (last 100 commits) instead of default shallow clone history, this is deemed enough for a PR history
       - name: Setup Nix Environment

.github/workflows/ci.yaml

@@ -50,14 +50,14 @@ jobs:
       - test
       - build
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
         with:
           ssh-key: ${{ secrets.POSTGREST_SSH_KEY }}
       - name: Tag latest commit
         run: |
           cabal_version="$(grep -oP '^version:\s*\K.*' postgrest.cabal)"
 
-          if [[ "$cabal_version" == *.*.*.* ]]; then
+          if [[ "$cabal_version" == *.* ]]; then
             git fetch --tags
 
             if [ -z "$(git tag --list "v$cabal_version")" ]; then

.github/workflows/docs.yaml

@@ -27,7 +27,7 @@ jobs:
     name: Build
     runs-on: ubuntu-24.04
     steps:
-    - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+    - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
     - name: Setup Nix Environment
       uses: ./.github/actions/setup-nix
       with:
@@ -41,7 +41,7 @@ jobs:
     name: Spellcheck
     runs-on: ubuntu-24.04
     steps:
-    - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+    - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
     - name: Setup Nix Environment
       uses: ./.github/actions/setup-nix
       with:

.github/workflows/linkcheck.yaml

@@ -9,7 +9,7 @@ jobs:
   linkcheck:
     runs-on: ubuntu-24.04
     steps:
-    - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+    - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
     - name: Setup Nix Environment
       uses: ./.github/actions/setup-nix
       with:

.github/workflows/release.yaml

@@ -26,7 +26,7 @@ jobs:
     needs:
       - build
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - name: Check the version to be released
         run: |
           cabal_version="$(grep -oP '^version:\s*\K.*' postgrest.cabal)"
@@ -49,7 +49,7 @@ jobs:
           echo "Relevant extract from CHANGELOG.md:"
           cat CHANGES.md
       - name: Save CHANGES.md as artifact
-        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
+        uses: actions/upload-artifact@bbbca2ddaa5d8feaa63e36b76fdaad77386f024f # v7.0.0
         with:
           name: release-changes
           path: CHANGES.md
@@ -64,9 +64,9 @@ jobs:
     needs:
       - prepare
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - name: Download all artifacts
-        uses: actions/download-artifact@634f93cb2916e3fdff6788551b99b062d0335ce0 # v5.0.0
+        uses: actions/download-artifact@3e5f45b2cfb9172054b4087a40e8e0b5a5461e7c # v8.0.1
         with:
           path: artifacts
       - name: Create release bundle with archives for all builds
@@ -81,9 +81,6 @@ jobs:
           tar cJvf "release-bundle/postgrest-${GITHUB_REF_NAME}-macos-aarch64.tar.xz" \
             -C artifacts/postgrest-macos-aarch64 postgrest
 
-          tar cJvf "release-bundle/postgrest-${GITHUB_REF_NAME}-macos-x86-64.tar.xz" \
-            -C artifacts/postgrest-macos-x86-64 postgrest
-
           tar cJvf "release-bundle/postgrest-${GITHUB_REF_NAME}-freebsd-x86-64.tar.xz" \
             -C artifacts/postgrest-freebsd-x86-64 postgrest
 
@@ -94,7 +91,7 @@ jobs:
             artifacts/postgrest-windows-x86-64/postgrest.exe
 
       - name: Save release bundle
-        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
+        uses: actions/upload-artifact@bbbca2ddaa5d8feaa63e36b76fdaad77386f024f # v7.0.0
         with:
           name: release-bundle
           path: release-bundle
@@ -138,17 +135,17 @@ jobs:
     env:
       DOCKER_REPO: ${{ vars.DOCKER_REPO }}
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - name: Download x86-64 Docker image
-        uses: actions/download-artifact@634f93cb2916e3fdff6788551b99b062d0335ce0 # v5.0.0
+        uses: actions/download-artifact@3e5f45b2cfb9172054b4087a40e8e0b5a5461e7c # v8.0.1
         with:
           name: postgrest-docker-x86-64
       - name: Download aarch64 binary
-        uses: actions/download-artifact@634f93cb2916e3fdff6788551b99b062d0335ce0 # v5.0.0
+        uses: actions/download-artifact@3e5f45b2cfb9172054b4087a40e8e0b5a5461e7c # v8.0.1
         with:
           name: postgrest-ubuntu-aarch64
-      - uses: docker/setup-buildx-action@e468171a9de216ec08956ac3ada2f0791b6bd435 # v3.11.1
+      - uses: docker/setup-buildx-action@4d04d5d9486b7bd6fa91e7baf45bbb4f8b9deedd # v4.0.0
-      - uses: docker/login-action@5e57cd118135c172c3672efd75eb46360885c0ef # v3.6.0
+      - uses: docker/login-action@4907a6ddec9925e35a0a9e82d7399ccc52663121 # v4.1.0
         with:
           username: ${{ vars.DOCKER_USER }}
           password: ${{ secrets.DOCKER_PASS }}
@@ -194,7 +191,7 @@ jobs:
       vars.DOCKER_REPO && vars.DOCKER_USER &&
       github.ref == 'refs/tags/devel'
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - uses: peter-evans/dockerhub-description@1b9a80c056b620d92cedb9d9b5a223409c68ddfa # v5.0.0
         with:
           username: ${{ vars.DOCKER_USER }}

.github/workflows/test.yaml

@@ -39,7 +39,7 @@ jobs:
         # https://github.com/actions/runner/issues/241#issuecomment-842566950
         shell: script -qec "bash --noprofile --norc -eo pipefail {0}"
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -51,7 +51,7 @@ jobs:
       - name: Run coverage (IO tests and Spec tests against PostgreSQL 15)
         run: postgrest-coverage
       - name: Upload coverage to codecov
-        uses: codecov/codecov-action@5a1091511ad55cbe89839c7260b706298ca349f7 # v5.5.1
+        uses: codecov/codecov-action@57e3a136b779b570ffcdbf80b3bdc90e7fab3de2 # v6.0.0
         with:
           files: ./coverage/codecov.json
           token: ${{ secrets.CODECOV_TOKEN }}
@@ -78,33 +78,37 @@ jobs:
         # https://github.com/actions/runner/issues/241#issuecomment-842566950
         shell: script -qec "bash --noprofile --norc -eo pipefail {0}"
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
           authToken: '${{ secrets.CACHIX_AUTH_TOKEN }}'
-          tools: tests.testSpec.bin tests.testIO.bin tests.testBigSchema.bin withTools.postgresql-${{ matrix.pgVersion }}.bin cabalTools.update.bin
+          tools: tests.testSpec.bin tests.testObservability.bin tests.testIO.bin tests.testBigSchema.bin withTools.pg-${{ matrix.pgVersion }}.bin cabalTools.update.bin
 
       - run: postgrest-cabal-update
 
       - name: Run spec tests
         if: always()
-        run: postgrest-with-postgresql-${{ matrix.pgVersion }} postgrest-test-spec
+        run: postgrest-with-pg-${{ matrix.pgVersion }} postgrest-test-spec
+
+      - name: Run observability tests
+        if: always()
+        run: postgrest-with-pg-${{ matrix.pgVersion }} postgrest-test-observability
 
       - name: Run IO tests
         if: always()
-        run: postgrest-with-postgresql-${{ matrix.pgVersion }} postgrest-test-io -vv
+        run: postgrest-with-pg-${{ matrix.pgVersion }} postgrest-test-io -vv
 
       - name: Run IO tests on a big schema
         if: always()
-        run: postgrest-with-postgresql-${{ matrix.pgVersion }} postgrest-test-big-schema -vv
+        run: postgrest-with-pg-${{ matrix.pgVersion }} postgrest-test-big-schema -vv
 
 
   memory:
     name: Memory
     runs-on: ubuntu-24.04
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
       - name: Setup Nix Environment
         uses: ./.github/actions/setup-nix
         with:
@@ -124,7 +128,7 @@ jobs:
     name: Loadtest
     runs-on: ubuntu-24.04
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
         with:
           fetch-depth: 0
       - name: Setup Nix Environment
@@ -152,14 +156,13 @@ jobs:
       fail-fast: false
       matrix:
         runs-on:
-          - macos-13 # x86_64-darwin
           - macos-14 # aarch64-darwin
           - ubuntu-24.04 # x86_64-linux
           - ubuntu-24.04-arm # aarch64-linux
     name: Flake Check
     runs-on: ${{ matrix.runs-on }}
     steps:
-      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0
+      - uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
         with:
           fetch-depth: 0
       - name: Setup Nix Environment

BACKERS.md

@@ -13,26 +13,26 @@ PostgREST ongoing development is only possible thanks to our Sponsors and Backer
         </a>
       </td>
       <td align="center" valign="middle">
-        <a href="https://neon.tech/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
+        <a href="https://supabase.io?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage" target="_blank">
-          <img width="296px" src="static/neon.jpg">
+          <img width="296px" src="static/supabase.svg">
         </a>
       </td>
       <td align="center" valign="middle">
-        <a href="https://tembo.io/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
+        <a href="https://www.euronodes.com/postgrest" target="_blank">
-          <img width="296px" src="static/tembo.png">
+          <img width="296px" src="static/euronodes.svg">
         </a>
       </td>
     </tr>
     <tr></tr>
     <tr>
       <td align="center" valign="middle">
-        <a href="https://www.euronodes.com/postgrest" target="_blank">
+        <a href="https://neon.tech/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
-          <img width="296px" src="static/euronodes.svg">
+          <img width="296px" src="static/neon.jpg">
         </a>
       </td>
       <td align="center" valign="middle">
-        <a href="https://supabase.io?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage" target="_blank">
+        <a href="https://www.bytebase.com/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
-          <img width="296px" src="static/supabase.png">
+          <img width="296px" src="static/bytebase.svg">
         </a>
       </td>
     </tr>
@@ -93,6 +93,11 @@ PostgREST ongoing development is only possible thanks to our Sponsors and Backer
           <img width="222px" src="static/code-build.png">
         </a>
       </td>
+      <td align="center" valign="middle">
+        <a href="https://tembo.io/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
+          <img width="296px" src="static/tembo.png">
+        </a>
+      </td>
     </tr>
   </tbody>
 </table>

CHANGELOG.md

@@ -1,10 +1,71 @@
 # Change Log
 
-All notable changes to this project will be documented in this file.
+All notable changes to this project will be documented in this file. From version `14.0` onwards PostgREST follows a `MAJOR.PATCH` two-part versioning. Only even-numbered MAJOR versions will be released, reserving odd-numbered MAJOR versions for development.
-This project adheres to [Semantic Versioning](http://semver.org/).
 
 ## Unreleased
 
+## [14.8] - 2026-04-03
+
+### Added
+
+- Log a `HINT` when the LISTEN channel stops working due to a PostgreSQL bug by @laurenceisla in #4581
+
+### Fixed
+
+- Fix invalid OpenAPI 2.0 format for integer types (`smallint`, `integer`, `bigint`) by @arturbent0 in #4641
+
+## [14.7] - 2026-03-20
+
+### Fixed
+
+- Fix not logging SIGTERM and SIGINT by @steve-chavez in #4728
+
+## [14.6] - 2026-03-06
+
+### Fixed
+
+- Fix leaking table and function names when calculating error hint by @taimoorzaeem in #4675
+
+## [14.5] - 2026-02-12
+
+### Fixed
+
+- Don't hide async exceptions in logs by @stevechavez in #4646
+
+## [14.4] - 2026-01-29
+
+### Fixed
+
+- Ensure Listener connections are released by @mkleczek in #4614
+- Fix incorrectly filtering the returned representation for PATCH requests when using `or/and` filters by @laurenceisla in #3707
+- Fix listener running with exception masked after first failure by @mkleczek #4615
+
+## [14.3] - 2026-01-03
+
+### Fixed
+
+- Fix performance and high memory usage of relation hint calculation by @mkleczek in #4462, #4463
+
+## [14.2] - 2025-12-18
+
+### Fixed
+
+- Fix `hasSingleUnnamedParam` incorrectly matching functions with named parameters by @joelonsql in #4553
+  + Functions with a single named parameter (e.g., `foo(data json)`) no longer incorrectly match the single-param fallback, returning a clean `PGRST202` error instead of a confusing PostgreSQL `42883` error.
+- Fix misleading logs on unsupported PostgreSQL versions by @taimoorzaeem in #4519
+- Fix regression where the `PGRST103` error response was truncated by @laurenceisla in #4455
+  + Happened when an `offset` was greater than the rows requested and `Prefer: count=exact` was sent.
+- Fix not returning `Content-Length` on empty HTTP `201` responses by @laurenceisla in #4518
+- Fix inaccurate Server-Timing header durations by @steve-chavez in #4522
+- Fix inaccurate "Schema cache queried" logs by @steve-chavez in #4522
+
+## [14.1] - 2025-11-05
+
+## Fixed
+
+- Fix `db-pre-config` function failing when function names are pg reserved words by @taimoorzaeem in #4380
+- Fix `server-host=!6` incorrectly binds to IPv4 address by @taimoorzaeem in #3202
+
 ## [14.0] - 2025-10-24
 
 ### Added

Dockerfile

@@ -2,7 +2,7 @@
 # The x86-64 is a single-static-binary image built via Nix, see:
 # nix/tools/docker/README.md
 
-FROM ubuntu:noble@sha256:66460d557b25769b102175144d538d88219c077c678a49af4afca6fbfc1b5252 AS postgrest
+FROM ubuntu:noble@sha256:186072bba1b2f436cbb91ef2567abca677337cfc786c86e107d25b7072feef0c AS postgrest
 
 RUN apt-get update -y \
     && apt install -y --no-install-recommends libpq-dev zlib1g-dev jq gcc libnuma-dev \

README.md

@@ -22,26 +22,26 @@ API than you are likely to write from scratch.
         </a>
       </td>
       <td align="center" valign="middle">
-        <a href="https://neon.tech/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
+        <a href="https://supabase.io?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage" target="_blank">
-          <img width="296px" src="static/neon.jpg">
+          <img width="296px" src="static/supabase.svg">
         </a>
       </td>
       <td align="center" valign="middle">
-        <a href="https://tembo.io/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
+        <a href="https://www.euronodes.com/postgrest" target="_blank">
-          <img width="296px" src="static/tembo.png">
+          <img width="296px" src="static/euronodes.svg">
         </a>
       </td>
     </tr>
     <tr></tr>
     <tr>
       <td align="center" valign="middle">
-        <a href="https://www.euronodes.com/postgrest" target="_blank">
+        <a href="https://neon.tech/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
-          <img width="296px" src="static/euronodes.svg">
+          <img width="296px" src="static/neon.jpg">
         </a>
       </td>
       <td align="center" valign="middle">
-        <a href="https://supabase.io?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage" target="_blank">
+        <a href="https://www.bytebase.com/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
-          <img width="296px" src="static/supabase.png">
+          <img width="296px" src="static/bytebase.svg">
         </a>
       </td>
     </tr>

cabal.project.freeze

@@ -1 +1 @@
-index-state: hackage.haskell.org 2025-10-13T04:53:27Z
+index-state: hackage.haskell.org 2025-10-29T04:02:18Z

default.nix

@@ -53,11 +53,11 @@ let
 
   postgresqlVersions =
     [
-      { name = "postgresql-17"; postgresql = pkgs.postgresql_17.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
+      { name = "pg-17"; postgresql = pkgs.postgresql_17.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
-      { name = "postgresql-16"; postgresql = pkgs.postgresql_16.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
+      { name = "pg-16"; postgresql = pkgs.postgresql_16.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
-      { name = "postgresql-15"; postgresql = pkgs.postgresql_15.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
+      { name = "pg-15"; postgresql = pkgs.postgresql_15.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
-      { name = "postgresql-14"; postgresql = pkgs.postgresql_14.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
+      { name = "pg-14"; postgresql = pkgs.postgresql_14.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
-      { name = "postgresql-13"; postgresql = pkgs.postgresql_13.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
+      { name = "pg-13"; postgresql = pkgs.postgresql_13.withPackages (p: [ p.postgis p.pg_safeupdate ]); }
     ];
 
   haskellPackages = pkgs.haskell.packages."${compiler}";

docker-hub-readme.md

@@ -19,26 +19,26 @@ write from scratch.
         </a>
       </td>
       <td align="center" valign="middle">
-        <a href="https://neon.tech/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
+        <a href="https://supabase.io?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage" target="_blank">
-          <img width="296px" src="https://raw.githubusercontent.com/PostgREST/postgrest/main/static/neon.jpg">
+          <img width="296px" src="https://raw.githubusercontent.com/PostgREST/postgrest/main/static/supabase.svg">
         </a>
       </td>
       <td align="center" valign="middle">
-        <a href="https://tembo.io/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
+        <a href="https://www.euronodes.com/postgrest" target="_blank">
-          <img width="296px" src="https://raw.githubusercontent.com/PostgREST/postgrest/main/static/tembo.png">
+          <img width="296px" src="https://raw.githubusercontent.com/PostgREST/postgrest/main/static/euronodes.svg">
         </a>
       </td>
     </tr>
     <tr></tr>
     <tr>
       <td align="center" valign="middle">
-        <a href="https://www.euronodes.com/postgrest" target="_blank">
+        <a href="https://neon.tech/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
-          <img width="296px" src="https://raw.githubusercontent.com/PostgREST/postgrest/main/static/euronodes.svg">
+          <img width="296px" src="https://raw.githubusercontent.com/PostgREST/postgrest/main/static/neon.jpg">
         </a>
       </td>
       <td align="center" valign="middle">
-        <a href="https://supabase.io?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage" target="_blank">
+        <a href="https://www.bytebase.com/?utm_source=sponsor&utm_campaign=postgrest" target="_blank">
-          <img width="296px" src="https://raw.githubusercontent.com/PostgREST/postgrest/main/static/supabase.png">
+          <img width="296px" src="https://raw.githubusercontent.com/PostgREST/postgrest/main/static/bytebase.svg">
         </a>
       </td>
     </tr>

docs/conf.py

@@ -302,6 +302,7 @@ linkcheck_ignore = [
     r"https://www.cybertec-postgresql.com/.*",
     # Odd SSL error
     r"https://www.dripdepot.com",
+    r"https://www.euronodes.com",
     # New GitHub UI delays comment load, so anchor fails
     r"https://github.com/.*#issuecomment",
     # Random 500 Internal Server Error

docs/explanations/install.rst

@@ -16,7 +16,7 @@ Supported PostgreSQL versions
 =============================
 
 =============== =================================
-**Supported**   PostgreSQL >= 12
+**Supported**   PostgreSQL >= 13
 =============== =================================
 
 PostgREST works with all PostgreSQL versions still `officially supported <https://www.postgresql.org/support/versioning/>`_.
@@ -146,7 +146,7 @@ To avoid having to install the database at all, you can run both it and the serv
       ports:
         - "3000:3000"
       environment:
-        PGRST_SERVER_HOST: localhost # necessary for `postgrest --ready` flag to work
+        PGRST_SERVER_HOST: 0.0.0.0 # necessary for `postgrest --ready` flag to work
         PGRST_DB_URI: postgres://app_user:password@db:5432/app_db
         PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
       depends_on:

docs/index.rst

@@ -38,6 +38,28 @@ Sponsors
     .. image:: ../static/cybertec.svg
       :target: https://www.cybertec-postgresql.com/en/?utm_source=postgrest.org&utm_medium=referral&utm_campaign=postgrest
 
+  .. container:: img-dark
+
+    .. image:: ../static/supabase-dark.svg
+      :target: https://supabase.com/?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage
+
+  .. container:: img-light
+
+    .. image:: ../static/supabase.svg
+      :target: https://supabase.com/?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage
+
+  .. container:: img-dark
+
+    .. image:: ../static/euronodes.svg
+      :target: https://www.euronodes.com/postgrest
+
+  .. container:: img-light
+
+    .. image:: ../static/euronodes.svg
+      :target: https://www.euronodes.com/postgrest
+
+  |
+
   .. container:: img-dark
 
     .. image:: ../static/neon-dark.jpg
@@ -48,30 +70,15 @@ Sponsors
     .. image:: ../static/neon.jpg
       :target: https://neon.com/?utm_source=sponsor&utm_campaign=postgrest
 
-  .. image:: ../static/tembo.png
-    :target: https://www.tembo.io/?utm_source=sponsor&utm_campaign=postgrest
-
-  |
-
   .. container:: img-dark
 
-    .. image:: ../static/euronodes.svg
+    .. image:: ../static/bytebase-dark.svg
-      :target: https://www.euronodes.com/postgrest
+      :target: https://www.bytebase.com/?utm_source=sponsor&utm_campaign=postgrest
 
   .. container:: img-light
 
-    .. image:: ../static/euronodes.svg
+    .. image:: ../static/bytebase.svg
-      :target: https://www.euronodes.com/postgrest
+      :target: https://www.bytebase.com/?utm_source=sponsor&utm_campaign=postgrest
-
-  .. container:: img-dark
-
-    .. image:: ../static/supabase-dark.png
-      :target: https://supabase.com/?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage
-
-  .. container:: img-light
-
-    .. image:: ../static/supabase.png
-      :target: https://supabase.com/?utm_source=postgrest%20backers&utm_medium=open%20source%20partner&utm_campaign=postgrest%20backers%20github&utm_term=homepage
 
   .. The static/empty.png(created with `convert -size 320x95 xc:#fcfcfc empty.png`) is an ugly workaround
      to create space and center the logos. It's not easy to layout with restructuredText.
@@ -213,7 +220,6 @@ In Production
 Here are some companies that use PostgREST in production.
 
 * `Catarse <https://www.catarse.me>`_
-* `Datrium <https://www.datrium.com>`_
 * `Drip Depot <https://www.dripdepot.com>`_
 * `Image-charts <https://www.image-charts.com>`_
 * `Netwo <https://www.netwo.io>`_

docs/postgrest.dict

@@ -194,3 +194,5 @@ Websockets
 webuser
 wfts
 www
+debouncing
+deduplicates

docs/references/observability.rst

@@ -120,7 +120,7 @@ Restart the database and watch the log file in real-time to understand how HTTP
 Metrics
 =======
 
-The ``metrics`` endpoint on the :ref:`admin_server` endpoint provides metrics in `Prometheus text format <https://prometheus.io/docs/instrumenting/exposition_formats/#text-based-format>`_.
+The ``metrics`` endpoint on the :ref:`admin_server` endpoint provides metrics in `Prometheus text format <https://prometheus.io/docs/instrumenting/exposition_formats/#prometheus-text-format>`_.
 
 .. code-block:: bash
 

docs/references/schema_cache.rst

@@ -53,6 +53,19 @@ To reload the schema cache from within the database, you can use the ``NOTIFY``
 
   NOTIFY pgrst, 'reload schema'
 
+Debouncing
+~~~~~~~~~~
+
+PostgREST does not reload the schema cache for each notification when several ``NOTIFY pgrst`` events are generated quickly after one another.
+
+There are two cases to consider: when notifications are sent within a single transaction and when they are sent across multiple transactions.
+
+In the first case, PostgreSQL deduplicates identical ``NOTIFY`` events within the same transaction. This means that even if multiple ``NOTIFY pgrst`` statements are executed before a ``COMMIT``, only a single notification is delivered to PostgREST.
+
+In the second case, when notifications are sent from separate transactions in a short time span, PostgREST applies a debouncing mechanism to avoid excessive schema cache reloads.
+
+Instead of reloading the schema cache for each notification, events are grouped within a small time window of 100 milliseconds. The reload function is executed once immediately when the first notification is received and once more after the burst of events settles, resulting in at most two executions within that time window.
+
 .. _auto_schema_reloading:
 
 Automatic Schema Cache Reloading

nix/README.md

@@ -91,11 +91,11 @@ postgrest-gen-ctags               postgrest-watch
 postgrest-gen-jwt                 postgrest-with-all
 postgrest-gen-secret              postgrest-with-git
 postgrest-git-hooks               postgrest-with-pgrst
-postgrest-hsie-graph-modules      postgrest-with-postgresql-13
+postgrest-hsie-graph-modules      postgrest-with-pg-13
-postgrest-hsie-graph-symbols      postgrest-with-postgresql-14
+postgrest-hsie-graph-symbols      postgrest-with-pg-14
-postgrest-hsie-minimal-imports    postgrest-with-postgresql-15
+postgrest-hsie-minimal-imports    postgrest-with-pg-15
-postgrest-lint                    postgrest-with-postgresql-16
+postgrest-lint                    postgrest-with-pg-16
-postgrest-loadtest                postgrest-with-postgresql-17
+postgrest-loadtest                postgrest-with-pg-17
 postgrest-loadtest-against        postgrest-with-slow-pg
 postgrest-loadtest-report         postgrest-with-slow-postgrest
 postgrest-nixpkgs-upgrade
@@ -174,7 +174,7 @@ $ nix-shell --run "postgrest-with-all postgrest-test-spec"
 
 # Run the tests against a specific version of PostgreSQL (use tab-completion in
 # nix-shell to see all available versions):
-$ nix-shell --run "postgrest-with-postgresql-13 postgrest-test-spec"
+$ nix-shell --run "postgrest-with-pg-13 postgrest-test-spec"
 
 ```
 
@@ -284,7 +284,7 @@ Tools like `postgrest-build`, `postgrest-run`, `postgrest-repl` etc. are simple
 also run in CI, with the exception of the IO and Memory checks that need to be run
 separately.
 
-`postgrest-with-postgresql-*` take a command as an argument and will run it
+`postgrest-with-pg-*` take a command as an argument and will run it
 with a temporary database. `postgrest-with-all` will run the command against
 all supported PostgreSQL versions. Tests run without `postgrest-with-*` are
 run against the latest PostgreSQL version by default.

nix/overlays/checked-shell-script/checked-shell-script.nix

@@ -104,8 +104,7 @@ let
         ''
 
         + lib.optionalString withTmpDir ''
-          mkdir -p "''${TMPDIR:-/tmp}/postgrest"
+          tmpdir="$(${coreutils}/bin/mktemp -d --tmpdir ${name}-XXX)"
-          tmpdir="$(${coreutils}/bin/mktemp -d --tmpdir postgrest/${name}-XXX)"
 
           # we keep the tmpdir when an error occurs for debugging
           trap 'echo Temporary directory kept at: $tmpdir' ERR

nix/overlays/haskell-packages.nix

@@ -60,6 +60,16 @@ let
           }
           { };
 
+      # TODO: Remove once available in nixpkgs haskellPackages
+      streaming-commons =
+        prev.callHackageDirect
+          {
+            pkg = "streaming-commons";
+            ver = "0.2.3.1";
+            sha256 = "sha256-Gl2eaJcWe1sxmcE/octWlH9uSnERguf+5H66K4fV87s=";
+          }
+          { };
+
       # Downgrade hasql and related packages while we are still on GHC 9.4 for the static build.
       hasql = lib.dontCheck (lib.doJailbreak prev.hasql_1_6_4_4);
       hasql-dynamic-statements = lib.dontCheck prev.hasql-dynamic-statements_0_3_1_5;

nix/tools/devTools.nix

@@ -82,6 +82,7 @@ let
       }
       ''
         ${tests}/bin/postgrest-test-spec
+        ${tests}/bin/postgrest-test-observability
         ${tests}/bin/postgrest-test-doctests
         ${tests}/bin/postgrest-test-io
         ${tests}/bin/postgrest-test-big-schema

nix/tools/style.nix

@@ -12,6 +12,7 @@
 , silver-searcher
 , statix
 , stylish-haskell
+, writeText
 }:
 let
   style =
@@ -51,6 +52,20 @@ let
         ${git}/bin/git diff-index --exit-code HEAD -- '*.hs' '*.lhs' '*.nix' '*.py'
       '';
 
+  hlintConfig = writeText "hlintConfig.yml" ''
+
+    # Arguments passed to hlint
+    - arguments: [-j, -XQuasiQuotes, -XNoPatternSynonyms]
+
+    # Warnings
+    - warn: { lhs: "a == a", rhs: "True",  note: "This comparison always evaluates to True" }
+    - warn: { lhs: "a /= a", rhs: "False", note: "This comparison always evaluates to False" }
+    - warn: { lhs: "a < a",  rhs: "False", note: "This comparison always evaluates to False" }
+    - warn: { lhs: "a > a",  rhs: "False", note: "This comparison always evaluates to False" }
+    - warn: { lhs: "a <= a", rhs: "True",  note: "This comparison always evaluates to True" }
+    - warn: { lhs: "a >= a", rhs: "True",  note: "This comparison always evaluates to True" }
+  '';
+
   lint =
     checkedShellScript
       {
@@ -79,7 +94,7 @@ let
         echo "Linting Haskell files..."
         # --vimgrep fixes a bug in ag: https://github.com/ggreer/the_silver_searcher/issues/753
         ${silver-searcher}/bin/ag -l --vimgrep -g '\.l?hs$' . \
-          | xargs ${hlint}/bin/hlint -j -X QuasiQuotes -X NoPatternSynonyms
+          | xargs ${hlint}/bin/hlint --hint=${hlintConfig}
       '';
 
 in

nix/tools/tests.nix

@@ -32,6 +32,20 @@ let
           ${cabal-install}/bin/cabal v2-run ${devCabalOptions} test:spec -- "''${_arg_leftovers[@]}"
       '';
 
+  testObservability =
+    checkedShellScript
+      {
+        name = "postgrest-test-observability";
+        docs = "Run the Haskell observability test suite.";
+        args = [ "ARG_LEFTOVERS([hspec arguments])" ];
+        workingDir = "/";
+        withEnv = postgrest.env;
+      }
+      ''
+        ${withTools.withPg} -f test/observability/fixtures/load.sql \
+          ${cabal-install}/bin/cabal v2-run ${devCabalOptions} test:observability -- "''${_arg_leftovers[@]}"
+      '';
+
   testDoctests =
     checkedShellScript
       {
@@ -83,7 +97,7 @@ let
       }
       ''
         ${cabal-install}/bin/cabal v2-build ${devCabalOptions} exe:postgrest
-        ${cabal-install}/bin/cabal v2-exec -- ${withTools.withPg} -f test/io/fixtures.sql \
+        ${cabal-install}/bin/cabal v2-exec -- ${withTools.withPg} -f test/io/fixtures/load.sql \
           ${ioTestPython}/bin/pytest --ignore=test/io/test_big_schema.py --ignore=test/io/test_replica.py -v test/io "''${_arg_leftovers[@]}"
       '';
 
@@ -98,7 +112,7 @@ let
       }
       ''
         ${cabal-install}/bin/cabal v2-build ${devCabalOptions} exe:postgrest
-        ${cabal-install}/bin/cabal v2-exec -- ${withTools.withPg} -f test/io/big_schema.sql \
+        ${cabal-install}/bin/cabal v2-exec -- ${withTools.withPg} -f test/io/fixtures/big_schema.sql \
           ${ioTestPython}/bin/pytest -v test/io/test_big_schema.py "''${_arg_leftovers[@]}"
       '';
 
@@ -113,7 +127,7 @@ let
       }
       ''
         ${cabal-install}/bin/cabal v2-build ${devCabalOptions} exe:postgrest
-        ${cabal-install}/bin/cabal v2-exec -- ${withTools.withPg} --replica -f test/io/replica.sql \
+        ${cabal-install}/bin/cabal v2-exec -- ${withTools.withPg} --replica -f test/io/fixtures/replica.sql \
           ${ioTestPython}/bin/pytest -v test/io/test_replica.py "''${_arg_leftovers[@]}"
       '';
 
@@ -155,7 +169,7 @@ let
           rm -rf coverage/*
 
           # build once before running all the tests
-          ${cabal-install}/bin/cabal v2-build ${devCabalOptions} exe:postgrest lib:postgrest test:spec
+          ${cabal-install}/bin/cabal v2-build ${devCabalOptions} exe:postgrest lib:postgrest test:spec test:observability
 
           (
             trap 'echo Found dead code: Check file list above.' ERR ;
@@ -164,26 +178,31 @@ let
 
           # collect all tests
           HPCTIXFILE="$tmpdir"/io.tix \
-            ${withTools.withPg} -f test/io/fixtures.sql \
+            ${withTools.withPg} -f test/io/fixtures/load.sql \
             ${cabal-install}/bin/cabal v2-exec ${devCabalOptions} -- ${ioTestPython}/bin/pytest --ignore=test/io/test_big_schema.py --ignore=test/io/test_replica.py -v test/io
 
           HPCTIXFILE="$tmpdir"/big_schema.tix \
-            ${withTools.withPg} -f test/io/big_schema.sql \
+            ${withTools.withPg} -f test/io/fixtures/big_schema.sql \
             ${cabal-install}/bin/cabal v2-exec ${devCabalOptions} -- ${ioTestPython}/bin/pytest -v test/io/test_big_schema.py
 
           HPCTIXFILE="$tmpdir"/replica.tix \
-            ${withTools.withPg} --replica -f test/io/replica.sql \
+            ${withTools.withPg} --replica -f test/io/fixtures/replica.sql \
             ${cabal-install}/bin/cabal v2-exec ${devCabalOptions} -- ${ioTestPython}/bin/pytest -v test/io/test_replica.py
 
           HPCTIXFILE="$tmpdir"/spec.tix \
             ${withTools.withPg} -f test/spec/fixtures/load.sql \
             ${cabal-install}/bin/cabal v2-run ${devCabalOptions} test:spec
 
+          HPCTIXFILE="$tmpdir"/observability.tix \
+            ${withTools.withPg} -f test/observability/fixtures/load.sql \
+            ${cabal-install}/bin/cabal v2-run ${devCabalOptions} test:observability
+
           # Note: No coverage for doctests, as doctests leverage GHCi and GHCi does not support hpc
 
           # collect all the tix files
           ${ghc}/bin/hpc sum  --union --exclude=Paths_postgrest --output="$tmpdir"/tests.tix \
-            "$tmpdir"/io*.tix "$tmpdir"/big_schema*.tix "$tmpdir"/replica*.tix "$tmpdir"/spec.tix
+            "$tmpdir"/io*.tix "$tmpdir"/big_schema*.tix "$tmpdir"/replica*.tix "$tmpdir"/spec.tix \
+            "$tmpdir"/observability.tix
 
           # prepare the overlay
           ${ghc}/bin/hpc overlay --output="$tmpdir"/overlay.tix test/coverage.overlay
@@ -250,6 +269,7 @@ buildToolbox
   tools = {
     inherit
       testSpec
+      testObservability
       testDoctests
       testSpecIdempotence
       testIO

nix/tools/withTools.nix

@@ -46,7 +46,7 @@ let
         }
 
         # Avoid starting multiple layers of withTmpDb, but make sure to have the last invocation
-        # load fixtures. Otherwise postgrest-with-postgresql-xx postgrest-test-io would not be possible.
+        # load fixtures. Otherwise postgrest-with-pg-xx postgrest-test-io would not be possible.
         if ! test -v PGHOST; then
 
           mkdir -p "$tmpdir"/{db,socket}
@@ -74,7 +74,13 @@ let
             >> "$setuplog"
 
           log "Starting the database cluster..."
+
           # Instead of listening on a local port, we will listen on a unix domain socket.
+          # NOTE: unix domain socket filename name must remain under max limit.
+          # On Linux, it's 108 chars (including '\0' terminator)
+          # On MacOS, it's 104 chars
+          # See: https://serverfault.com/questions/641347/check-if-a-path-exceeds-maximum-for-unix-domain-socket
+
           pg_ctl -l "$tmpdir/db.log" -w start -o "-F -c listen_addresses=\"\" -c hba_file=$HBA_FILE -k $PGHOST -c log_statement=\"all\" " \
             >> "$setuplog"
 
@@ -433,7 +439,7 @@ buildToolbox
       withSlowPg
       withSlowPgrst;
   } // builtins.listToAttrs (
-    # Create a `postgrest-with-postgresql-` for each PostgreSQL version
+    # Create a `postgrest-with-pg-` for each PostgreSQL version
     builtins.map (pg: { inherit (pg) name; value = withTmpDb pg; }) postgresqlVersions
   );
   # make latest withPg available for other nix files

postgrest.cabal

@@ -1,5 +1,5 @@
 name:               postgrest
-version:            14.0
+version:            14.8
 synopsis:           REST API for any Postgres database
 description:        Reads the schema of a PostgreSQL database and creates RESTful routes
                     for tables, views, and functions, supporting all HTTP methods that security
@@ -94,6 +94,7 @@ library
                       PostgREST.Response.OpenAPI
                       PostgREST.Response.GucHeader
                       PostgREST.Response.Performance
+                      PostgREST.TimeIt
                       PostgREST.Version
   build-depends:      base                      >= 4.9 && < 4.20
                     , HTTP                      >= 4000.3.7 && < 4000.5
@@ -102,10 +103,8 @@ library
                     , auto-update               >= 0.1.4 && < 0.3
                     , base64-bytestring         >= 1 && < 1.3
                     , bytestring                >= 0.10.8 && < 0.13
-                    , cache                     >= 0.1.3 && < 0.2.0
                     , case-insensitive          >= 1.2 && < 1.3
                     , cassava                   >= 0.4.5 && < 0.6
-                    , clock                     >= 0.8.3 && < 0.9.0
                     , configurator-pg           >= 0.2.11 && < 0.3
                     , containers                >= 0.5.7 && < 0.7
                     , cookie                    >= 0.4.2 && < 0.6
@@ -118,11 +117,9 @@ library
                     , hasql-notifications       >= 0.2.2.2 && < 0.2.3
                     , hasql-pool                >= 1.0.1 && < 1.1
                     , hasql-transaction         >= 1.0.1 && < 1.2
-                    , heredoc                   >= 0.2 && < 0.3
                     , http-client               >= 0.7.19 && < 0.8
                     , http-types                >= 0.12.2 && < 0.13
                     , insert-ordered-containers >= 0.2.2 && < 0.3
-                    , iproute                   >= 1.7.0 && < 1.8
                     , jose-jwt                  >= 0.9.6 && < 0.11
                     , lens                      >= 4.14 && < 5.4
                     , lens-aeson                >= 1.0.1 && < 1.3
@@ -139,11 +136,10 @@ library
                     , regex-tdfa                >= 1.2.2 && < 1.4
                     , retry                     >= 0.7.4 && < 0.10
                     , scientific                >= 0.3.4 && < 0.4
-                    , streaming-commons         >= 0.1.1 && < 0.3
+                    , streaming-commons         >= 0.2.3.1 && < 0.3
                     , swagger2                  >= 2.4 && < 2.9
                     , text                      >= 1.2.2 && < 2.2
                     , time                      >= 1.6 && < 1.13
-                    , timeit                    >= 2.0 && < 2.1
                     , unordered-containers      >= 0.2.8 && < 0.3
                     , unix-compat               >= 0.5.4 && < 0.8
                     , vault                     >= 0.3.1.5 && < 0.4
@@ -215,7 +211,6 @@ test-suite spec
                       Feature.Auth.AudienceJwtSecretSpec
                       Feature.Auth.AuthSpec
                       Feature.Auth.BinaryJwtSecretSpec
-                      Feature.Auth.JwtCacheSpec
                       Feature.Auth.NoAnonSpec
                       Feature.Auth.NoJwtSecretSpec
                       Feature.ConcurrentSpec
@@ -298,6 +293,39 @@ test-suite spec
                       -- https://github.com/PostgREST/postgrest/issues/387
                       -with-rtsopts=-K33K
 
+test-suite observability
+  type:               exitcode-stdio-1.0
+  default-language:   Haskell2010
+  default-extensions: OverloadedStrings
+                      QuasiQuotes
+                      NoImplicitPrelude
+  hs-source-dirs:     test/observability
+  main-is:            Main.hs
+  other-modules:      ObsHelper
+                      Observation.JwtCache
+                      Observation.MetricsSpec
+  build-depends:      base              >= 4.9 && < 4.20
+                    , base64-bytestring >= 1 && < 1.3
+                    , bytestring        >= 0.10.8 && < 0.13
+                    , hasql-pool        >= 1.0.1 && < 1.1
+                    , hasql-transaction >= 1.0.1 && < 1.2
+                    , hspec             >= 2.3 && < 2.12
+                    , hspec-expectations >= 0.8.4 && < 0.9
+                    , hspec-wai         >= 0.10 && < 0.12
+                    , hspec-wai-json    >= 0.10 && < 0.12
+                    , http-types        >= 0.12.3 && < 0.13
+                    , jose-jwt          >= 0.9.6 && < 0.11
+                    , postgrest
+                    , prometheus-client >= 1.1.1 && < 1.2.0
+                    , protolude         >= 0.3.1 && < 0.4
+                    , text              >= 1.2.2 && < 2.2
+                    , wai               >= 3.2.1 && < 3.3
+  ghc-options:        -threaded -O0 -Werror -Wall -fwarn-identities
+                      -fno-spec-constr -optP-Wno-nonportable-include-path
+                      -fwrite-ide-info
+                      -- https://github.com/PostgREST/postgrest/issues/387
+                      -with-rtsopts=-K33K
+
 test-suite doctests
   type:               exitcode-stdio-1.0
   default-language:   Haskell2010

src/PostgREST/Admin.hs

@@ -19,22 +19,23 @@ import PostgREST.Observation (Observation (..))
 
 import qualified PostgREST.AppState as AppState
 
-import Protolude
+import qualified Network.Socket as NS
+import           Protolude
 
-runAdmin :: AppState -> Warp.Settings -> IO ()
+runAdmin :: AppState -> Maybe NS.Socket -> NS.Socket -> Warp.Settings -> IO ()
-runAdmin appState settings = do
+runAdmin appState maybeAdminSocket socketREST settings = do
-  whenJust (AppState.getSocketAdmin appState) $ \adminSocket -> do
+  whenJust maybeAdminSocket $ \adminSocket -> do
     address <- resolveSocketToAddress adminSocket
     observer $ AdminStartObs address
     void . forkIO $ Warp.runSettingsSocket settings adminSocket adminApp
   where
-    adminApp = admin appState
+    adminApp = admin appState socketREST
     observer = AppState.getObserver appState
 
 -- | PostgREST admin application
-admin :: AppState.AppState -> Wai.Application
+admin :: AppState.AppState -> NS.Socket -> Wai.Application
-admin appState req respond  = do
+admin appState socketREST req respond  = do
-  isMainAppReachable  <- isRight <$> reachMainApp (AppState.getSocketREST appState)
+  isMainAppReachable  <- isRight <$> reachMainApp socketREST
   isLoaded <- AppState.isLoaded appState
   isPending <- AppState.isPending appState
 

src/PostgREST/App.hs

@@ -9,18 +9,24 @@ Some of its functionality includes:
 - Producing HTTP Headers according to RFCs.
 - Content Negotiation
 -}
-{-# LANGUAGE RecordWildCards #-}
+{-# LANGUAGE RecordWildCards     #-}
+{-# LANGUAGE ScopedTypeVariables #-}
+{-# LANGUAGE ViewPatterns        #-}
 module PostgREST.App
   ( postgrest
   , run
   ) where
 
 
+import GHC.IO.Exception (IOErrorType (..))
+import System.IO.Error  (ioeGetErrorType)
+
 import Control.Monad.Except     (liftEither)
 import Data.Either.Combinators  (mapLeft, whenLeft)
 import Data.Maybe               (fromJust)
 import Data.String              (IsString (..))
-import Network.Wai.Handler.Warp (defaultSettings, setHost, setPort,
+import Network.Wai.Handler.Warp (defaultSettings, setHost,
+                                 setOnException, setPort,
                                  setServerName)
 
 import qualified Data.Text.Encoding       as T
@@ -51,35 +57,58 @@ import PostgREST.Observation          (Observation (..))
 import PostgREST.Response.Performance (ServerTiming (..),
                                        serverTimingHeader)
 import PostgREST.SchemaCache          (SchemaCache (..))
+import PostgREST.TimeIt               (timeItT)
 import PostgREST.Version              (docsVersion, prettyVersion)
 
-import qualified Data.ByteString.Char8 as BS
+import qualified Data.ByteString.Char8  as BS
-import qualified Data.List             as L
+import qualified Data.List              as L
-import qualified Network.HTTP.Types    as HTTP
+import           Data.Streaming.Network (bindPortTCP,
-import           Protolude             hiding (Handler)
+                                         bindRandomPortTCP)
-import           System.TimeIt         (timeItT)
+import qualified Data.Text              as T
+import qualified Network.HTTP.Types     as HTTP
+import qualified Network.Socket         as NS
+import           PostgREST.Unix         (createAndBindDomainSocket)
+import           Protolude              hiding (Handler)
 
 type Handler = ExceptT Error
 
 run :: AppState -> IO ()
 run appState = do
-  let observer = AppState.getObserver appState
   conf@AppConfig{..} <- AppState.getConfig appState
 
   AppState.schemaCacheLoader appState -- Loads the initial SchemaCache
-  Unix.installSignalHandlers (AppState.getMainThreadId appState) (AppState.schemaCacheLoader appState) (AppState.readInDbConfig False appState)
+  (mainSocket, adminSocket) <- initSockets conf
+
+  Unix.installSignalHandlers observer (AppState.getMainThreadId appState) (AppState.schemaCacheLoader appState) (AppState.readInDbConfig False appState)
 
   Listener.runListener appState
 
-  Admin.runAdmin appState (serverSettings conf)
+  Admin.runAdmin appState adminSocket mainSocket (serverSettings conf)
 
   let app = postgrest configLogLevel appState (AppState.schemaCacheLoader appState)
 
   do
-    address <- resolveSocketToAddress (AppState.getSocketREST appState)
+    address <- resolveSocketToAddress mainSocket
     observer $ AppServerAddressObs address
 
-  Warp.runSettingsSocket (serverSettings conf) (AppState.getSocketREST appState) app
+  Warp.runSettingsSocket (serverSettings conf & setOnException onWarpException) mainSocket app
+  where
+    observer = AppState.getObserver appState
+
+    onWarpException :: Maybe Wai.Request -> SomeException -> IO ()
+    onWarpException _ ex =
+      when (shouldDisplayException ex) $
+        observer $ WarpErrorObs $ show ex
+
+    -- Similar to wai defaultShouldDisplayException in
+    -- https://github.com/yesodweb/wai//blob/8c3882c60f6abe043889fc20c7efd3fa9747fa4a/warp/Network/Wai/Handler/Warp/Settings.hs#L251-L258
+    -- but without omitting AsyncException since it's important to log for ThreadKilled, StackOverflow and other cases.
+    -- We want to reuse this to avoid flooding the logs for some transient failure cases.
+    shouldDisplayException :: SomeException -> Bool
+    shouldDisplayException se
+        | Just (_ :: Warp.InvalidRequest) <- fromException se = False
+        | Just (ioeGetErrorType -> et) <- fromException se, et == ResourceVanished || et == InvalidArgument = False
+        | otherwise = True
 
 serverSettings :: AppConfig -> Warp.Settings
 serverSettings AppConfig{..} =
@@ -206,3 +235,40 @@ addRetryHint delay response = do
 
 isServiceUnavailable :: Wai.Response -> Bool
 isServiceUnavailable response = Wai.responseStatus response == HTTP.status503
+
+type AppSockets = (NS.Socket, Maybe NS.Socket)
+
+initSockets :: AppConfig -> IO AppSockets
+initSockets AppConfig{..} = do
+  let
+    cfg'usp = configServerUnixSocket
+    cfg'uspm = configServerUnixSocketMode
+    cfg'host = configServerHost
+    cfg'port = configServerPort
+    cfg'adminHost = configAdminServerHost
+    cfg'adminPort = configAdminServerPort
+
+  sock <- case cfg'usp of
+    -- I'm not using `streaming-commons`' bindPath function here because it's not defined for Windows,
+    -- but we need to have runtime error if we try to use it in Windows, not compile time error
+    Just path -> createAndBindDomainSocket path cfg'uspm
+    Nothing -> do
+      (_, sock) <-
+        if cfg'port /= 0
+          then do
+            sock <- bindPortTCP cfg'port (fromString $ T.unpack cfg'host)
+            pure (cfg'port, sock)
+          else do
+            -- explicitly bind to a random port, returning bound port number
+            (num, sock) <- bindRandomPortTCP (fromString $ T.unpack cfg'host)
+            pure (num, sock)
+      pure sock
+
+  adminSock <- case cfg'adminPort of
+    Just adminPort -> do
+      adminSock <- bindPortTCP adminPort (fromString $ T.unpack cfg'adminHost)
+      pure $ Just adminSock
+    Nothing -> pure Nothing
+
+  pure (sock, adminSock)
+

src/PostgREST/AppState.hs

@@ -13,11 +13,9 @@ module PostgREST.AppState
   , getNextListenerDelay
   , getTime
   , getJwtCacheState
-  , getSocketREST
-  , getSocketAdmin
   , init
-  , initSockets
   , initWithPool
+  , putConfig -- For tests TODO refactoring
   , putNextListenerDelay
   , putSchemaCache
   , putPgVersion
@@ -32,20 +30,18 @@ module PostgREST.AppState
 
 import qualified Data.ByteString.Char8      as BS
 import           Data.Either.Combinators    (whenLeft)
-import qualified Data.Text                  as T (unpack)
 import qualified Hasql.Pool                 as SQL
 import qualified Hasql.Pool.Config          as SQL
 import qualified Hasql.Session              as SQL
 import qualified Hasql.Transaction.Sessions as SQL
 import qualified Network.HTTP.Types.Status  as HTTP
-import qualified Network.Socket             as NS
 import qualified PostgREST.Auth.JwtCache    as JwtCache
 import qualified PostgREST.Error            as Error
 import qualified PostgREST.Logger           as Logger
 import qualified PostgREST.Metrics          as Metrics
 import           PostgREST.Observation
+import           PostgREST.TimeIt           (timeItT)
 import           PostgREST.Version          (prettyVersion)
-import           System.TimeIt              (timeItT)
 
 import Control.AutoUpdate (defaultUpdateSettings, mkAutoUpdate,
                            updateAction)
@@ -69,11 +65,8 @@ import PostgREST.Config.PgVersion        (PgVersion (..),
 import PostgREST.SchemaCache             (SchemaCache (..),
                                           querySchemaCache,
                                           showSummary)
-import PostgREST.SchemaCache.Identifiers (dumpQi)
+import PostgREST.SchemaCache.Identifiers (quoteQi)
-import PostgREST.Unix                    (createAndBindDomainSocket)
 
-import Data.Streaming.Network (bindPortTCP, bindRandomPortTCP)
-import Data.String            (IsString (..))
 import Protolude
 
 data AppState = AppState
@@ -99,10 +92,6 @@ data AppState = AppState
   , stateNextDelay         :: IORef Int
   -- | Keeps track of the next delay for the listener
   , stateNextListenerDelay :: IORef Int
-  -- | Network socket for REST API
-  , stateSocketREST        :: NS.Socket
-  -- | Network socket for the admin UI
-  , stateSocketAdmin       :: Maybe NS.Socket
   -- | Observation handler
   , stateObserver          :: ObservationHandler
   -- | JWT Cache
@@ -117,8 +106,6 @@ data SchemaCacheStatus
   | SCPending
   deriving Eq
 
-type AppSockets = (NS.Socket, Maybe NS.Socket)
-
 init :: AppConfig -> IO AppState
 init conf@AppConfig{configLogLevel, configDbPoolSize} = do
   loggerState  <- Logger.init
@@ -128,12 +115,10 @@ init conf@AppConfig{configLogLevel, configDbPoolSize} = do
   observer $ AppStartObs prettyVersion
 
   pool <- initPool conf observer
-  (sock, adminSock) <- initSockets conf
+  initWithPool pool conf loggerState metricsState observer --{ stateSocketREST = sock, stateSocketAdmin = adminSock}
-  state' <- initWithPool (sock, adminSock) pool conf loggerState metricsState observer
-  pure state' { stateSocketREST = sock, stateSocketAdmin = adminSock}
 
-initWithPool :: AppSockets -> SQL.Pool -> AppConfig -> Logger.LoggerState -> Metrics.MetricsState -> ObservationHandler -> IO AppState
+initWithPool :: SQL.Pool -> AppConfig -> Logger.LoggerState -> Metrics.MetricsState -> ObservationHandler -> IO AppState
-initWithPool (sock, adminSock) pool conf loggerState metricsState observer = do
+initWithPool pool conf loggerState metricsState observer = do
 
   appState <- AppState pool
     <$> newIORef minimumPgVersion -- assume we're in a supported version when starting, this will be corrected on a later step
@@ -146,8 +131,6 @@ initWithPool (sock, adminSock) pool conf loggerState metricsState observer = do
     <*> myThreadId
     <*> newIORef 0
     <*> newIORef 1
-    <*> pure sock
-    <*> pure adminSock
     <*> pure observer
     <*> JwtCache.init conf observer
     <*> pure loggerState
@@ -166,40 +149,6 @@ initWithPool (sock, adminSock) pool conf loggerState metricsState observer = do
 destroy :: AppState -> IO ()
 destroy = destroyPool
 
-initSockets :: AppConfig -> IO AppSockets
-initSockets AppConfig{..} = do
-  let
-    cfg'usp = configServerUnixSocket
-    cfg'uspm = configServerUnixSocketMode
-    cfg'host = configServerHost
-    cfg'port = configServerPort
-    cfg'adminHost = configAdminServerHost
-    cfg'adminPort = configAdminServerPort
-
-  sock <- case cfg'usp of
-    -- I'm not using `streaming-commons`' bindPath function here because it's not defined for Windows,
-    -- but we need to have runtime error if we try to use it in Windows, not compile time error
-    Just path -> createAndBindDomainSocket path cfg'uspm
-    Nothing -> do
-      (_, sock) <-
-        if cfg'port /= 0
-          then do
-            sock <- bindPortTCP cfg'port (fromString $ T.unpack cfg'host)
-            pure (cfg'port, sock)
-          else do
-            -- explicitly bind to a random port, returning bound port number
-            (num, sock) <- bindRandomPortTCP (fromString $ T.unpack cfg'host)
-            pure (num, sock)
-      pure sock
-
-  adminSock <- case cfg'adminPort of
-    Just adminPort -> do
-      adminSock <- bindPortTCP adminPort (fromString $ T.unpack cfg'adminHost)
-      pure $ Just adminSock
-    Nothing -> pure Nothing
-
-  pure (sock, adminSock)
-
 initPool :: AppConfig -> ObservationHandler -> IO SQL.Pool
 initPool AppConfig{..} observer = do
   SQL.acquire $ SQL.settings
@@ -313,12 +262,6 @@ getTime = stateGetTime
 getJwtCacheState :: AppState -> JwtCacheState
 getJwtCacheState = stateJwtCache
 
-getSocketREST :: AppState -> NS.Socket
-getSocketREST = stateSocketREST
-
-getSocketAdmin :: AppState -> Maybe NS.Socket
-getSocketAdmin = stateSocketAdmin
-
 getMainThreadId :: AppState -> ThreadId
 getMainThreadId = stateMainThreadId
 
@@ -382,14 +325,16 @@ retryingSchemaCacheLoad appState@AppState{stateObserver=observer, stateMainThrea
             observer ExitDBNoRecoveryObs
             killThread mainThreadId
           return Nothing
-        Right actualPgVersion -> do
+        Right actualPgVersion ->
-          when (actualPgVersion < minimumPgVersion) $ do
+          if actualPgVersion < minimumPgVersion then do
             observer $ ExitUnsupportedPgVersion actualPgVersion minimumPgVersion
             killThread mainThreadId
-          observer $ DBConnectedObs $ pgvFullName actualPgVersion
+            return Nothing
-          observer $ PoolInit configDbPoolSize
+          else do
-          putPgVersion appState actualPgVersion
+            observer $ DBConnectedObs $ pgvFullName actualPgVersion
-          return $ Just actualPgVersion
+            observer $ PoolInit configDbPoolSize
+            putPgVersion appState actualPgVersion
+            return $ Just actualPgVersion
 
     qInDbConfig :: IO ()
     qInDbConfig = do
@@ -441,7 +386,7 @@ readInDbConfig startingUp appState@AppState{stateObserver=observer} = do
   pgVer <- getPgVersion appState
   dbSettings <-
     if configDbConfig conf then do
-      qDbSettings <- usePool appState (queryDbSettings (dumpQi <$> configDbPreConfig conf) (configDbPreparedStatements conf))
+      qDbSettings <- usePool appState (queryDbSettings (quoteQi <$> configDbPreConfig conf) (configDbPreparedStatements conf))
       case qDbSettings of
         Left e -> do
           observer $ ConfigReadErrorObs e

src/PostgREST/Auth.hs

@@ -25,8 +25,8 @@ import qualified Network.Wai                     as Wai
 import qualified Network.Wai.Middleware.HttpAuth as Wai
 
 import Data.List        (lookup)
+import PostgREST.TimeIt (timeItT)
 import System.IO.Unsafe (unsafePerformIO)
-import System.TimeIt    (timeItT)
 
 import PostgREST.AppState      (AppState, getConfig, getJwtCacheState,
                                 getTime)

src/PostgREST/Error.hs

@@ -3,6 +3,7 @@ Module      : PostgREST.Error
 Description : PostgREST error HTTP responses
 -}
 {-# OPTIONS_GHC -fno-warn-orphans #-}
+{-# LANGUAGE NamedFieldPuns  #-}
 {-# LANGUAGE RecordWildCards #-}
 
 module PostgREST.Error
@@ -41,6 +42,7 @@ import Network.HTTP.Types.Header (Header)
 import           PostgREST.MediaType (MediaType (..))
 import qualified PostgREST.MediaType as MediaType
 
+import PostgREST.SchemaCache              (SchemaCache (SchemaCache, dbTablesFuzzyIndex))
 import PostgREST.SchemaCache.Identifiers  (QualifiedIdentifier (..),
                                            Schema)
 import PostgREST.SchemaCache.Relationship (Cardinality (..),
@@ -49,10 +51,8 @@ import PostgREST.SchemaCache.Relationship (Cardinality (..),
                                            RelationshipsMap)
 import PostgREST.SchemaCache.Routine      (Routine (..),
                                            RoutineParam (..))
-import PostgREST.SchemaCache.Table        (Table (..))
 import Protolude
 
-
 class (ErrorBody a, JSON.ToJSON a) => PgrstError a where
   status   :: a -> HTTP.Status
   headers  :: a -> [Header]
@@ -250,7 +250,7 @@ data SchemaCacheError
   | NoRelBetween Text Text (Maybe Text) Text RelationshipsMap
   | NoRpc Text Text [Text] MediaType Bool [QualifiedIdentifier] [Routine]
   | ColumnNotFound Text Text
-  | TableNotFound Text Text [Table]
+  | TableNotFound Text Text SchemaCache
   deriving Show
 
 instance PgrstError SchemaCacheError where
@@ -313,7 +313,7 @@ instance ErrorBody SchemaCacheError where
       where
         onlySingleParams = isInvPost && contentType `elem` [MTTextPlain, MTTextXML, MTOctetStream]
   hint (AmbiguousRpc _)      = Just "Try renaming the parameters or the function itself in the database so function overloading can be resolved"
-  hint (TableNotFound schemaName relName tbls) = JSON.String <$> tableNotFoundHint schemaName relName tbls
+  hint (TableNotFound schemaName relName schemaCache) = JSON.String <$> tableNotFoundHint schemaName relName schemaCache
 
   hint _ = Nothing
 
@@ -386,7 +386,7 @@ noRelBetweenHint parent child schema allRels = ("Perhaps you meant '" <>) <$>
 -- Just "Perhaps you meant to call the function api.test"
 --
 -- >>> noRpcHint "api" "other" [] procs []
--- Just "Perhaps you meant to call the function api.another"
+-- Nothing
 --
 -- >>> noRpcHint "api" "noclosealternative" [] procs []
 -- Nothing
@@ -423,18 +423,30 @@ noRpcHint schema procName params allProcs overloadedProcs =
     -- E.g. ["val", "param", "name"] into "(name, param, val)"
     listToText       = ("(" <>) . (<> ")") . T.intercalate ", " . sort
     possibleProcs
-      | null overloadedProcs = Fuzzy.getOne fuzzySetOfProcs procName
+      | null overloadedProcs = getFuzzyHint HintProcedure fuzzySetOfProcs procName
-      | otherwise            = (procName <>) <$> Fuzzy.getOne fuzzySetOfParams (listToText params)
+      | otherwise            = (procName <>) <$> getFuzzyHint HintParams fuzzySetOfParams (listToText params)
 
 -- |
 -- Do a fuzzy search in all tables in the same schema and return closest result
-tableNotFoundHint :: Text -> Text -> [Table] -> Maybe Text
+tableNotFoundHint :: Text -> Text -> SchemaCache -> Maybe Text
-tableNotFoundHint schema tblName tblList
+tableNotFoundHint schema tblName SchemaCache{dbTablesFuzzyIndex}
   = fmap (\tbl -> "Perhaps you meant the table '" <> schema <> "." <> tbl <> "'") perhapsTable
     where
-      perhapsTable = Fuzzy.getOne fuzzyTableSet tblName
+      perhapsTable = (\fuzzySet -> getFuzzyHint HintTable fuzzySet tblName) =<< HM.lookup schema dbTablesFuzzyIndex
-      fuzzyTableSet = Fuzzy.fromList [ tableName tbl | tbl <- tblList, tableSchema tbl == schema]
 
+data HintType
+  = HintTable
+  | HintProcedure
+  | HintParams
+
+-- | Get hint using Fuzzy Search with at least 0.75 similarity score
+getFuzzyHint :: HintType -> Fuzzy.FuzzySet -> Text -> Maybe Text
+getFuzzyHint hintType =
+  let minScore = 0.75 :: Double -- used for table and procedure name hints
+  in case hintType of
+    HintTable     -> Fuzzy.getOneWithMinScore minScore
+    HintProcedure -> Fuzzy.getOneWithMinScore minScore
+    HintParams    -> Fuzzy.getOne -- For params, we stick to `getOne` which defaults to 0.33 min score, not a security risk to reveal params
 
 compressedRel :: Relationship -> JSON.Value
 -- An ambiguousness error cannot happen for computed relationships TODO refactor so this mempty is not needed

src/PostgREST/Listener.hs

@@ -1,3 +1,4 @@
+{-# LANGUAGE LambdaCase      #-}
 {-# LANGUAGE MultiWayIf      #-}
 {-# LANGUAGE RecordWildCards #-}
 
@@ -9,12 +10,14 @@ import qualified Hasql.Connection      as SQL
 import qualified Hasql.Notifications   as SQL
 import           PostgREST.AppState    (AppState, getConfig)
 import           PostgREST.Config      (AppConfig (..))
-import           PostgREST.Observation (Observation (..))
+import           PostgREST.Observation (Observation (..),
+                                        isDbListenerBug)
 import           PostgREST.Version     (prettyVersion)
 
 import qualified PostgREST.AppState as AppState
 import qualified PostgREST.Config   as Config
 
+import Data.Either.Combinators (whenRight)
 import Protolude
 
 -- | Starts the Listener in a thread
@@ -22,17 +25,20 @@ runListener :: AppState -> IO ()
 runListener appState = do
   AppConfig{..} <- getConfig appState
   when configDbChannelEnabled $
-    void . forkIO $ retryingListen appState
+    void . forkIO . void $ retryingListen appState
 
 -- | Starts a LISTEN connection and handles notifications. It recovers with exponential backoff with a cap of 32 seconds, if the LISTEN connection is lost.
-retryingListen :: AppState -> IO ()
+-- | This function never returns (but can throw) and return type enforces that.
+retryingListen :: AppState -> IO Void
 retryingListen appState = do
   AppConfig{..} <- AppState.getConfig appState
   let
     dbChannel = toS configDbChannel
-    handleFinally err = do
+    onError err = do
       AppState.putIsListenerOn appState False
       observer $ DBListenFail dbChannel (Right err)
+      when (isDbListenerBug err) $
+        observer DBListenBugHint
       unless configDbPoolAutomaticRecovery $
         killThread mainThreadId
 
@@ -42,29 +48,39 @@ retryingListen appState = do
       threadDelay (delay * oneSecondInMicro)
       unless (delay == maxDelay) $
         AppState.putNextListenerDelay appState (delay * 2)
+      -- loop running the listener
       retryingListen appState
 
-  -- forkFinally allows to detect if the thread dies
+  -- Execute the listener with with error handling
-  void . flip forkFinally handleFinally $ do
+  handle onError $ do
-    dbOrError <- SQL.acquire $ toUtf8 (Config.addTargetSessionAttrs $ Config.addFallbackAppName prettyVersion configDbUri)
+    -- Make sure we don't leak connections on errors
-    case dbOrError of
+    bracket
-      Right db -> do
+      -- acquire connection
-        SQL.listen db $ SQL.toPgIdentifier dbChannel
+      (SQL.acquire $ toUtf8 (Config.addTargetSessionAttrs $ Config.addFallbackAppName prettyVersion configDbUri))
-        AppState.putIsListenerOn appState True
+      -- release connection
+      (`whenRight` releaseConnection) $
+      -- use connection
+      \case
+        Right db -> do
+          SQL.listen db $ SQL.toPgIdentifier dbChannel
+          AppState.putIsListenerOn appState True
 
-        delay <- AppState.getNextListenerDelay appState
+          delay <- AppState.getNextListenerDelay appState
-        when (delay > 1) $ do -- if we did a retry
+          when (delay > 1) $ do -- if we did a retry
-          -- assume we lost notifications, refresh the schema cache
+            -- assume we lost notifications, refresh the schema cache
-          AppState.schemaCacheLoader appState
+            AppState.schemaCacheLoader appState
-          -- reset the delay
+            -- reset the delay
-          AppState.putNextListenerDelay appState 1
+            AppState.putNextListenerDelay appState 1
 
-        observer $ DBListenStart dbChannel
+          observer $ DBListenStart dbChannel
-        SQL.waitForNotifications handleNotification db
 
-      Left err -> do
+          -- wait for notifications
-        observer $ DBListenFail dbChannel (Left err)
+          -- this will never return, in case of an error it will throw and be caught by onError
-        exitFailure
+          forever $ SQL.waitForNotifications handleNotification db
+
+        Left err -> do
+          observer $ DBListenFail dbChannel (Left err)
+          exitFailure
   where
     observer = AppState.getObserver appState
     mainThreadId = AppState.getMainThreadId appState
@@ -79,3 +95,5 @@ retryingListen appState = do
 
     cacheReloader =
       AppState.schemaCacheLoader appState
+
+    releaseConnection = void . forkIO . handle (observer . DBListenerConnectionCleanupFail) . SQL.release

src/PostgREST/Observation.hs

@@ -1,4 +1,5 @@
-{-# LANGUAGE LambdaCase #-}
+{-# LANGUAGE DeriveGeneric #-}
+{-# LANGUAGE LambdaCase    #-}
 {-|
 Module      : PostgREST.Observation
 Description : This module holds an Observation type which is the core of Observability for PostgREST.
@@ -12,6 +13,7 @@ module PostgREST.Observation
   , observationMessage
   , ObservationHandler
   , showOnSingleLine
+  , isDbListenerBug
   ) where
 
 import qualified Data.ByteString.Lazy       as LBS
@@ -44,10 +46,12 @@ data Observation
   | SchemaCacheLoadedObs Double
   | ConnectionRetryObs Int
   | DBListenStart Text
-  | DBListenFail Text (Either SQL.ConnectionError (Either SomeException ()))
+  | DBListenFail Text (Either SQL.ConnectionError SomeException)
   | DBListenRetry Int
+  | DBListenBugHint -- https://github.com/PostgREST/postgrest/issues/3147
   | DBListenerGotSCacheMsg ByteString
   | DBListenerGotConfigMsg ByteString
+  | DBListenerConnectionCleanupFail SomeException
   | QueryObs MainQuery Status
   | ConfigReadErrorObs SQL.UsageError
   | ConfigInvalidObs Text
@@ -62,6 +66,9 @@ data Observation
   | PoolRequestFullfilled
   | JwtCacheLookup Bool
   | JwtCacheEviction
+  | TerminationUnixSignalObs Text
+  | WarpErrorObs Text
+  deriving (Generic)
 
 data ObsFatalError = ServerAuthError | ServerPgrstBug | ServerError42P05 | ServerError08P01
 
@@ -114,10 +121,14 @@ observationMessage = \case
       either showListenerConnError showListenerException listenErr
   DBListenRetry delay ->
     "Retrying listening for database notifications in " <> (show delay::Text) <> " seconds..."
+  DBListenBugHint ->
+    "HINT:  This is likely a bug in the notification queue, try executing the following to solve it: select pg_notification_queue_usage();"
   DBListenerGotSCacheMsg channel ->
     "Received a schema cache reload message on the " <> show channel <> " channel"
   DBListenerGotConfigMsg channel ->
     "Received a config reload message on the " <> show channel <> " channel"
+  DBListenerConnectionCleanupFail ex ->
+    "Failed during listener connection cleanup: " <> showOnSingleLine '\t' (show ex)
   QueryObs{} ->
     mempty -- TODO pending refactor: The logic for printing the query cannot be done here. Join the observationMessage function into observationLogger to avoid this mempty.
   ConfigReadErrorObs usageErr ->
@@ -154,9 +165,13 @@ observationMessage = \case
     "Looked up a JWT in JWT cache"
   JwtCacheEviction ->
     "Evicted entry from JWT cache"
+  TerminationUnixSignalObs signal ->
+    "Received termination unix signal " <> signal
+  WarpErrorObs txt ->
+    "Warp server error: " <> txt
   where
     showMillis :: Double -> Text
-    showMillis x = toS $ showFFloat (Just 1) (x * 1000) ""
+    showMillis x = toS $ showFFloat (Just 1) x ""
 
     jsonMessage err = T.decodeUtf8 . LBS.toStrict . Error.errorPayload $ Error.PgError False err
 
@@ -164,10 +179,12 @@ observationMessage = \case
     showListenerConnError :: SQL.ConnectionError -> Text
     showListenerConnError = maybe "Connection error" (showOnSingleLine '\t' . T.decodeUtf8)
 
-    showListenerException :: Either SomeException () -> Text
+    showListenerException :: SomeException -> Text
-    showListenerException (Right _) = "Failed getting notifications" -- should not happen as the listener will never finish (hasql-notifications uses `forever` internally) with a Right result
+    showListenerException = showOnSingleLine '\t' . show
-    showListenerException (Left e)  = showOnSingleLine '\t' $ show e
 
 
 showOnSingleLine :: Char -> Text -> Text
 showOnSingleLine split txt = T.intercalate " " $ T.filter (/= split) <$> T.lines txt -- the errors from hasql-notifications come intercalated with "\t\n"
+
+isDbListenerBug :: SomeException -> Bool
+isDbListenerBug e = "could not access status of transaction" `T.isInfixOf` show e

src/PostgREST/Plan.hs

@@ -172,7 +172,7 @@ dbActionPlan dbAct conf apiReq sCache = case dbAct of
 
 wrappedReadPlan :: QualifiedIdentifier -> AppConfig -> SchemaCache -> ApiRequest -> Bool -> Either Error CrudPlan
 wrappedReadPlan  identifier conf sCache apiRequest@ApiRequest{iPreferences=Preferences{..},..} headersOnly = do
-  qi <- findTable identifier (dbTables sCache)
+  qi <- findTable identifier sCache
   rPlan <- readPlan qi conf sCache apiRequest
   (handler, mediaType)  <- mapLeft ApiRequestError $ negotiateContent conf apiRequest qi iAcceptMediaType (dbMediaHandlers sCache) (hasDefaultSelect rPlan)
   if not (null invalidPrefs) && preferHandling == Just Strict then Left $ ApiRequestError $ InvalidPreferences invalidPrefs else Right ()
@@ -180,7 +180,7 @@ wrappedReadPlan  identifier conf sCache apiRequest@ApiRequest{iPreferences=Prefe
 
 mutateReadPlan :: Mutation -> ApiRequest -> QualifiedIdentifier -> AppConfig -> SchemaCache -> Either Error CrudPlan
 mutateReadPlan  mutation apiRequest@ApiRequest{iPreferences=Preferences{..},..} identifier conf sCache = do
-  qi <- findTable identifier (dbTables sCache)
+  qi <- findTable identifier sCache
   rPlan <- readPlan qi conf sCache apiRequest
   mPlan <- mutatePlan mutation qi apiRequest sCache rPlan
   if not (null invalidPrefs) && preferHandling == Just Strict then Left $ ApiRequestError $ InvalidPreferences invalidPrefs else Right ()
@@ -256,14 +256,16 @@ findProc qi argumentsKeys allProcs contentMediaType isInvPost =
       | hasSingleUnnamedParam proc = (ts,proc:fs)
       | otherwise                  = (ts,fs)
     -- If the function is called with post and has a single unnamed parameter
-    -- it can be called depending on content type and the parameter type
+    -- it can be called depending on content type and the parameter type.
-    hasSingleUnnamedParam Function{pdParams=[RoutineParam{ppType}]} = isInvPost && case (contentMediaType, ppType) of
+    -- The parameter must have no declared name (ppName == mempty).
-      (MTApplicationJSON, "json")  -> True
+    hasSingleUnnamedParam Function{pdParams=[RoutineParam{ppName, ppType}]} =
-      (MTApplicationJSON, "jsonb") -> True
+      isInvPost && ppName == mempty && case (contentMediaType, ppType) of
-      (MTTextPlain, "text")        -> True
+        (MTApplicationJSON, "json")  -> True
-      (MTTextXML, "xml")           -> True
+        (MTApplicationJSON, "jsonb") -> True
-      (MTOctetStream, "bytea")     -> True
+        (MTTextPlain, "text")        -> True
-      _                            -> False
+        (MTTextXML, "xml")           -> True
+        (MTOctetStream, "bytea")     -> True
+        _                            -> False
     hasSingleUnnamedParam _ = False
     matchesParams proc =
       let
@@ -810,10 +812,10 @@ validateAggFunctions aggFunctionsAllowed (Node rp@ReadPlan {select} forest)
   | otherwise = Node rp <$> traverse (validateAggFunctions aggFunctionsAllowed) forest
 
 -- | Lookup table in the schema cache before creating read plan
-findTable :: QualifiedIdentifier -> TablesMap -> Either Error QualifiedIdentifier
+findTable :: QualifiedIdentifier -> SchemaCache -> Either Error QualifiedIdentifier
-findTable qi@QualifiedIdentifier{..} tableMap =
+findTable qi@QualifiedIdentifier{..} sc@SchemaCache{dbTables} =
-  case HM.lookup qi tableMap of
+  case HM.lookup qi dbTables of
-    Nothing -> Left $ SchemaCacheErr $ TableNotFound qiSchema qiName (HM.elems tableMap)
+    Nothing -> Left $ SchemaCacheErr $ TableNotFound qiSchema qiName sc
     Just _ -> Right qi
 
 addFilters :: ResolverContext -> ApiRequest -> ReadPlanTree -> Either Error ReadPlanTree
@@ -963,10 +965,17 @@ addRanges ApiRequest{..} rReq =
 
 addLogicTrees :: ResolverContext -> ApiRequest -> ReadPlanTree -> Either Error ReadPlanTree
 addLogicTrees ctx ApiRequest{..} rReq =
-  foldr addLogicTreeToNode (Right rReq) qsLogic
+  foldr addLogicTreeToNode (Right rReq) logic
   where
     QueryParams.QueryParams{..} = iQueryParams
 
+    logic =
+      case iAction of
+        ActDb (ActRelationRead _  _) -> qsLogic
+        ActDb (ActRoutine _ _)       -> qsLogic
+        -- For mutations, take the non-root logic filters. These will only affect the embeddings and not the top level of the returned representation.
+        _                            -> filter (not . null . fst) qsLogic
+
     addLogicTreeToNode :: (EmbedPath, LogicTree) -> Either Error ReadPlanTree -> Either Error ReadPlanTree
     addLogicTreeToNode = updateNode (\t (Node q@ReadPlan{from=fromTable, where_=lf} f) -> Node q{ReadPlan.where_=resolveLogicTree ctx{qi=fromTable} t:lf} f)
 

src/PostgREST/Query/SqlFragment.hs

@@ -6,13 +6,22 @@ Module      : PostgREST.Query.SqlFragment
 Description : Helper functions for PostgREST.QueryBuilder.
 -}
 module PostgREST.Query.SqlFragment
-  ( noLocationF
+  ( accessibleFuncs
-  , handlerF
+  , accessibleTables
+  , addConfigPgrstInserted
   , countF
-  , groupF
+  , currentSettingF
+  , escapeIdent
+  , escapeIdentList
+  , explainF
+  , fromJsonBodyF
   , fromQi
+  , groupF
+  , handlerF
+  , intercalateSnippet
   , limitOffsetF
   , locationF
+  , noLocationF
   , orderF
   , pgFmtColumn
   , pgFmtFilter
@@ -21,28 +30,19 @@ module PostgREST.Query.SqlFragment
   , pgFmtLogicTree
   , pgFmtOrderTerm
   , pgFmtSelectItem
-  , pgFmtSpreadSelectItem
   , pgFmtSpreadJoinSelectItem
-  , fromJsonBodyF
+  , pgFmtSpreadSelectItem
   , responseHeadersF
   , responseStatusF
-  , addConfigPgrstInserted
-  , currentSettingF
   , returningF
+  , schemaDescription
+  , setConfigWithConstantName
+  , setConfigWithConstantNameJSON
+  , setConfigWithDynamicName
   , singleParameter
   , sourceCTE
   , sourceCTEName
   , unknownEncoder
-  , intercalateSnippet
-  , explainF
-  , setConfigWithConstantName
-  , setConfigWithDynamicName
-  , setConfigWithConstantNameJSON
-  , escapeIdent
-  , escapeIdentList
-  , schemaDescription
-  , accessibleTables
-  , accessibleFuncs
   ) where
 
 import qualified Data.Aeson                      as JSON
@@ -90,7 +90,8 @@ import PostgREST.RangeQuery              (NonnegRange, allRange,
                                           rangeLimit, rangeOffset)
 import PostgREST.SchemaCache.Identifiers (FieldName,
                                           QualifiedIdentifier (..),
-                                          RelIdentifier (..))
+                                          RelIdentifier (..),
+                                          escapeIdent, trimNullChars)
 import PostgREST.SchemaCache.Routine     (MediaHandler (..),
                                           Routine (..),
                                           funcReturnsScalar,
@@ -163,9 +164,6 @@ pgBuildArrayLiteral vals =
 pgFmtIdent :: Text -> SQL.Snippet
 pgFmtIdent x = SQL.sql . encodeUtf8 $ escapeIdent x
 
-escapeIdent :: Text -> Text
-escapeIdent x = "\"" <> T.replace "\"" "\"\"" (trimNullChars x) <> "\""
-
 -- Only use it if the input comes from the database itself, like on `jsonb_build_object('column_from_a_table', val)..`
 pgFmtLit :: Text -> Text
 pgFmtLit x =
@@ -176,9 +174,6 @@ pgFmtLit x =
     then "E" <> slashed
     else slashed
 
-trimNullChars :: Text -> Text
-trimNullChars = T.takeWhile (/= '\x0')
-
 -- |
 -- Format a list of identifiers and separate them by commas.
 --

src/PostgREST/Response.hs

@@ -62,11 +62,12 @@ data PgrstResponse = PgrstResponse {
 
 actionResponse :: DbResult -> ApiRequest -> (Text, Text) -> AppConfig -> SchemaCache -> Schema -> Bool -> Either Error.Error PgrstResponse
 
-actionResponse (DbCrudResult WrappedReadPlan{pMedia, wrHdrsOnly=headersOnly, crudQi=identifier} RSStandard{..}) ctxApiRequest@ApiRequest{iPreferences=Preferences{..},..} _ _ _ _ _ = do
+actionResponse (DbCrudResult plan@WrappedReadPlan{pMedia, wrHdrsOnly=headersOnly, crudQi=identifier} RSStandard{..}) ctxApiRequest@ApiRequest{..} _ _ _ _ _ = do
   let
     (status, contentRange) = RangeQuery.rangeStatusHeader iTopLevelRange rsQueryTotal rsTableTotal
-    cLHeader = if headersOnly then mempty else [contentLengthHeaderStrict rsBody]
+    cLHeader = if headersOnly then mempty else [ contentLengthHeader bod ]
-    prefHeader = maybeToList . prefAppliedHeader $ Preferences Nothing Nothing preferCount preferTransaction Nothing preferHandling preferTimezone Nothing []
+    prefHeader = maybeToList . prefAppliedHeader $ responsePreferences plan ctxApiRequest
+
     headers =
       [ contentRange
       , ( "Content-Location"
@@ -87,12 +88,10 @@ actionResponse (DbCrudResult WrappedReadPlan{pMedia, wrHdrsOnly=headersOnly, cru
 
   Right $ PgrstResponse ovStatus ovHeaders bod
 
-actionResponse (DbCrudResult MutateReadPlan{mrMutation=MutationCreate, mrMutatePlan, pMedia, crudQi=QualifiedIdentifier{..}} RSStandard{..}) ctxApiRequest@ApiRequest{iPreferences=Preferences{..}, ..} _ _ _ _ _ = do
+actionResponse (DbCrudResult plan@MutateReadPlan{mrMutation=MutationCreate, pMedia, crudQi=QualifiedIdentifier{..}} RSStandard{..}) ctxApiRequest@ApiRequest{..} _ _ _ _ _ = do
   let
-    pkCols = case mrMutatePlan of { Insert{insPkCols} -> insPkCols; _ -> mempty;}
+    prefHeader = prefAppliedHeader $ responsePreferences plan ctxApiRequest
-    prefHeader = prefAppliedHeader $
+
-      Preferences (if null pkCols && isNothing (qsOnConflict iQueryParams) then Nothing else preferResolution)
-      preferRepresentation preferCount preferTransaction preferMissing preferHandling preferTimezone Nothing []
     headers =
       catMaybes
         [ if null rsLocation then
@@ -105,37 +104,39 @@ actionResponse (DbCrudResult MutateReadPlan{mrMutation=MutationCreate, mrMutateP
                   <> HTTP.renderSimpleQuery True rsLocation
               )
         , Just . RangeQuery.contentRangeH 1 0 $
-            if shouldCount preferCount then Just rsQueryTotal else Nothing
+            if shouldCount (preferCount iPreferences) then Just rsQueryTotal else Nothing
-        , Just $ contentLengthHeaderStrict rsBody
         , prefHeader ]
 
     isInsertIfGTZero i =
-        if i <= 0 && preferResolution == Just MergeDuplicates then
+        if i <= 0 && preferResolution iPreferences == Just MergeDuplicates then
           HTTP.status200
         else
           HTTP.status201
     status = maybe HTTP.status200 isInsertIfGTZero rsInserted
-    (headers', bod) = case preferRepresentation of
+    (headers', bod) = case preferRepresentation iPreferences of
       Just Full -> (headers ++ contentTypeHeaders pMedia ctxApiRequest, LBS.fromStrict rsBody)
       Just None -> (headers, mempty)
       Just HeadersOnly -> (headers, mempty)
       Nothing -> (headers, mempty)
 
-  (ovStatus, ovHeaders) <- overrideStatusHeaders rsGucStatus rsGucHeaders status headers'
+  (ovStatus, ovHeaders) <- overrideStatusHeaders rsGucStatus rsGucHeaders status $ contentLengthHeader bod:headers'
 
   Right $ PgrstResponse ovStatus ovHeaders bod
 
-actionResponse (DbCrudResult MutateReadPlan{mrMutation=MutationUpdate, pMedia} RSStandard{..}) ctxApiRequest@ApiRequest{iPreferences=Preferences{..}} _ _ _ _ _ = do
+actionResponse (DbCrudResult plan@MutateReadPlan{mrMutation=MutationUpdate, pMedia} RSStandard{..}) ctxApiRequest@ApiRequest{..} _ _ _ _ _ = do
   let
     contentRangeHeader =
       Just . RangeQuery.contentRangeH 0 (rsQueryTotal - 1) $
-        if shouldCount preferCount then Just rsQueryTotal else Nothing
+        if shouldCount (preferCount iPreferences) then Just rsQueryTotal else Nothing
-    prefHeader = prefAppliedHeader $ Preferences Nothing preferRepresentation preferCount preferTransaction preferMissing preferHandling preferTimezone preferMaxAffected []
+
+    prefHeader = prefAppliedHeader $ responsePreferences plan ctxApiRequest
+
     headers = catMaybes [contentRangeHeader, prefHeader]
+    lbsBody = LBS.fromStrict rsBody
 
   let (status, headers', body) =
-        case preferRepresentation of
+        case preferRepresentation iPreferences of
-          Just Full -> (HTTP.status200, headers ++ [contentLengthHeaderStrict rsBody] ++ contentTypeHeaders pMedia ctxApiRequest, LBS.fromStrict rsBody)
+          Just Full -> (HTTP.status200, headers ++ [contentLengthHeader lbsBody] ++ contentTypeHeaders pMedia ctxApiRequest, lbsBody)
           Just None -> (HTTP.status204, headers, mempty)
           _         -> (HTTP.status204, headers, mempty)
 
@@ -143,31 +144,33 @@ actionResponse (DbCrudResult MutateReadPlan{mrMutation=MutationUpdate, pMedia} R
 
   Right $ PgrstResponse ovStatus ovHeaders body
 
-actionResponse (DbCrudResult MutateReadPlan{mrMutation=MutationSingleUpsert, pMedia} RSStandard{..}) ctxApiRequest@ApiRequest{iPreferences=Preferences{..}} _ _ _ _ _ = do
+actionResponse (DbCrudResult plan@MutateReadPlan{mrMutation=MutationSingleUpsert, pMedia} RSStandard{..}) ctxApiRequest@ApiRequest{..} _ _ _ _ _ = do
   let
-    prefHeader = maybeToList . prefAppliedHeader $ Preferences Nothing preferRepresentation preferCount preferTransaction Nothing preferHandling preferTimezone Nothing []
+    prefHeader = maybeToList . prefAppliedHeader $ responsePreferences plan ctxApiRequest
-    cLHeader = [contentLengthHeaderStrict rsBody]
+    lbsBody = LBS.fromStrict rsBody
+    cLHeader = [contentLengthHeader lbsBody]
     cTHeader = contentTypeHeaders pMedia ctxApiRequest
 
   let isInsertIfGTZero i = if i > 0 then HTTP.status201 else HTTP.status200
       upsertStatus       = isInsertIfGTZero $ fromJust rsInserted
       (status, headers, body) =
-        case preferRepresentation of
+        case preferRepresentation iPreferences of
-          Just Full -> (upsertStatus, cLHeader ++ cTHeader ++ prefHeader, LBS.fromStrict rsBody)
+          Just Full -> (upsertStatus, cLHeader ++ cTHeader ++ prefHeader, lbsBody)
           Just None -> (HTTP.status204,  prefHeader, mempty)
           _ -> (HTTP.status204, prefHeader, mempty)
   (ovStatus, ovHeaders) <- overrideStatusHeaders rsGucStatus rsGucHeaders status headers
 
   Right $ PgrstResponse ovStatus ovHeaders body
 
-actionResponse (DbCrudResult MutateReadPlan{mrMutation=MutationDelete, pMedia} RSStandard{..}) ctxApiRequest@ApiRequest{iPreferences=Preferences{..}} _ _ _ _ _ = do
+actionResponse (DbCrudResult plan@MutateReadPlan{mrMutation=MutationDelete, pMedia} RSStandard{..}) ctxApiRequest@ApiRequest{..} _ _ _ _ _ = do
   let
-    contentRangeHeader = RangeQuery.contentRangeH 1 0 $ if shouldCount preferCount then Just rsQueryTotal else Nothing
+    contentRangeHeader = RangeQuery.contentRangeH 1 0 $ if shouldCount (preferCount iPreferences) then Just rsQueryTotal else Nothing
-    prefHeader = maybeToList . prefAppliedHeader $ Preferences Nothing preferRepresentation preferCount preferTransaction Nothing preferHandling preferTimezone preferMaxAffected []
+    prefHeader = maybeToList . prefAppliedHeader $ responsePreferences plan ctxApiRequest
     headers = contentRangeHeader : prefHeader
+    lbsBody = LBS.fromStrict rsBody
     (status, headers', body) =
-        case preferRepresentation of
+        case preferRepresentation iPreferences of
-            Just Full -> (HTTP.status200, headers ++ [contentLengthHeaderStrict rsBody] ++ contentTypeHeaders pMedia ctxApiRequest, LBS.fromStrict rsBody)
+            Just Full -> (HTTP.status200, headers ++ [contentLengthHeader lbsBody] ++ contentTypeHeaders pMedia ctxApiRequest, lbsBody)
             Just None -> (HTTP.status204, headers, mempty)
             _ -> (HTTP.status204, headers, mempty)
 
@@ -175,7 +178,7 @@ actionResponse (DbCrudResult MutateReadPlan{mrMutation=MutationDelete, pMedia} R
 
   Right $ PgrstResponse ovStatus ovHeaders body
 
-actionResponse (DbCrudResult CallReadPlan{pMedia, crInvMthd=invMethod, crProc=proc} RSStandard {..}) ctxApiRequest@ApiRequest{iPreferences=Preferences{..},..} _ _ _ _ _ = do
+actionResponse (DbCrudResult plan@CallReadPlan{pMedia, crInvMthd=invMethod, crProc=proc} RSStandard {..}) ctxApiRequest@ApiRequest{..} _ _ _ _ _ = do
   let
     (status, contentRange) =
       RangeQuery.rangeStatusHeader iTopLevelRange rsQueryTotal rsTableTotal
@@ -184,8 +187,8 @@ actionResponse (DbCrudResult CallReadPlan{pMedia, crInvMthd=invMethod, crProc=pr
         $ Error.OutOfBounds (show $ RangeQuery.rangeOffset iTopLevelRange) (maybe "0" show rsTableTotal)
       else LBS.fromStrict rsBody
     isHeadMethod = invMethod == InvRead True
-    prefHeader = maybeToList . prefAppliedHeader $ Preferences Nothing Nothing preferCount preferTransaction Nothing preferHandling preferTimezone preferMaxAffected []
+    prefHeader = maybeToList . prefAppliedHeader $ responsePreferences plan ctxApiRequest
-    cLHeader = if isHeadMethod then mempty else [contentLengthHeaderLazy rsOrErrBody]
+    cLHeader = if isHeadMethod then mempty else [contentLengthHeader rsOrErrBody]
     headers = contentRange : prefHeader
     (status', headers', body) =
         if Routine.funcReturnsVoid proc then
@@ -200,19 +203,20 @@ actionResponse (DbCrudResult CallReadPlan{pMedia, crInvMthd=invMethod, crProc=pr
   Right $ PgrstResponse ovStatus ovHeaders body
 
 actionResponse (DbPlanResult media plan) ctxApiRequest _ _ _ _ _ =
-  Right $ PgrstResponse HTTP.status200 (contentLengthHeaderStrict plan : contentTypeHeaders media ctxApiRequest) $ LBS.fromStrict plan
+  let body = LBS.fromStrict plan in
+  Right $ PgrstResponse HTTP.status200 (contentLengthHeader body : contentTypeHeaders media ctxApiRequest) body
 
 actionResponse (MaybeDbResult InspectPlan{ipHdrsOnly=headersOnly} body) _ versions conf sCache schema negotiatedByProfile =
   let
     rsBody = maybe mempty (\(x, y, z) -> if headersOnly then mempty else OpenAPI.encode versions conf sCache x y z) body
-    cLHeader = if headersOnly then mempty else [contentLengthHeaderLazy rsBody]
+    cLHeader = if headersOnly then mempty else [contentLengthHeader rsBody]
   in
   Right $ PgrstResponse HTTP.status200 (MediaType.toContentType MTOpenAPI : cLHeader ++ maybeToList (profileHeader schema negotiatedByProfile)) rsBody
 
-actionResponse (NoDbResult (RelInfoPlan qi@QualifiedIdentifier{..})) _ _ _ SchemaCache{dbTables} _ _ =
+actionResponse (NoDbResult (RelInfoPlan qi@QualifiedIdentifier{..})) _ _ _ sc@SchemaCache{dbTables} _ _ =
   case HM.lookup qi dbTables of
     Just tbl -> respondInfo $ allowH tbl
-    Nothing  -> Left $ Error.SchemaCacheErr $ Error.TableNotFound qiSchema qiName (HM.elems dbTables)
+    Nothing  -> Left $ Error.SchemaCacheErr $ Error.TableNotFound qiSchema qiName sc
   where
     allowH table =
       let hasPK = not . null $ tablePKCols table in
@@ -232,7 +236,7 @@ actionResponse (NoDbResult SchemaInfoPlan) _ _ _ _ _ _ = respondInfo "OPTIONS,GE
 respondInfo :: ByteString -> Either Error.Error PgrstResponse
 respondInfo allowHeader =
   let allOrigins = ("Access-Control-Allow-Origin", "*") in
-  Right $ PgrstResponse HTTP.status200 [contentLengthHeaderStrict mempty, allOrigins, (HTTP.hAllow, allowHeader)] mempty
+  Right $ PgrstResponse HTTP.status200 [contentLengthHeader mempty, allOrigins, (HTTP.hAllow, allowHeader)] mempty
 
 -- Status and headers can be overridden as per https://postgrest.org/en/stable/references/transactions.html#response-headers
 overrideStatusHeaders :: Maybe Text -> Maybe BS.ByteString -> HTTP.Status -> [HTTP.Header]-> Either Error.Error (HTTP.Status, [HTTP.Header])
@@ -249,14 +253,8 @@ decodeGucStatus :: Maybe Text -> Either Error.Error (Maybe HTTP.Status)
 decodeGucStatus =
   maybe (Right Nothing) $ first (const . Error.ApiRequestError $ Error.GucStatusError) . fmap (Just . toEnum . fst) . decimal
 
-contentLengthHeader :: Show b => (a -> b) -> a -> HTTP.Header
+contentLengthHeader :: LBS.ByteString -> HTTP.Header
-contentLengthHeader lenFn body = ("Content-Length", show (lenFn body))
+contentLengthHeader body = ("Content-Length", show (LBS.length body))
-
-contentLengthHeaderStrict :: BS.ByteString -> HTTP.Header
-contentLengthHeaderStrict = contentLengthHeader BS.length
-
-contentLengthHeaderLazy :: LBS.ByteString -> HTTP.Header
-contentLengthHeaderLazy = contentLengthHeader LBS.length
 
 contentTypeHeaders :: MediaType -> ApiRequest -> [HTTP.Header]
 contentTypeHeaders mediaType ApiRequest{..} =
@@ -274,3 +272,31 @@ addHeadersIfNotIncluded :: [HTTP.Header] -> [HTTP.Header] -> [HTTP.Header]
 addHeadersIfNotIncluded newHeaders initialHeaders =
   filter (\(nk, _) -> isNothing $ find (\(ik, _) -> ik == nk) initialHeaders) newHeaders ++
   initialHeaders
+
+-- | Get Preferences for Preference-Applied header per plan
+responsePreferences :: CrudPlan -> ApiRequest -> Preferences
+responsePreferences plan ApiRequest{iPreferences=Preferences{..}, iQueryParams=QueryParams{..}} =
+  let
+    -- Only returned on Inserts
+    preferResolution' = case plan of
+      MutateReadPlan{mrMutation=MutationCreate, mrMutatePlan} ->
+        let pkCols = case mrMutatePlan of { Insert{insPkCols} -> insPkCols ; _ -> mempty; }
+        in (if null pkCols && isNothing qsOnConflict then Nothing else preferResolution)
+      _ -> Nothing
+
+    preferRepresentation' = case plan of
+      MutateReadPlan{} -> preferRepresentation
+      _                -> Nothing
+
+    preferMissing' = case plan of
+      MutateReadPlan{mrMutation=MutationCreate} -> preferMissing
+      MutateReadPlan{mrMutation=MutationUpdate} -> preferMissing
+      _                                         -> Nothing
+
+    preferMaxAffected' = case plan of
+      MutateReadPlan{mrMutation=MutationUpdate} -> preferMaxAffected
+      MutateReadPlan{mrMutation=MutationDelete} -> preferMaxAffected
+      CallReadPlan{}                            -> preferMaxAffected
+      _                                         -> Nothing
+
+    in Preferences preferResolution' preferRepresentation' preferCount preferTransaction preferMissing' preferHandling preferTimezone preferMaxAffected' []

src/PostgREST/Response/OpenAPI.hs

@@ -73,6 +73,12 @@ toSwaggerType colType             = case T.takeEnd 2 colType of
   "[]" -> Just SwaggerArray
   _    -> Just SwaggerString
 
+toSwaggerFormat :: Text -> Maybe Text
+toSwaggerFormat "smallint" = Just "int32"
+toSwaggerFormat "integer"  = Just "int32"
+toSwaggerFormat "bigint"   = Just "int64"
+toSwaggerFormat colType    = Just colType
+
 typeFromArray :: Text -> Text
 typeFromArray = T.dropEnd 2
 
@@ -141,7 +147,7 @@ makeProperty tbl rels col = (colName col, Inline s)
         & default_ .~ (JSON.decode . toUtf8Lazy . parseDefault (colType col) =<< colDefault col)
         & description .~ d
         & enum_ .~ e
-        & format ?~ colType col
+        & format .~ toSwaggerFormat (colType col)
         & maxLength .~ (fromIntegral <$> colMaxLen col)
         & type_ .~ toSwaggerType (colType col)
         & items .~ (SwaggerItemsObject <$> makePropertyItems (colType col))
@@ -160,7 +166,7 @@ makeProcProperty (RoutineParam n t _ _ _) = (n, Inline s)
     s = (mempty :: Schema)
           & type_ .~ toSwaggerType t
           & items .~ (SwaggerItemsObject <$> makePropertyItems t)
-          & format ?~ t
+          & format .~ toSwaggerFormat t
 
 makePreferParam :: [Text] -> Param
 makePreferParam ts =
@@ -192,14 +198,14 @@ makeProcGetParam (RoutineParam n t _ r v) =
     baseSchema = (mempty :: ParamOtherSchema)
       & in_ .~ ParamQuery
     schemaNotMulti = baseSchema
-      & format ?~ t
+      & format .~ toSwaggerFormat t
       & type_ ?~ toParamType (toSwaggerType t)
     schemaMulti = baseSchema
       & type_ ?~ fromMaybe SwaggerString (toSwaggerType t)
       & items ?~ SwaggerItemsPrimitive (Just CollectionMulti)
         ((mempty :: ParamSchema x)
           & type_ .~ toSwaggerTypeFromArray t
-          & format ?~ typeFromArray t)
+          & format .~ toSwaggerFormat (typeFromArray t))
     toParamType paramType = case paramType of
       -- Array uses {} in query params
       Just SwaggerArray -> SwaggerString

src/PostgREST/Response/Performance.hs

@@ -1,4 +1,3 @@
-{-# LANGUAGE NumericUnderscores #-}
 module PostgREST.Response.Performance
   ( ServerTiming (..)
   , serverTimingHeader
@@ -24,12 +23,12 @@ data ServerTiming =
 -- The duration precision is milliseconds, per the docs
 --
 -- >>> serverTimingHeader ServerTiming { plan=Just 0.1, transaction=Just 0.2, response=Just 0.3, jwt=Just 0.4, parse=Just 0.5}
--- ("Server-Timing","jwt;dur=400.0, parse;dur=500.0, plan;dur=100.0, transaction;dur=200.0, response;dur=300.0")
+-- ("Server-Timing","jwt;dur=0.4, parse;dur=0.5, plan;dur=0.1, transaction;dur=0.2, response;dur=0.3")
 serverTimingHeader :: ServerTiming -> HTTP.Header
 serverTimingHeader timing =
   ("Server-Timing", renderTiming)
   where
-    renderMetric metric = maybe "" (\dur -> BS.concat [metric, BS.pack $ ";dur=" <> showFFloat (Just 1) (dur * 1_000) ""])
+    renderMetric metric = maybe "" (\dur -> BS.concat [metric, BS.pack $ ";dur=" <> showFFloat (Just 1) dur ""])
     renderTiming = BS.intercalate ", " $ (\(k, v) -> renderMetric k (v timing)) <$>
       [ ("jwt", jwt)
       , ("parse", parse)

src/PostgREST/SchemaCache.hs

@@ -20,6 +20,7 @@ These queries are executed once at startup or when PostgREST is reloaded.
 
 module PostgREST.SchemaCache
   ( SchemaCache(..)
+  , TablesFuzzyIndex
   , querySchemaCache
   , showSummary
   , decodeFuncs
@@ -42,11 +43,11 @@ import NeatInterpolation          (trimming)
 import PostgREST.Config                      (AppConfig (..))
 import PostgREST.Config.Database             (TimezoneNames,
                                               toIsolationLevel)
-import PostgREST.Query.SqlFragment           (escapeIdent)
 import PostgREST.SchemaCache.Identifiers     (FieldName,
                                               QualifiedIdentifier (..),
                                               RelIdentifier (..),
-                                              Schema, isAnyElement)
+                                              Schema, escapeIdent,
+                                              isAnyElement)
 import PostgREST.SchemaCache.Relationship    (Cardinality (..),
                                               Junction (..),
                                               Relationship (..),
@@ -66,21 +67,28 @@ import PostgREST.SchemaCache.Table           (Column (..), ColumnMap,
 
 import qualified PostgREST.MediaType as MediaType
 
-import Control.Arrow    ((&&&))
+import           Control.Arrow    ((&&&))
-import Protolude
+import qualified Data.FuzzySet    as Fuzzy
-import System.IO.Unsafe (unsafePerformIO)
+import           Protolude
+import           System.IO.Unsafe (unsafePerformIO)
+
+type TablesFuzzyIndex = HM.HashMap Schema Fuzzy.FuzzySet
 
 data SchemaCache = SchemaCache
-  { dbTables          :: TablesMap
+  { dbTables           :: TablesMap
-  , dbRelationships   :: RelationshipsMap
+  , dbRelationships    :: RelationshipsMap
-  , dbRoutines        :: RoutineMap
+  , dbRoutines         :: RoutineMap
-  , dbRepresentations :: RepresentationsMap
+  , dbRepresentations  :: RepresentationsMap
-  , dbMediaHandlers   :: MediaHandlerMap
+  , dbMediaHandlers    :: MediaHandlerMap
-  , dbTimezones       :: TimezoneNames
+  , dbTimezones        :: TimezoneNames
-  }
+  -- Memoized fuzzy index of table names per schema to support approximate matching
+  -- Since index construction can be expensive, we build it once and store in the SchemaCache
+  -- Haskell lazy evaluation ensures it's only built on first use and memoized afterwards
+  , dbTablesFuzzyIndex :: TablesFuzzyIndex
+  } deriving (Show)
 
 instance JSON.ToJSON SchemaCache where
-  toJSON (SchemaCache tabs rels routs reps hdlers tzs) = JSON.object [
+  toJSON (SchemaCache tabs rels routs reps hdlers tzs _) = JSON.object [
       "dbTables"          .= JSON.toJSON tabs
     , "dbRelationships"   .= JSON.toJSON rels
     , "dbRoutines"        .= JSON.toJSON routs
@@ -90,7 +98,7 @@ instance JSON.ToJSON SchemaCache where
     ]
 
 showSummary :: SchemaCache -> Text
-showSummary (SchemaCache tbls rels routs reps mediaHdlrs tzs) =
+showSummary (SchemaCache tbls rels routs reps mediaHdlrs tzs _) =
   T.intercalate ", "
   [ show (HM.size tbls)       <> " Relations"
   , show (HM.size rels)       <> " Relationships"
@@ -138,6 +146,8 @@ data KeyDep
 -- | A SQL query that can be executed independently
 type SqlQuery = ByteString
 
+maxDbTablesForFuzzySearch :: Int
+maxDbTablesForFuzzySearch = 500
 
 querySchemaCache :: AppConfig -> SQL.Transaction SchemaCache
 querySchemaCache conf@AppConfig{..} = do
@@ -166,6 +176,11 @@ querySchemaCache conf@AppConfig{..} = do
     , dbRepresentations = reps
     , dbMediaHandlers = HM.union mHdlers initialMediaHandlers -- the custom handlers will override the initial ones
     , dbTimezones = tzones
+
+    , dbTablesFuzzyIndex =
+        -- Only build fuzzy index for schemas with a reasonable number of tables
+        -- Fuzzy.FuzzySet is memory heavy we just don't use it for large schemas
+        Fuzzy.fromList <$> HM.filter ((< maxDbTablesForFuzzySearch) . length) (HM.fromListWith (<>) ((qiSchema &&& pure . qiName) <$> HM.keys tabsWViewsPks))
     }
   where
     schemas = toList configDbSchemas
@@ -203,6 +218,7 @@ removeInternal schemas dbStruct =
     , dbRepresentations = dbRepresentations dbStruct -- no need to filter, not directly exposed through the API
     , dbMediaHandlers   = dbMediaHandlers dbStruct
     , dbTimezones       = dbTimezones dbStruct
+    , dbTablesFuzzyIndex = dbTablesFuzzyIndex dbStruct
     }
   where
     hasInternalJunction ComputedRelationship{} = False

src/PostgREST/SchemaCache/Identifiers.hs

@@ -2,14 +2,17 @@
 {-# LANGUAGE DeriveGeneric  #-}
 
 module PostgREST.SchemaCache.Identifiers
-  ( QualifiedIdentifier(..)
+  ( FieldName
+  , QualifiedIdentifier(..)
   , RelIdentifier(..)
-  , isAnyElement
   , Schema
   , TableName
-  , FieldName
   , dumpQi
+  , escapeIdent
+  , isAnyElement
+  , quoteQi
   , toQi
+  , trimNullChars
   ) where
 
 import qualified Data.Aeson as JSON
@@ -39,6 +42,10 @@ dumpQi :: QualifiedIdentifier -> Text
 dumpQi (QualifiedIdentifier s i) =
   (if T.null s then mempty else s <> ".") <> i
 
+quoteQi :: QualifiedIdentifier -> Text
+quoteQi (QualifiedIdentifier s i) =
+  (if T.null s then mempty else escapeIdent s <> ".") <> escapeIdent i
+
 -- TODO: Handle a case where the QI comes like this: "my.fav.schema"."my.identifier"
 -- Right now it only handles the schema.identifier case
 toQi :: Text -> QualifiedIdentifier
@@ -46,6 +53,12 @@ toQi txt = case T.drop 1 <$> T.breakOn "." txt of
   (i, "") -> QualifiedIdentifier mempty i
   (s, i)  -> QualifiedIdentifier s i
 
+escapeIdent :: Text -> Text
+escapeIdent x = "\"" <> T.replace "\"" "\"\"" (trimNullChars x) <> "\""
+
+trimNullChars :: Text -> Text
+trimNullChars = T.takeWhile (/= '\x0')
+
 type Schema = Text
 type TableName = Text
 type FieldName = Text

src/PostgREST/SchemaCache/Routine.hs

@@ -90,7 +90,7 @@ data RoutineParam = RoutineParam
 instance Ord Routine where
   Function schema1 name1 des1 prms1 rt1 vol1 hasVar1 iso1 sets1 `compare` Function schema2 name2 des2 prms2 rt2 vol2 hasVar2 iso2 sets2
     | schema1 == schema2 && name1 == name2 && length prms1 < length prms2  = LT
-    | schema2 == schema2 && name1 == name2 && length prms1 > length prms2  = GT
+    | schema1 == schema2 && name1 == name2 && length prms1 > length prms2  = GT
     | otherwise = (schema1, name1, des1, prms1, rt1, vol1, hasVar1, iso1, sets1) `compare` (schema2, name2, des2, prms2, rt2, vol2, hasVar2, iso2, sets2)
 
 -- | A map of all procs, all of which can be overloaded(one entry will have more than one Routine).

src/PostgREST/TimeIt.hs

@@ -0,0 +1,20 @@
+module PostgREST.TimeIt
+  ( timeItT
+  ) where
+
+import GHC.Clock
+import Protolude
+
+{-
+ - The signature is the same as https://hackage.haskell.org/package/timeit-2.0/docs/src/System-TimeIt.html#timeIt,
+ - we vendor this functionality because it gave errors as shown on https://github.com/PostgREST/postgrest/issues/4522 plus
+ - the function is small enough. This vendored function is different in that the result is in milliseconds.
+ -}
+timeItT :: MonadIO m => m a -> m (Double, a)
+timeItT p = do
+  s <- liftIO getMonotonicTime
+  x <- p
+  e <- liftIO getMonotonicTime
+  let time = (e - s) * 1000
+  return (time, x)
+

src/PostgREST/Unix.hs

@@ -11,26 +11,27 @@ import qualified System.Posix.Signals as Signals
 import System.Posix.Types       (FileMode)
 import System.PosixCompat.Files (setFileMode)
 
-import           Data.String      (String)
+import           Data.String           (String)
-import qualified Network.Socket   as NS
+import qualified Network.Socket        as NS
+import qualified PostgREST.Observation as Observation
 import           Protolude
-import           System.Directory (removeFile)
+import           System.Directory      (removeFile)
-import           System.IO.Error  (isDoesNotExistError)
+import           System.IO.Error       (isDoesNotExistError)
 
 -- | Set signal handlers, only for systems with signals
-installSignalHandlers :: ThreadId -> IO () -> IO () -> IO ()
+installSignalHandlers :: Observation.ObservationHandler -> ThreadId -> IO () -> IO () -> IO ()
 #ifndef mingw32_HOST_OS
-installSignalHandlers tid usr1 usr2 = do
+installSignalHandlers observer tid usr1 usr2 = do
   let interrupt = throwTo tid UserInterrupt
-  install Signals.sigINT interrupt
+  install Signals.sigINT  $ observer (Observation.TerminationUnixSignalObs "SIGINT") >> interrupt
-  install Signals.sigTERM interrupt
+  install Signals.sigTERM $ observer (Observation.TerminationUnixSignalObs "SIGTERM") >> interrupt
   install Signals.sigUSR1 usr1
   install Signals.sigUSR2 usr2
   where
     install signal handler =
       void $ Signals.installHandler signal (Signals.Catch handler) Nothing
 #else
-installSignalHandlers _ _ _ = pass
+installSignalHandlers _ _ _ _ = pass
 #endif
 
 -- | Create a unix domain socket and bind it to the given path.

stack.yaml

@@ -14,3 +14,4 @@ extra-deps:
   - hasql-pool-1.0.1
   - jose-jwt-0.10.0
   - postgresql-libpq-0.10.1.0
+  - streaming-commons-0.2.3.1

stack.yaml.lock

@@ -39,6 +39,13 @@ packages:
       size: 1096
   original:
     hackage: postgresql-libpq-0.10.1.0
+- completed:
+    hackage: streaming-commons-0.2.3.1@sha256:ed7999fea9e912b1211ea93d7e20a7998bf4753166370c94048885650f303bf0,4841
+    pantry-tree:
+      sha256: f08e83fb00fd45865fa8cfdef5ecef7161d5135257ee98050bfbe4b807ad65f8
+      size: 2374
+  original:
+    hackage: streaming-commons-0.2.3.1
 snapshots:
 - completed:
     sha256: 238fa745b64f91184f9aa518fe04bdde6552533d169b0da5256670df83a0f1a9

static/bytebase-dark.svg

@@ -0,0 +1,7 @@
+<svg width="1024" height="220" viewBox="0 0 1024 220" fill="none" xmlns="http://www.w3.org/2000/svg">
+<path d="M267.945 167.731H314.905C342.254 167.731 359.527 154.103 359.527 133.123C359.527 117.522 352.69 106.404 332.178 101.563C347.652 95.8246 352.51 87.2173 352.51 75.203C352.51 55.6573 337.756 42.2085 310.407 42.2085H267.945V167.731ZM293.134 145.137V114.474H312.566C326.241 114.474 333.978 120.032 333.978 129.178C333.978 140.116 326.241 145.137 312.566 145.137H293.134ZM293.134 94.0314V64.6232H308.068C321.743 64.6232 326.96 70.5407 326.96 79.148C326.96 89.1898 321.743 94.0314 308.068 94.0314H293.134ZM378.999 205.208C400.95 205.208 412.645 192.477 420.562 173.649L461.945 74.4857H433.157L414.984 127.743C413.365 132.226 411.925 137.785 411.026 142.627C410.126 137.785 408.687 132.226 407.067 127.743L388.895 74.4857H359.567L396.632 163.427L395.372 167.372C392.673 175.621 386.736 181.539 377.38 181.539C373.421 181.539 370.003 181.18 366.404 180.283L362.806 203.415C367.664 204.85 374.141 205.208 378.999 205.208Z" fill="white"/>
+<path d="M509.624 167.881H522.918V146.401H512.858C508.187 146.401 505.312 143.716 505.312 137.809V95.3848H525.074V74.7993H505.312V53.4979L481.059 56.0039V74.7993H467.945V95.3848H481.059V139.062C481.059 159.289 491.12 167.881 509.624 167.881ZM575.459 170.208C593.963 170.208 609.592 161.974 617.138 150.339L598.274 136.198C593.963 143.358 585.878 148.728 575.459 148.728C563.242 148.728 554.978 141.031 552.283 130.112H622.168C622.707 127.069 622.887 124.204 622.887 121.519C622.887 94.6688 603.484 72.4723 575.459 72.4723C547.433 72.4723 527.132 94.6688 527.132 121.519C527.132 148.37 546.535 170.208 575.459 170.208ZM552.283 112.032C554.978 100.934 563.602 93.2368 575.099 93.2368C586.956 93.2368 595.759 101.65 598.454 112.032H552.283ZM687.249 170.208C712.939 170.208 733.42 148.549 733.42 121.698C733.42 94.8478 712.939 72.8303 687.249 72.8303C671.26 72.8303 662.817 79.2744 657.786 89.1197V37.2085H633.533V167.881H657.068V152.666C662.098 163.227 670.542 170.208 687.249 170.208ZM657.068 121.698C657.068 107.199 667.308 94.6688 683.297 94.6688C698.747 94.6688 709.167 107.02 709.167 121.519C709.167 135.661 698.747 148.37 683.297 148.37C667.308 148.37 657.068 136.198 657.068 121.698ZM785.937 170.208C802.645 170.208 811.088 163.227 816.119 152.666V167.881H839.653V74.7993H815.58V89.4777C810.549 79.4534 802.106 72.8303 785.937 72.8303C760.247 72.8303 739.767 94.8478 739.767 121.698C739.767 148.549 760.247 170.208 785.937 170.208ZM764.02 121.519C764.02 107.02 774.439 94.6688 789.889 94.6688C805.878 94.6688 816.119 107.199 816.119 121.698C816.119 136.198 805.878 148.37 789.889 148.37C774.439 148.37 764.02 135.661 764.02 121.519ZM886.04 170.208C906.7 170.208 922.33 157.32 922.33 140.136C922.33 126.173 916.042 116.149 888.915 109.168C877.776 106.304 876.339 103.44 876.339 100.218C876.339 94.4898 881.369 91.9837 888.016 91.9837C894.304 91.9837 900.053 94.8478 904.544 102.008L922.15 88.5827C912.629 76.2314 902.568 72.4723 887.477 72.4723C865.56 72.4723 852.625 84.6446 852.625 100.934C852.625 112.748 856.398 124.025 881.369 129.933C896.101 133.334 898.436 136.019 898.436 140.852C898.436 146.401 893.765 150.518 885.501 150.518C877.597 150.518 870.411 146.759 863.584 139.062L847.235 153.919C857.476 164.838 870.231 170.208 886.04 170.208ZM976.517 170.208C995.021 170.208 1010.65 161.974 1018.2 150.339L999.332 136.198C995.021 143.358 986.936 148.728 976.517 148.728C964.3 148.728 956.036 141.031 953.342 130.112H1023.23C1023.77 127.069 1023.94 124.204 1023.94 121.519C1023.94 94.6688 1004.54 72.4723 976.517 72.4723C948.491 72.4723 928.19 94.6688 928.19 121.519C928.19 148.37 947.593 170.208 976.517 170.208ZM953.342 112.032C956.036 100.934 964.66 93.2368 976.157 93.2368C988.014 93.2368 996.817 101.65 999.512 112.032H953.342Z" fill="white"/>
+<path d="M63.3182 209.286C40.9935 198.863 22.8237 181.221 11.7365 159.201C0.649395 137.179 -2.70929 112.063 2.20168 87.8978C7.11264 63.733 20.0073 41.927 38.8077 25.9936C57.6082 10.0604 81.2196 0.927572 105.837 0.0669197C130.455 -0.793732 154.645 6.66803 174.511 21.2495C194.375 35.8312 208.758 56.6835 215.339 80.4466C221.924 104.21 220.322 129.499 210.797 152.241C201.271 174.982 184.374 193.851 162.831 205.806L109.664 109.786L63.3182 209.286Z" fill="white"/>
+<path d="M162.726 205.864C147.617 214.226 130.748 218.901 113.494 219.504C96.24 220.108 79.0873 216.625 63.4319 209.338L109.665 109.785L162.726 205.864Z" fill="#111827"/>
+<path d="M47.9785 64.5504H172.557V117.247C172.557 130.949 169.34 141.254 162.907 148.162C156.59 155.19 148.402 158.703 138.342 158.703C131.908 158.703 126.293 157.123 121.497 153.961C116.818 150.915 113.251 146.7 110.794 141.314C108.572 147.167 105.062 151.793 100.266 155.19C95.4705 158.703 89.7972 160.46 83.2466 160.46C72.2509 160.46 63.5947 156.947 57.2781 149.92C51.0784 142.894 47.9785 132.764 47.9785 119.531V64.5504ZM149.572 89.4936H122.55V117.072C122.55 122.107 123.661 125.972 125.884 128.665C128.224 131.476 131.616 132.88 136.061 132.88C140.038 132.88 143.255 131.535 145.711 128.842C148.284 126.265 149.572 122.341 149.572 117.072V89.4936ZM99.3891 89.4936H70.9642V118.828C70.9642 124.098 72.1924 128.022 74.6489 130.598C77.1054 133.291 80.6146 134.637 85.1766 134.637C89.5047 134.637 92.9555 133.291 95.529 130.598C98.1024 128.022 99.3891 124.098 99.3891 118.828V89.4936Z" fill="#111827"/>
+</svg>

static/bytebase.svg

@@ -0,0 +1,7 @@
+<svg width="1024" height="220" viewBox="0 0 1024 220" fill="none" xmlns="http://www.w3.org/2000/svg">
+<path d="M268 167.731H314.961C342.309 167.731 359.582 154.103 359.582 133.123C359.582 117.522 352.745 106.404 332.234 101.563C347.707 95.8246 352.565 87.2173 352.565 75.203C352.565 55.6573 337.811 42.2085 310.463 42.2085H268V167.731ZM293.19 145.137V114.474H312.622C326.296 114.474 334.033 120.032 334.033 129.178C334.033 140.116 326.296 145.137 312.622 145.137H293.19ZM293.19 94.0314V64.6232H308.123C321.798 64.6232 327.016 70.5407 327.016 79.148C327.016 89.1898 321.798 94.0314 308.123 94.0314H293.19ZM379.054 205.208C401.005 205.208 412.7 192.477 420.617 173.649L462 74.4857H433.212L415.039 127.743C413.42 132.226 411.981 137.785 411.081 142.627C410.181 137.785 408.742 132.226 407.123 127.743L388.95 74.4857H359.622L396.687 163.427L395.427 167.372C392.729 175.621 386.791 181.539 377.435 181.539C373.476 181.539 370.058 181.18 366.459 180.283L362.861 203.415C367.719 204.85 374.196 205.208 379.054 205.208Z" fill="#111827"/>
+<path d="M509.679 167.881H522.973V146.401H512.913C508.242 146.401 505.368 143.716 505.368 137.809V95.3848H525.129V74.7993H505.368V53.4979L481.115 56.0039V74.7993H468V95.3848H481.115V139.062C481.115 159.289 491.175 167.881 509.679 167.881ZM575.514 170.208C594.018 170.208 609.648 161.974 617.193 150.339L598.33 136.198C594.018 143.358 585.934 148.728 575.514 148.728C563.298 148.728 555.034 141.031 552.339 130.112H622.223C622.762 127.069 622.942 124.204 622.942 121.519C622.942 94.6688 603.539 72.4723 575.514 72.4723C547.488 72.4723 527.188 94.6688 527.188 121.519C527.188 148.37 546.59 170.208 575.514 170.208ZM552.339 112.032C555.034 100.934 563.657 93.2368 575.155 93.2368C587.012 93.2368 595.814 101.65 598.509 112.032H552.339ZM687.305 170.208C712.995 170.208 733.475 148.549 733.475 121.698C733.475 94.8478 712.995 72.8303 687.305 72.8303C671.316 72.8303 662.872 79.2744 657.842 89.1197V37.2085H633.589V167.881H657.123V152.666C662.153 163.227 670.597 170.208 687.305 170.208ZM657.123 121.698C657.123 107.199 667.363 94.6688 683.352 94.6688C698.802 94.6688 709.222 107.02 709.222 121.519C709.222 135.661 698.802 148.37 683.352 148.37C667.363 148.37 657.123 136.198 657.123 121.698ZM785.992 170.208C802.7 170.208 811.144 163.227 816.174 152.666V167.881H839.708V74.7993H815.635V89.4777C810.605 79.4534 802.161 72.8303 785.992 72.8303C760.302 72.8303 739.822 94.8478 739.822 121.698C739.822 148.549 760.302 170.208 785.992 170.208ZM764.075 121.519C764.075 107.02 774.495 94.6688 789.945 94.6688C805.934 94.6688 816.174 107.199 816.174 121.698C816.174 136.198 805.934 148.37 789.945 148.37C774.495 148.37 764.075 135.661 764.075 121.519ZM886.096 170.208C906.756 170.208 922.385 157.32 922.385 140.136C922.385 126.173 916.097 116.149 888.97 109.168C877.832 106.304 876.394 103.44 876.394 100.218C876.394 94.4898 881.425 91.9837 888.072 91.9837C894.36 91.9837 900.108 94.8478 904.6 102.008L922.206 88.5827C912.684 76.2314 902.624 72.4723 887.533 72.4723C865.615 72.4723 852.68 84.6446 852.68 100.934C852.68 112.748 856.453 124.025 881.425 129.933C896.156 133.334 898.492 136.019 898.492 140.852C898.492 146.401 893.821 150.518 885.557 150.518C877.652 150.518 870.466 146.759 863.639 139.062L847.291 153.919C857.531 164.838 870.286 170.208 886.096 170.208ZM976.572 170.208C995.076 170.208 1010.71 161.974 1018.25 150.339L999.388 136.198C995.076 143.358 986.992 148.728 976.572 148.728C964.356 148.728 956.092 141.031 953.397 130.112H1023.28C1023.82 127.069 1024 124.204 1024 121.519C1024 94.6688 1004.6 72.4723 976.572 72.4723C948.546 72.4723 928.246 94.6688 928.246 121.519C928.246 148.37 947.648 170.208 976.572 170.208ZM953.397 112.032C956.092 100.934 964.715 93.2368 976.213 93.2368C988.07 93.2368 996.873 101.65 999.567 112.032H953.397Z" fill="#111827"/>
+<path d="M63.3735 209.286C41.0488 198.863 22.879 181.221 11.7918 159.201C0.704692 137.179 -2.65399 112.063 2.25697 87.8978C7.16794 63.733 20.0626 41.927 38.863 25.9936C57.6635 10.0604 81.2749 0.927572 105.892 0.0669197C130.51 -0.793732 154.7 6.66803 174.566 21.2495C194.43 35.8312 208.813 56.6835 215.395 80.4466C221.979 104.21 220.378 129.499 210.852 152.241C201.326 174.982 184.429 193.851 162.886 205.806L109.72 109.786L63.3735 209.286Z" fill="#111827"/>
+<path d="M162.781 205.864C147.672 214.226 130.804 218.901 113.549 219.504C96.2953 220.108 79.1426 216.625 63.4872 209.338L109.72 109.785L162.781 205.864Z" fill="white"/>
+<path d="M48.0338 64.5504H172.612V117.247C172.612 130.949 169.396 141.254 162.962 148.162C156.645 155.19 148.458 158.703 138.397 158.703C131.964 158.703 126.349 157.123 121.553 153.961C116.874 150.915 113.306 146.7 110.85 141.314C108.627 147.167 105.118 151.793 100.322 155.19C95.5258 158.703 89.8525 160.46 83.3019 160.46C72.3062 160.46 63.65 156.947 57.3333 149.92C51.1337 142.894 48.0338 132.764 48.0338 119.531V64.5504ZM149.627 89.4936H122.606V117.072C122.606 122.107 123.717 125.972 125.939 128.665C128.279 131.476 131.671 132.88 136.116 132.88C140.093 132.88 143.31 131.535 145.767 128.842C148.34 126.265 149.627 122.341 149.627 117.072V89.4936ZM99.4444 89.4936H71.0194V118.828C71.0194 124.098 72.2477 128.022 74.7042 130.598C77.1607 133.291 80.6699 134.637 85.2319 134.637C89.56 134.637 93.0108 133.291 95.5843 130.598C98.1577 128.022 99.4444 124.098 99.4444 118.828V89.4936Z" fill="white"/>
+</svg>

static/supabase-dark.svg

@@ -0,0 +1,23 @@
+<svg width="581" height="113" viewBox="0 0 581 113" fill="none" xmlns="http://www.w3.org/2000/svg">
+<path d="M151.397 66.7608C151.996 72.3621 157.091 81.9642 171.877 81.9642C184.764 81.9642 190.959 73.7624 190.959 65.7607C190.959 58.559 186.063 52.6577 176.373 50.6571L169.379 49.1569C166.682 48.6568 164.884 47.1565 164.884 44.7559C164.884 41.9552 167.681 39.8549 171.178 39.8549C176.772 39.8549 178.87 43.5556 179.27 46.4564L190.359 43.9558C189.76 38.6546 185.064 29.7527 171.078 29.7527C160.488 29.7527 152.696 37.0543 152.696 45.8561C152.696 52.7576 156.991 58.4591 166.482 60.5594L172.976 62.0598C176.772 62.8599 178.271 64.6605 178.271 66.8609C178.271 69.4615 176.173 71.762 171.777 71.762C165.983 71.762 163.085 68.1611 162.786 64.2602L151.397 66.7608Z" fill="white"/>
+<path d="M233.421 80.4639H246.109C245.909 78.7635 245.609 75.3628 245.609 71.5618V31.2529H232.321V59.8592C232.321 65.5606 228.925 69.5614 223.031 69.5614C216.837 69.5614 214.039 65.1604 214.039 59.6592V31.2529H200.752V62.3599C200.752 73.0622 207.545 81.7642 219.434 81.7642C224.628 81.7642 230.325 79.7638 233.022 75.1627C233.022 77.1631 233.221 79.4636 233.421 80.4639Z" fill="white"/>
+<path d="M273.076 99.4682V75.663C275.473 78.9636 280.469 81.6644 287.263 81.6644C301.149 81.6644 310.439 70.6617 310.439 55.7584C310.439 41.1553 302.148 30.1528 287.762 30.1528C280.37 30.1528 274.875 33.4534 272.677 37.2544V31.253H259.79V99.4682H273.076ZM297.352 55.8585C297.352 64.6606 291.958 69.7616 285.164 69.7616C278.372 69.7616 272.877 64.5605 272.877 55.8585C272.877 47.1566 278.372 42.0554 285.164 42.0554C291.958 42.0554 297.352 47.1566 297.352 55.8585Z" fill="white"/>
+<path d="M317.964 67.0609C317.964 74.7627 324.357 81.8643 334.848 81.8643C342.139 81.8643 346.835 78.4634 349.332 74.5625C349.332 76.463 349.532 79.1635 349.832 80.4639H362.02C361.72 78.7635 361.422 75.2627 361.422 72.6622V48.4567C361.422 38.5545 355.627 29.7527 340.043 29.7527C326.855 29.7527 319.761 38.2544 318.963 45.9562L330.751 48.4567C331.151 44.1558 334.348 40.455 340.141 40.455C345.737 40.455 348.434 43.3556 348.434 46.8564C348.434 48.5568 347.536 49.9572 344.738 50.3572L332.65 52.1576C324.458 53.3579 317.964 58.2589 317.964 67.0609ZM337.644 71.962C333.349 71.962 331.25 69.1614 331.25 66.2608C331.25 62.4599 333.947 60.5594 337.345 60.0594L348.434 58.359V60.5594C348.434 69.2615 343.239 71.962 337.644 71.962Z" fill="white"/>
+<path d="M387.703 80.4641V74.4627C390.299 78.6637 395.494 81.6644 402.288 81.6644C416.276 81.6644 425.467 70.5618 425.467 55.6585C425.467 41.0552 417.174 29.9528 402.788 29.9528C395.494 29.9528 390.1 33.1535 387.902 36.6541V8.04785H374.815V80.4641H387.703ZM412.178 55.7584C412.178 64.7605 406.784 69.7616 399.99 69.7616C393.297 69.7616 387.703 64.6606 387.703 55.7584C387.703 46.7564 393.297 41.8554 399.99 41.8554C406.784 41.8554 412.178 46.7564 412.178 55.7584Z" fill="white"/>
+<path d="M432.99 67.0609C432.99 74.7627 439.383 81.8643 449.873 81.8643C457.165 81.8643 461.862 78.4634 464.358 74.5625C464.358 76.463 464.559 79.1635 464.858 80.4639H477.046C476.748 78.7635 476.448 75.2627 476.448 72.6622V48.4567C476.448 38.5545 470.653 29.7527 455.068 29.7527C441.881 29.7527 434.788 38.2544 433.989 45.9562L445.776 48.4567C446.177 44.1558 449.374 40.455 455.167 40.455C460.763 40.455 463.46 43.3556 463.46 46.8564C463.46 48.5568 462.561 49.9572 459.763 50.3572L447.676 52.1576C439.484 53.3579 432.99 58.2589 432.99 67.0609ZM452.671 71.962C448.375 71.962 446.276 69.1614 446.276 66.2608C446.276 62.4599 448.973 60.5594 452.371 60.0594L463.46 58.359V60.5594C463.46 69.2615 458.265 71.962 452.671 71.962Z" fill="white"/>
+<path d="M485.645 66.7608C486.243 72.3621 491.339 81.9642 506.124 81.9642C519.012 81.9642 525.205 73.7624 525.205 65.7607C525.205 58.559 520.311 52.6577 510.62 50.6571L503.626 49.1569C500.929 48.6568 499.132 47.1565 499.132 44.7559C499.132 41.9552 501.928 39.8549 505.425 39.8549C511.021 39.8549 513.118 43.5556 513.519 46.4564L524.607 43.9558C524.007 38.6546 519.312 29.7527 505.326 29.7527C494.735 29.7527 486.944 37.0543 486.944 45.8561C486.944 52.7576 491.238 58.4591 500.73 60.5594L507.224 62.0598C511.021 62.8599 512.519 64.6605 512.519 66.8609C512.519 69.4615 510.421 71.762 506.025 71.762C500.23 71.762 497.334 68.1611 497.034 64.2602L485.645 66.7608Z" fill="white"/>
+<path d="M545.385 50.2571C545.685 45.7562 549.482 40.5549 556.375 40.5549C563.967 40.5549 567.165 45.3561 567.365 50.2571H545.385ZM568.664 63.0601C567.065 67.4609 563.668 70.5617 557.474 70.5617C550.88 70.5617 545.385 65.8606 545.087 59.3593H580.252C580.252 59.159 580.451 57.1587 580.451 55.2582C580.451 39.4547 571.361 29.7527 556.175 29.7527C543.588 29.7527 531.998 39.9548 531.998 55.6584C531.998 72.262 543.886 81.9642 557.374 81.9642C569.462 81.9642 577.255 74.8626 579.753 66.3607L568.664 63.0601Z" fill="white"/>
+<path d="M63.7076 110.284C60.8481 113.885 55.0502 111.912 54.9813 107.314L53.9738 40.0627L99.1935 40.0627C107.384 40.0627 111.952 49.5228 106.859 55.9374L63.7076 110.284Z" fill="url(#paint0_linear)"/>
+<path d="M63.7076 110.284C60.8481 113.885 55.0502 111.912 54.9813 107.314L53.9738 40.0627L99.1935 40.0627C107.384 40.0627 111.952 49.5228 106.859 55.9374L63.7076 110.284Z" fill="url(#paint1_linear)" fill-opacity="0.2"/>
+<path d="M45.317 2.07103C48.1765 -1.53037 53.9745 0.442937 54.0434 5.041L54.4849 72.2922H9.83113C1.64038 72.2922 -2.92775 62.8321 2.1655 56.4175L45.317 2.07103Z" fill="#3ECF8E"/>
+<defs>
+<linearGradient id="paint0_linear" x1="53.9738" y1="54.974" x2="94.1635" y2="71.8295" gradientUnits="userSpaceOnUse">
+<stop stop-color="#249361"/>
+<stop offset="1" stop-color="#3ECF8E"/>
+</linearGradient>
+<linearGradient id="paint1_linear" x1="36.1558" y1="30.578" x2="54.4844" y2="65.0806" gradientUnits="userSpaceOnUse">
+<stop/>
+<stop offset="1" stop-opacity="0"/>
+</linearGradient>
+</defs>
+</svg>

static/supabase.svg

@@ -0,0 +1,23 @@
+<svg width="581" height="113" viewBox="0 0 581 113" fill="none" xmlns="http://www.w3.org/2000/svg">
+<path d="M151.397 66.7608C151.996 72.3621 157.091 81.9642 171.877 81.9642C184.764 81.9642 190.959 73.7624 190.959 65.7607C190.959 58.559 186.063 52.6577 176.373 50.6571L169.379 49.1569C166.682 48.6568 164.884 47.1565 164.884 44.7559C164.884 41.9552 167.681 39.8549 171.178 39.8549C176.772 39.8549 178.87 43.5556 179.27 46.4564L190.359 43.9558C189.76 38.6546 185.064 29.7527 171.078 29.7527C160.488 29.7527 152.696 37.0543 152.696 45.8561C152.696 52.7576 156.991 58.4591 166.482 60.5594L172.976 62.0598C176.772 62.8599 178.271 64.6605 178.271 66.8609C178.271 69.4615 176.173 71.762 171.777 71.762C165.983 71.762 163.085 68.1611 162.786 64.2602L151.397 66.7608Z" fill="#1F1F1F"/>
+<path d="M233.421 80.4639H246.109C245.909 78.7635 245.609 75.3628 245.609 71.5618V31.2529H232.321V59.8592C232.321 65.5606 228.925 69.5614 223.031 69.5614C216.837 69.5614 214.039 65.1604 214.039 59.6592V31.2529H200.752V62.3599C200.752 73.0622 207.545 81.7642 219.434 81.7642C224.628 81.7642 230.325 79.7638 233.022 75.1627C233.022 77.1631 233.221 79.4636 233.421 80.4639Z" fill="#1F1F1F"/>
+<path d="M273.076 99.4682V75.663C275.473 78.9636 280.469 81.6644 287.263 81.6644C301.149 81.6644 310.439 70.6617 310.439 55.7584C310.439 41.1553 302.148 30.1528 287.762 30.1528C280.37 30.1528 274.875 33.4534 272.677 37.2544V31.253H259.79V99.4682H273.076ZM297.352 55.8585C297.352 64.6606 291.958 69.7616 285.164 69.7616C278.372 69.7616 272.877 64.5605 272.877 55.8585C272.877 47.1566 278.372 42.0554 285.164 42.0554C291.958 42.0554 297.352 47.1566 297.352 55.8585Z" fill="#1F1F1F"/>
+<path d="M317.964 67.0609C317.964 74.7627 324.357 81.8643 334.848 81.8643C342.139 81.8643 346.835 78.4634 349.332 74.5625C349.332 76.463 349.532 79.1635 349.832 80.4639H362.02C361.72 78.7635 361.422 75.2627 361.422 72.6622V48.4567C361.422 38.5545 355.627 29.7527 340.043 29.7527C326.855 29.7527 319.761 38.2544 318.963 45.9562L330.751 48.4567C331.151 44.1558 334.348 40.455 340.141 40.455C345.737 40.455 348.434 43.3556 348.434 46.8564C348.434 48.5568 347.536 49.9572 344.738 50.3572L332.65 52.1576C324.458 53.3579 317.964 58.2589 317.964 67.0609ZM337.644 71.962C333.349 71.962 331.25 69.1614 331.25 66.2608C331.25 62.4599 333.947 60.5594 337.345 60.0594L348.434 58.359V60.5594C348.434 69.2615 343.239 71.962 337.644 71.962Z" fill="#1F1F1F"/>
+<path d="M387.703 80.4641V74.4627C390.299 78.6637 395.494 81.6644 402.288 81.6644C416.276 81.6644 425.467 70.5618 425.467 55.6585C425.467 41.0552 417.174 29.9528 402.788 29.9528C395.494 29.9528 390.1 33.1535 387.902 36.6541V8.04785H374.815V80.4641H387.703ZM412.178 55.7584C412.178 64.7605 406.784 69.7616 399.99 69.7616C393.297 69.7616 387.703 64.6606 387.703 55.7584C387.703 46.7564 393.297 41.8554 399.99 41.8554C406.784 41.8554 412.178 46.7564 412.178 55.7584Z" fill="#1F1F1F"/>
+<path d="M432.99 67.0609C432.99 74.7627 439.383 81.8643 449.873 81.8643C457.165 81.8643 461.862 78.4634 464.358 74.5625C464.358 76.463 464.559 79.1635 464.858 80.4639H477.046C476.748 78.7635 476.448 75.2627 476.448 72.6622V48.4567C476.448 38.5545 470.653 29.7527 455.068 29.7527C441.881 29.7527 434.788 38.2544 433.989 45.9562L445.776 48.4567C446.177 44.1558 449.374 40.455 455.167 40.455C460.763 40.455 463.46 43.3556 463.46 46.8564C463.46 48.5568 462.561 49.9572 459.763 50.3572L447.676 52.1576C439.484 53.3579 432.99 58.2589 432.99 67.0609ZM452.671 71.962C448.375 71.962 446.276 69.1614 446.276 66.2608C446.276 62.4599 448.973 60.5594 452.371 60.0594L463.46 58.359V60.5594C463.46 69.2615 458.265 71.962 452.671 71.962Z" fill="#1F1F1F"/>
+<path d="M485.645 66.7608C486.243 72.3621 491.339 81.9642 506.124 81.9642C519.012 81.9642 525.205 73.7624 525.205 65.7607C525.205 58.559 520.311 52.6577 510.62 50.6571L503.626 49.1569C500.929 48.6568 499.132 47.1565 499.132 44.7559C499.132 41.9552 501.928 39.8549 505.425 39.8549C511.021 39.8549 513.118 43.5556 513.519 46.4564L524.607 43.9558C524.007 38.6546 519.312 29.7527 505.326 29.7527C494.735 29.7527 486.944 37.0543 486.944 45.8561C486.944 52.7576 491.238 58.4591 500.73 60.5594L507.224 62.0598C511.021 62.8599 512.519 64.6605 512.519 66.8609C512.519 69.4615 510.421 71.762 506.025 71.762C500.23 71.762 497.334 68.1611 497.034 64.2602L485.645 66.7608Z" fill="#1F1F1F"/>
+<path d="M545.385 50.2571C545.685 45.7562 549.482 40.5549 556.375 40.5549C563.967 40.5549 567.165 45.3561 567.365 50.2571H545.385ZM568.664 63.0601C567.065 67.4609 563.668 70.5617 557.474 70.5617C550.88 70.5617 545.385 65.8606 545.087 59.3593H580.252C580.252 59.159 580.451 57.1587 580.451 55.2582C580.451 39.4547 571.361 29.7527 556.175 29.7527C543.588 29.7527 531.998 39.9548 531.998 55.6584C531.998 72.262 543.886 81.9642 557.374 81.9642C569.462 81.9642 577.255 74.8626 579.753 66.3607L568.664 63.0601Z" fill="#1F1F1F"/>
+<path d="M63.7076 110.284C60.8481 113.885 55.0502 111.912 54.9813 107.314L53.9738 40.0627L99.1935 40.0627C107.384 40.0627 111.952 49.5228 106.859 55.9374L63.7076 110.284Z" fill="url(#paint0_linear)"/>
+<path d="M63.7076 110.284C60.8481 113.885 55.0502 111.912 54.9813 107.314L53.9738 40.0627L99.1935 40.0627C107.384 40.0627 111.952 49.5228 106.859 55.9374L63.7076 110.284Z" fill="url(#paint1_linear)" fill-opacity="0.2"/>
+<path d="M45.317 2.07103C48.1765 -1.53037 53.9745 0.442937 54.0434 5.041L54.4849 72.2922H9.83113C1.64038 72.2922 -2.92775 62.8321 2.1655 56.4175L45.317 2.07103Z" fill="#3ECF8E"/>
+<defs>
+<linearGradient id="paint0_linear" x1="53.9738" y1="54.974" x2="94.1635" y2="71.8295" gradientUnits="userSpaceOnUse">
+<stop stop-color="#249361"/>
+<stop offset="1" stop-color="#3ECF8E"/>
+</linearGradient>
+<linearGradient id="paint1_linear" x1="36.1558" y1="30.578" x2="54.4844" y2="65.0806" gradientUnits="userSpaceOnUse">
+<stop/>
+<stop offset="1" stop-opacity="0"/>
+</linearGradient>
+</defs>
+</svg>

test/io/__snapshots__/test_cli/test_schema_cache_snapshot[dbRelationships].yaml

@@ -1 +1,39 @@
-[]
+- - - qiName: directors
+      qiSchema: public
+    - public
+  - - relCardinality:
+        relColumns:
+        - - id
+          - director_id
+        relCons: fk_director
+        tag: O2M
+      relFTableIsView: false
+      relForeignTable:
+        qiName: films
+        qiSchema: public
+      relIsSelf: false
+      relTable:
+        qiName: directors
+        qiSchema: public
+      relTableIsView: false
+      tag: Relationship
+
+- - - qiName: films
+      qiSchema: public
+    - public
+  - - relCardinality:
+        relColumns:
+        - - director_id
+          - id
+        relCons: fk_director
+        tag: M2O
+      relFTableIsView: false
+      relForeignTable:
+        qiName: directors
+        qiSchema: public
+      relIsSelf: false
+      relTable:
+        qiName: films
+        qiSchema: public
+      relTableIsView: false
+      tag: Relationship

test/io/__snapshots__/test_cli/test_schema_cache_snapshot[dbRoutines].yaml

@@ -106,6 +106,23 @@
       pdSchema: public
       pdVolatility: Volatile
 
+- - qiName: notify_pgrst
+    qiSchema: public
+  - - pdDescription: null
+      pdFuncSettings: []
+      pdHasVariadic: false
+      pdName: notify_pgrst
+      pdParams: []
+      pdReturnType:
+        contents:
+          contents:
+            qiName: void
+            qiSchema: pg_catalog
+          tag: Scalar
+        tag: Single
+      pdSchema: public
+      pdVolatility: Volatile
+
 - - qiName: migrate_function
     qiSchema: public
   - - pdDescription: null
@@ -461,6 +478,23 @@
       pdSchema: public
       pdVolatility: Volatile
 
+- - qiName: 'true'
+    qiSchema: public
+  - - pdDescription: null
+      pdFuncSettings: []
+      pdHasVariadic: false
+      pdName: 'true'
+      pdParams: []
+      pdReturnType:
+        contents:
+          contents:
+            qiName: bool
+            qiSchema: pg_catalog
+          tag: Scalar
+        tag: Single
+      pdSchema: public
+      pdVolatility: Volatile
+
 - - qiName: create_function
     qiSchema: public
   - - pdDescription: null

test/io/__snapshots__/test_cli/test_schema_cache_snapshot[dbTables].yaml

@@ -71,6 +71,37 @@
     tableSchema: public
     tableUpdatable: true
 
+- - qiName: directors
+    qiSchema: public
+  - tableColumns:
+      id:
+        colDefault: null
+        colDescription: null
+        colEnum: []
+        colMaxLen: null
+        colName: id
+        colNominalType: integer
+        colNullable: false
+        colType: integer
+      name:
+        colDefault: null
+        colDescription: null
+        colEnum: []
+        colMaxLen: null
+        colName: name
+        colNominalType: text
+        colNullable: true
+        colType: text
+    tableDeletable: true
+    tableDescription: null
+    tableInsertable: true
+    tableIsView: false
+    tableName: directors
+    tablePKCols:
+    - id
+    tableSchema: public
+    tableUpdatable: true
+
 - - qiName: projects
     qiSchema: public
   - tableColumns: {}
@@ -95,6 +126,46 @@
     tableSchema: public
     tableUpdatable: false
 
+- - qiName: films
+    qiSchema: public
+  - tableColumns:
+      director_id:
+        colDefault: null
+        colDescription: null
+        colEnum: []
+        colMaxLen: null
+        colName: director_id
+        colNominalType: integer
+        colNullable: true
+        colType: integer
+      id:
+        colDefault: null
+        colDescription: null
+        colEnum: []
+        colMaxLen: null
+        colName: id
+        colNominalType: integer
+        colNullable: false
+        colType: integer
+      title:
+        colDefault: null
+        colDescription: null
+        colEnum: []
+        colMaxLen: null
+        colName: title
+        colNominalType: text
+        colNullable: true
+        colType: text
+    tableDeletable: true
+    tableDescription: null
+    tableInsertable: true
+    tableIsView: false
+    tableName: films
+    tablePKCols:
+    - id
+    tableSchema: public
+    tableUpdatable: true
+
 - - qiName: items
     qiSchema: public
   - tableColumns:

test/io/config.py

@@ -7,7 +7,9 @@ import yaml
 
 BASEDIR = pathlib.Path(os.path.realpath(__file__)).parent
 CONFIGSDIR = BASEDIR / "configs"
-FIXTURES = yaml.load((BASEDIR / "fixtures.yaml").read_text(), Loader=yaml.Loader)
+FIXTURES = yaml.load(
+    (BASEDIR / "fixtures/fixtures.yaml").read_text(), Loader=yaml.Loader
+)
 POSTGREST_BIN = shutil.which("postgrest")
 SECRET = "reallyreallyreallyreallyverysafe"
 

test/io/fixtures/big_schema.sql

@@ -5,7 +5,7 @@ We use it to test our metadata generation because it contains a good amount of d
 
 Custom roles and privileges were removed.
 
-postgrest-with-postgresql-14 -f test/io/big_schema.sql psql
+postgrest-with-pg-14 -f test/io/big_schema.sql psql
 
 Has 12 functions:
 
@@ -11375,12 +11375,34 @@ ALTER TABLE ONLY apflora.zielber
 
 ALTER TABLE apflora."user" ENABLE ROW LEVEL SECURITY;
 
+
+CREATE SCHEMA fuzzysearch;
+
+-- Create many tables to test fuzzy string search
+-- computing hints for non existing tables
+DO
+$$
+DECLARE
+  r record;
+BEGIN
+  FOR r IN
+    SELECT
+      format('CREATE TABLE fuzzysearch.unknown_table_%s ()', n) AS ct
+    FROM
+      generate_series(1, 499) n
+    LOOP
+      EXECUTE r.ct;
+    END LOOP;
+END
+$$;
+
 DROP ROLE IF EXISTS postgrest_test_anonymous;
 CREATE ROLE postgrest_test_anonymous;
 
 GRANT postgrest_test_anonymous TO :PGUSER;
 
 GRANT USAGE ON SCHEMA apflora TO postgrest_test_anonymous;
+GRANT USAGE ON SCHEMA fuzzysearch TO postgrest_test_anonymous;
 
 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA apflora
 TO postgrest_test_anonymous;

test/io/fixtures/load.sql

@@ -256,3 +256,45 @@ select * from projects;
 
 create or replace view infinite_recursion as
 select * from infinite_recursion;
+
+create or replace function "true"() returns boolean as $_$
+  select true;
+$_$ language sql;
+
+create or replace function notify_pgrst() returns void as $$
+  notify pgrst;
+$$ language sql;
+
+-- directors and films table can be used for resource embedding tests
+create table directors (
+  id int primary key,
+  name text
+);
+
+create table films (
+  id int primary key,
+  title text,
+  director_id int,
+
+  constraint fk_director
+    foreign key (director_id) references directors (id)
+    on update cascade
+    on delete cascade
+);
+
+-- data to test resource embedding
+truncate table directors cascade;
+insert into directors
+values (1, 'quentin tarantino'),
+       (2, 'christopher nolan'),
+       (3, 'yorgos lathinmos');
+
+truncate table films cascade;
+insert into films
+values (1, 'pulp fiction', 1),
+       (2, 'intersteller',2),
+       (3, 'dogtooth',3),
+       (4, 'reservoir dogs', 1);
+
+
+GRANT SELECT ON directors, films TO postgrest_test_anonymous, postgrest_test_w_superuser_settings;

test/io/postgrest.py

@@ -94,20 +94,28 @@ def run(
     "Run PostgREST and yield an endpoint that is ready for connections."
 
     with tempfile.TemporaryDirectory() as tmpdir:
+
+        # with python requests, "localhost" doesn't automatically resolves
+        # to [::1], hence we use this explicitly when host is ipv6 special
+        # address
+        ipv6_special_addresses = ["*6", "!6"]
+
+        localhost = "[::1]" if host in ipv6_special_addresses else "localhost"
+
         if port:
             env["PGRST_SERVER_PORT"] = str(port)
-            env["PGRST_SERVER_HOST"] = host or "localhost"
+            env["PGRST_SERVER_HOST"] = host or localhost
             # When constructing IPv6 address, host address should be bracketed like [host]
-            apihost = f"[{host}]" if host and is_ipv6(host) else "localhost"
+            apihost = f"[{host}]" if host and is_ipv6(host) else localhost
             baseurl = f"http://{apihost}:{port}"
         else:
             socketfile = pathlib.Path(tmpdir) / "postgrest.sock"
             env["PGRST_SERVER_UNIX_SOCKET"] = str(socketfile)
             baseurl = "http+unix://" + urllib.parse.quote_plus(str(socketfile))
 
-        adminport = freeport(port)
+        adminport = freeport(used_ports=[port])
         env["PGRST_ADMIN_SERVER_PORT"] = str(adminport)
-        adminhost = f"[{host}]" if host and is_ipv6(host) else "localhost"
+        adminhost = f"[{host}]" if host and is_ipv6(host) else localhost
         adminurl = f"http://{adminhost}:{adminport}"
 
         command = [POSTGREST_BIN]
@@ -157,14 +165,14 @@ def run(
                 process.wait()
 
 
-def freeport(used_port=None):
+def freeport(used_ports=None):
-    "Find a free port on localhost."
+    "Find an unused free port on localhost."
     while True:
         with contextlib.closing(socket.socket(socket.AF_INET, socket.SOCK_STREAM)) as s:
             s.bind(("", 0))
             s.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
             port = s.getsockname()[1]
-            if port != used_port:
+            if used_ports is None or port not in used_ports:
                 return port
 
 

test/io/test_auth.py

@@ -0,0 +1,427 @@
+"Auth related IO tests for PostgREST"
+
+from datetime import datetime, timedelta, timezone
+from operator import attrgetter
+import signal
+import time
+import pytest
+
+from config import BASEDIR, CONFIGSDIR, FIXTURES, SECRET
+from util import authheader, jwtauthheader, parse_server_timings_header
+from postgrest import (
+    run,
+    sleep_until_postgrest_config_reload,
+    sleep_until_postgrest_scache_reload,
+    wait_until_exit,
+)
+
+
+@pytest.mark.parametrize(
+    "secretpath",
+    [path for path in (BASEDIR / "secrets").iterdir() if path.suffix != ".jwt"],
+    ids=attrgetter("name"),
+)
+def test_read_secret_from_file(secretpath, defaultenv):
+    "Authorization should succeed when the secret is read from a file."
+
+    env = {**defaultenv, "PGRST_JWT_SECRET": f"@{secretpath}"}
+
+    if secretpath.suffix == ".b64":
+        env["PGRST_JWT_SECRET_IS_BASE64"] = "true"
+
+    secret = secretpath.read_bytes()
+    headers = authheader(secretpath.with_suffix(".jwt").read_text())
+
+    with run(stdin=secret, env=env) as postgrest:
+        response = postgrest.session.get("/authors_only", headers=headers)
+        print(response.text)
+        assert response.status_code == 200
+
+
+def test_read_secret_from_stdin(defaultenv):
+    "Authorization should succeed when the secret is read from stdin."
+
+    env = {**defaultenv, "PGRST_DB_CONFIG": "false", "PGRST_JWT_SECRET": "@/dev/stdin"}
+
+    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
+
+    with run(stdin=SECRET.encode(), env=env) as postgrest:
+        response = postgrest.session.get("/authors_only", headers=headers)
+        print(response.text)
+        assert response.status_code == 200
+
+
+# TODO: This test would fail right now, because of
+# https://github.com/PostgREST/postgrest/issues/2126
+@pytest.mark.skip
+def test_read_secret_from_stdin_dbconfig(defaultenv):
+    "Authorization should succeed when the secret is read from stdin with db-config=true."
+
+    env = {**defaultenv, "PGRST_DB_CONFIG": "true", "PGRST_JWT_SECRET": "@/dev/stdin"}
+
+    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
+
+    with run(stdin=SECRET.encode(), env=env) as postgrest:
+        response = postgrest.session.get("/authors_only", headers=headers)
+        print(response.text)
+        assert response.status_code == 200
+
+
+def test_jwt_errors(defaultenv):
+    "invalid JWT should throw error"
+
+    env = {**defaultenv, "PGRST_JWT_SECRET": SECRET, "PGRST_JWT_AUD": "io tests"}
+
+    def relativeSeconds(sec):
+        return int((datetime.now(timezone.utc) + timedelta(seconds=sec)).timestamp())
+
+    with run(env=env) as postgrest:
+        headers = jwtauthheader({}, "other secret")
+        response = postgrest.session.get("/", headers=headers)
+        assert response.status_code == 401
+        assert response.json()["message"] == "No suitable key or wrong key type"
+        assert (
+            response.json()["details"] == "None of the keys was able to decode the JWT"
+        )
+
+        headers = jwtauthheader({"role": "not_existing"}, SECRET)
+        response = postgrest.session.get("/", headers=headers)
+        assert response.status_code == 401
+        assert response.json()["message"] == 'role "not_existing" does not exist'
+
+        # -35 seconds, because we allow clock skew of 30 seconds
+        headers = jwtauthheader({"exp": relativeSeconds(-35)}, SECRET)
+        response = postgrest.session.get("/", headers=headers)
+        assert response.status_code == 401
+        assert response.json()["message"] == "JWT expired"
+
+        # 35 seconds, because we allow clock skew of 30 seconds
+        headers = jwtauthheader({"nbf": relativeSeconds(35)}, SECRET)
+        response = postgrest.session.get("/", headers=headers)
+        assert response.status_code == 401
+        assert response.json()["message"] == "JWT not yet valid"
+
+        # 35 seconds, because we allow clock skew of 35 seconds
+        headers = jwtauthheader({"iat": relativeSeconds(35)}, SECRET)
+        response = postgrest.session.get("/", headers=headers)
+        assert response.status_code == 401
+        assert response.json()["message"] == "JWT issued at future"
+
+        headers = jwtauthheader({"aud": "not set"}, SECRET)
+        response = postgrest.session.get("/", headers=headers)
+        assert response.status_code == 401
+        assert response.json()["message"] == "JWT not in audience"
+
+        # partial token, no signature
+        headers = authheader("eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.bm90IGFuIG9iamVjdA")
+        response = postgrest.session.get("/", headers=headers)
+        assert response.status_code == 401
+        assert response.json()["message"] == "Expected 3 parts in JWT; got 2"
+
+        # complete token but random characters
+        headers = authheader("quifquirndsjagnrgniur.fonvoienqhhdj.iuqvnvhojah")
+        response = postgrest.session.get("/", headers=headers)
+        assert response.status_code == 401
+        assert response.json()["message"] == "JWT cryptographic operation failed"
+
+        # token with algorithm "none"
+        headers = authheader(
+            "eyJ0eXAiOiJKV1QiLCJhbGciOiJub25lIn0.e30.yOBhlOIqn56T-4NvyEXCjfi3UmyQZ-BzXtePMO2NgRI"
+        )
+        response = postgrest.session.get("/", headers=headers)
+        assert response.status_code == 401
+        assert response.json()["message"] == "Wrong or unsupported encoding algorithm"
+        assert (
+            response.json()["details"]
+            == "JWT is unsecured but expected 'alg' was not 'none'"
+        )
+
+    env = {
+        **defaultenv,
+        "PGRST_SERVER_TIMING_ENABLED": "true",
+        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
+        "PGRST_JWT_SECRET": SECRET,
+    }
+
+    # for code coverage with cache enabled and server-timing enabled
+    with run(env=env) as postgrest:
+        response = postgrest.session.get("/authors_only")
+        assert response.status_code == 401
+        assert response.json()["message"] == "permission denied for table authors_only"
+
+    env = {
+        **defaultenv,
+        "PGRST_SERVER_TIMING_ENABLED": "false",
+        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
+        "PGRST_JWT_SECRET": SECRET,
+    }
+
+    # for code coverage with cache enabled and server-timing disabled
+    with run(env=env) as postgrest:
+        response = postgrest.session.get("/authors_only")
+        assert response.status_code == 401
+        assert response.json()["message"] == "permission denied for table authors_only"
+
+
+def test_fail_with_invalid_password(defaultenv):
+    "Connecting with an invalid password should fail without retries."
+    uri = f'postgresql://?dbname={defaultenv["PGDATABASE"]}&host={defaultenv["PGHOST"]}&user=some_protected_user&password=invalid_pass'
+    env = {**defaultenv, "PGRST_DB_URI": uri}
+    with run(env=env, wait_for_readiness=False) as postgrest:
+        exitCode = wait_until_exit(postgrest)
+        assert exitCode == 1
+
+
+@pytest.mark.parametrize(
+    "roleclaim", FIXTURES["roleclaims"], ids=lambda claim: claim["key"]
+)
+def test_role_claim_key(roleclaim, defaultenv):
+    "Authorization should depend on a correct role-claim-key and JWT claim."
+    env = {
+        **defaultenv,
+        "PGRST_JWT_ROLE_CLAIM_KEY": roleclaim["key"],
+        "PGRST_JWT_SECRET": SECRET,
+    }
+    headers = jwtauthheader(roleclaim["data"], SECRET)
+
+    with run(env=env) as postgrest:
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == roleclaim["expected_status"]
+
+
+@pytest.mark.parametrize(
+    "jwtaudroleclaim",
+    FIXTURES["jwtaudroleclaims"],
+    ids=lambda claim: claim["key"] + "_" + str(claim["expected_status"]),
+)
+def test_jwt_aud_in_role_claim_key(jwtaudroleclaim, defaultenv):
+    "Allows authorization with JWT aud claim in role-claim-key"
+
+    env = {
+        **defaultenv,
+        "PGRST_JWT_AUD": "postgrest_test_author",
+        "PGRST_JWT_ROLE_CLAIM_KEY": jwtaudroleclaim["key"],
+        "PGRST_JWT_SECRET": SECRET,
+    }
+
+    headers = jwtauthheader(jwtaudroleclaim["data"], SECRET)
+
+    with run(env=env) as postgrest:
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == jwtaudroleclaim["expected_status"]
+
+
+def test_iat_claim(defaultenv):
+    """
+    A claim with an 'iat' (issued at) attribute should be successful.
+
+    The PostgREST time cache leads to issues here, see:
+    https://github.com/PostgREST/postgrest/issues/1139
+
+    """
+
+    env = {**defaultenv, "PGRST_JWT_SECRET": SECRET}
+
+    claim = {"role": "postgrest_test_author", "iat": datetime.now(timezone.utc)}
+    headers = jwtauthheader(claim, SECRET)
+
+    with run(env=env) as postgrest:
+        for _ in range(10):
+            response = postgrest.session.get("/authors_only", headers=headers)
+            assert response.status_code == 200
+
+            time.sleep(0.1)
+
+
+def test_jwt_secret_reload(tmp_path, defaultenv):
+    "JWT secret should be reloaded from file when PostgREST is sent SIGUSR2."
+    config = (CONFIGSDIR / "sigusr2-settings.config").read_text()
+    configfile = tmp_path / "test.config"
+    configfile.write_text(config)
+
+    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
+
+    with run(configfile, env=defaultenv) as postgrest:
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == 401
+
+        # change setting
+        configfile.write_text(config.replace("invalid" * 5, SECRET))
+
+        # reload config
+        postgrest.process.send_signal(signal.SIGUSR2)
+
+        sleep_until_postgrest_config_reload()
+
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == 200
+
+
+def test_jwt_secret_external_file_reload(tmp_path, defaultenv):
+    "JWT secret external file should be reloaded when PostgREST is sent a SIGUSR2 or a NOTIFY."
+    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
+
+    external_secret_file = tmp_path / "jwt-secret-config"
+    external_secret_file.write_text("invalid" * 5)
+
+    env = {
+        **defaultenv,
+        "PGRST_JWT_SECRET": f"@{external_secret_file}",
+        "PGRST_DB_CHANNEL_ENABLED": "true",
+        "PGRST_DB_CONFIG": "false",
+        "PGRST_DB_ANON_ROLE": "postgrest_test_anonymous",  # required for NOTIFY
+    }
+
+    with run(env=env) as postgrest:
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == 401
+
+        # change external file
+        external_secret_file.write_text(SECRET)
+
+        # SIGUSR1 doesn't reload external files, at least when db-config=false
+        postgrest.process.send_signal(signal.SIGUSR1)
+        sleep_until_postgrest_scache_reload()
+
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == 401
+
+        # reload config and external file with SIGUSR2
+        postgrest.process.send_signal(signal.SIGUSR2)
+        sleep_until_postgrest_config_reload()
+
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == 200
+
+        # change external file to wrong value again
+        external_secret_file.write_text("invalid" * 5)
+
+        # reload config and external file with NOTIFY
+        response = postgrest.session.post("/rpc/reload_pgrst_config")
+        assert response.text == ""
+        assert response.status_code == 204
+        sleep_until_postgrest_config_reload()
+
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == 401
+
+
+# TODO: This test is more related to observability than authentication.
+#       So, move it an appropriate test module.
+def test_jwt_cache_server_timing(defaultenv):
+    "server-timing duration is exposed for JWT with expiry"
+
+    env = {
+        **defaultenv,
+        "PGRST_SERVER_TIMING_ENABLED": "true",
+        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
+        "PGRST_JWT_SECRET": SECRET,
+        "PGRST_DB_CONFIG": "false",
+    }
+
+    headers = jwtauthheader(
+        {
+            "role": "postgrest_test_author",
+            "exp": int(
+                (datetime.now(timezone.utc) + timedelta(minutes=30)).timestamp()
+            ),
+        },
+        SECRET,
+    )
+
+    with run(env=env) as postgrest:
+        first = postgrest.session.get("/authors_only", headers=headers)
+        second = postgrest.session.get("/authors_only", headers=headers)
+
+        assert first.status_code == 200
+        assert second.status_code == 200
+
+        first_dur = parse_server_timings_header(first.headers["Server-Timing"])["jwt"]
+        second_dur = parse_server_timings_header(second.headers["Server-Timing"])["jwt"]
+
+        # with jwt caching the parse time of second request with the same token
+        # should be at least as fast as the first one
+        assert second_dur <= first_dur
+
+
+def test_jwt_cache_without_server_timing(defaultenv):
+    "JWT cache does not break requests with server-timing disabled"
+
+    env = {
+        **defaultenv,
+        "PGRST_SERVER_TIMING_ENABLED": "false",
+        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
+        "PGRST_JWT_SECRET": SECRET,
+        "PGRST_DB_CONFIG": "false",
+    }
+
+    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
+
+    with run(env=env) as postgrest:
+        first = postgrest.session.get("/authors_only", headers=headers)
+        second = postgrest.session.get("/authors_only", headers=headers)
+
+        assert first.status_code == 200
+        assert second.status_code == 200
+
+
+def test_jwt_cache_without_exp_claim(defaultenv):
+    "server-timing duration is exposed for JWT without expiry"
+
+    env = {
+        **defaultenv,
+        "PGRST_SERVER_TIMING_ENABLED": "true",
+        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
+        "PGRST_JWT_SECRET": SECRET,
+        "PGRST_DB_CONFIG": "false",
+    }
+
+    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)  # no exp
+
+    with run(env=env) as postgrest:
+        first = postgrest.session.get("/authors_only", headers=headers)
+        second = postgrest.session.get("/authors_only", headers=headers)
+
+        assert first.status_code == 200
+        assert second.status_code == 200
+
+        first_dur = parse_server_timings_header(first.headers["Server-Timing"])["jwt"]
+        second_dur = parse_server_timings_header(second.headers["Server-Timing"])["jwt"]
+
+        assert first_dur >= 0
+        assert second_dur >= 0
+
+
+def test_invalidate_jwt_cache_when_secret_changes(tmp_path, defaultenv):
+    "JWT cache should be emptied after jwt-secret is changed in a config reload"
+
+    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
+
+    external_secret_file = tmp_path / "jwt-secret-config"
+    external_secret_file.write_text(SECRET)
+
+    env = {
+        **defaultenv,
+        "PGRST_JWT_SECRET": f"@{external_secret_file}",
+        "PGRST_DB_CHANNEL_ENABLED": "true",
+        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",  # enable cache
+        "PGRST_DB_ANON_ROLE": "postgrest_test_anonymous",  # required for NOTIFY
+    }
+
+    with run(env=env) as postgrest:
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == 200  # jwt gets cached
+
+        # change external file
+        external_secret_file.write_text("invalid" * 5)
+
+        # reload config and external file with NOTIFY
+        # jwt-cache should get empty
+        response = postgrest.session.post("/rpc/reload_pgrst_config")
+        assert response.text == ""
+        assert response.status_code == 204
+        sleep_until_postgrest_config_reload()
+
+        # now the request should fail because the cached token is removed
+        response = postgrest.session.get("/authors_only", headers=headers)
+        assert response.status_code == 401

test/io/test_big_schema.py

@@ -3,62 +3,11 @@
 import re
 
 import pytest
+import requests
 
 from postgrest import run
 
 
-def test_requests_with_resource_embedding_wait_for_schema_cache_reload(defaultenv):
-    "requests that use the schema cache with resource embedding wait long for the schema cache to reload"
-
-    env = {
-        **defaultenv,
-        "PGRST_DB_SCHEMAS": "apflora",
-        "PGRST_DB_POOL": "2",
-        "PGRST_DB_ANON_ROLE": "postgrest_test_anonymous",
-        "PGRST_INTERNAL_SCHEMA_CACHE_RELATIONSHIP_LOAD_SLEEP": "5100",
-    }
-
-    with run(env=env, wait_max_seconds=30) as postgrest:
-        # reload the schema cache
-        response = postgrest.session.get("/rpc/notify_pgrst")
-        assert response.status_code == 204
-
-        postgrest.wait_until_scache_starts_loading()
-
-        response = postgrest.session.get("/tpopmassn?select=*,tpop(*)")
-        assert response.status_code == 200
-
-        assert response.elapsed.total_seconds() > 5
-
-
-def test_requests_without_resource_embedding_wait_for_schema_cache_reload(defaultenv):
-    "requests that use the schema cache without resource embedding wait less for the schema cache to reload"
-
-    env = {
-        **defaultenv,
-        "PGRST_DB_SCHEMAS": "apflora",
-        "PGRST_DB_POOL": "2",
-        "PGRST_DB_ANON_ROLE": "postgrest_test_anonymous",
-        "PGRST_INTERNAL_SCHEMA_CACHE_LOAD_SLEEP": "1100",
-        "PGRST_INTERNAL_SCHEMA_CACHE_RELATIONSHIP_LOAD_SLEEP": "5000",
-    }
-
-    with run(env=env, wait_max_seconds=30) as postgrest:
-        # reload the schema cache
-        response = postgrest.session.get("/rpc/notify_pgrst")
-        assert response.status_code == 204
-
-        postgrest.wait_until_scache_starts_loading()
-
-        response = postgrest.session.get("/tpopmassn")
-        assert response.status_code == 200
-
-        assert (
-            response.elapsed.total_seconds() > 1
-            and response.elapsed.total_seconds() < 5
-        )
-
-
 def test_schema_cache_load_max_duration(defaultenv):
     "schema cache load should not surpass a max_duration of elapsed milliseconds"
 
@@ -106,6 +55,25 @@ def test_openapi_in_big_schema(defaultenv):
         assert response.status_code == 200
 
 
+def test_stackoverflow_is_logged(defaultenv):
+    "Stack overflow errors should be logged with the Warp error message"
+
+    env = {
+        **defaultenv,
+        "PGRST_DB_SCHEMAS": "apflora",
+        "PGRST_DB_ANON_ROLE": "postgrest_test_anonymous",
+    }
+
+    with run(env=env, wait_max_seconds=30, no_startup_stdout=False) as postgrest:
+        with pytest.raises(requests.exceptions.ConnectionError):
+            postgrest.session.get("/")
+
+        output = postgrest.read_stdout(nlines=10)
+        output.extend(postgrest.read_stdout(nlines=10))
+
+        assert any("Warp server error: stack overflow" in line for line in output)
+
+
 # See: https://github.com/PostgREST/postgrest/issues/3329
 def test_should_not_fail_with_stack_overflow(defaultenv):
     "requesting a non-existent relationship should not fail with stack overflow due to fuzzy search of candidates"
@@ -122,3 +90,23 @@ def test_should_not_fail_with_stack_overflow(defaultenv):
         assert response.status_code == 404
         data = response.json()
         assert data["code"] == "PGRST205"
+
+
+def test_second_request_for_non_existent_table_should_be_quick(defaultenv):
+    "requesting a non-existent relationship should be quick after the fuzzy search index is loaded (2nd request)"
+
+    env = {
+        **defaultenv,
+        "PGRST_DB_SCHEMAS": "fuzzysearch",
+        "PGRST_DB_POOL": "2",
+        "PGRST_DB_ANON_ROLE": "postgrest_test_anonymous",
+    }
+
+    with run(env=env, wait_max_seconds=30) as postgrest:
+        response = postgrest.session.get("/unknown-table")
+        assert response.status_code == 404
+        data = response.json()
+        assert data["code"] == "PGRST205"
+        first_duration = response.elapsed.total_seconds()
+        response = postgrest.session.get("/unknown-table")
+        assert response.elapsed.total_seconds() < first_duration / 2

test/io/test_cli.py

@@ -286,6 +286,7 @@ def test_jwt_secret_min_length(defaultenv):
     assert "The JWT secret must be at least 32 characters long." in error
 
 
+# TODO: Improve readability of "--ready" healthcheck tests
 @pytest.mark.parametrize("host", ["127.0.0.1", "::1"], ids=["IPv4", "IPv6"])
 def test_cli_ready_flag_success(host, defaultenv):
     "test PostgREST ready flag succeeds when ready"
@@ -340,8 +341,11 @@ def test_cli_ready_flag_fail_with_http_exception(defaultenv):
 
     # when healthcheck process sends the request to a wrong endpoint
     with run(env=defaultenv, port=port) as postgrest:
-        # we set it to some freeport where admin is not running
+        # we set it to some freeport where server and admin server is not running
-        postgrest.config["PGRST_ADMIN_SERVER_PORT"] = str(freeport())
+        admin_port = int(postgrest.config["PGRST_ADMIN_SERVER_PORT"])
+        used_ports = [port, admin_port]
+
+        postgrest.config["PGRST_ADMIN_SERVER_PORT"] = str(freeport(used_ports))
         output = cli(["--ready"], env=postgrest.config, expect_error=True)
         (admin_host, admin_port) = get_admin_host_and_port_from_config(postgrest.config)
 

test/io/test_io.py

@@ -1,15 +1,13 @@
 "Unit tests for Input/Ouput of PostgREST seen as a black box."
 
-from datetime import datetime, timedelta, timezone
-from operator import attrgetter
 import os
 import re
 import signal
 import time
 import pytest
 
-from config import BASEDIR, CONFIGSDIR, FIXTURES, SECRET
+from config import CONFIGSDIR, FIXTURES, SECRET
-from util import Thread, authheader, jwtauthheader, parse_server_timings_header
+from util import Thread, jwtauthheader, parse_server_timings_header
 from postgrest import (
     freeport,
     is_ipv6,
@@ -23,162 +21,6 @@ from postgrest import (
 )
 
 
-@pytest.mark.parametrize(
-    "secretpath",
-    [path for path in (BASEDIR / "secrets").iterdir() if path.suffix != ".jwt"],
-    ids=attrgetter("name"),
-)
-def test_read_secret_from_file(secretpath, defaultenv):
-    "Authorization should succeed when the secret is read from a file."
-
-    env = {**defaultenv, "PGRST_JWT_SECRET": f"@{secretpath}"}
-
-    if secretpath.suffix == ".b64":
-        env["PGRST_JWT_SECRET_IS_BASE64"] = "true"
-
-    secret = secretpath.read_bytes()
-    headers = authheader(secretpath.with_suffix(".jwt").read_text())
-
-    with run(stdin=secret, env=env) as postgrest:
-        response = postgrest.session.get("/authors_only", headers=headers)
-        print(response.text)
-        assert response.status_code == 200
-
-
-def test_read_secret_from_stdin(defaultenv):
-    "Authorization should succeed when the secret is read from stdin."
-
-    env = {**defaultenv, "PGRST_DB_CONFIG": "false", "PGRST_JWT_SECRET": "@/dev/stdin"}
-
-    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
-
-    with run(stdin=SECRET.encode(), env=env) as postgrest:
-        response = postgrest.session.get("/authors_only", headers=headers)
-        print(response.text)
-        assert response.status_code == 200
-
-
-# TODO: This test would fail right now, because of
-# https://github.com/PostgREST/postgrest/issues/2126
-@pytest.mark.skip
-def test_read_secret_from_stdin_dbconfig(defaultenv):
-    "Authorization should succeed when the secret is read from stdin with db-config=true."
-
-    env = {**defaultenv, "PGRST_DB_CONFIG": "true", "PGRST_JWT_SECRET": "@/dev/stdin"}
-
-    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
-
-    with run(stdin=SECRET.encode(), env=env) as postgrest:
-        response = postgrest.session.get("/authors_only", headers=headers)
-        print(response.text)
-        assert response.status_code == 200
-
-
-def test_jwt_errors(defaultenv):
-    "invalid JWT should throw error"
-
-    env = {**defaultenv, "PGRST_JWT_SECRET": SECRET, "PGRST_JWT_AUD": "io tests"}
-
-    def relativeSeconds(sec):
-        return int((datetime.now(timezone.utc) + timedelta(seconds=sec)).timestamp())
-
-    with run(env=env) as postgrest:
-        headers = jwtauthheader({}, "other secret")
-        response = postgrest.session.get("/", headers=headers)
-        assert response.status_code == 401
-        assert response.json()["message"] == "No suitable key or wrong key type"
-        assert (
-            response.json()["details"] == "None of the keys was able to decode the JWT"
-        )
-
-        headers = jwtauthheader({"role": "not_existing"}, SECRET)
-        response = postgrest.session.get("/", headers=headers)
-        assert response.status_code == 401
-        assert response.json()["message"] == 'role "not_existing" does not exist'
-
-        # -35 seconds, because we allow clock skew of 30 seconds
-        headers = jwtauthheader({"exp": relativeSeconds(-35)}, SECRET)
-        response = postgrest.session.get("/", headers=headers)
-        assert response.status_code == 401
-        assert response.json()["message"] == "JWT expired"
-
-        # 35 seconds, because we allow clock skew of 30 seconds
-        headers = jwtauthheader({"nbf": relativeSeconds(35)}, SECRET)
-        response = postgrest.session.get("/", headers=headers)
-        assert response.status_code == 401
-        assert response.json()["message"] == "JWT not yet valid"
-
-        # 35 seconds, because we allow clock skew of 35 seconds
-        headers = jwtauthheader({"iat": relativeSeconds(35)}, SECRET)
-        response = postgrest.session.get("/", headers=headers)
-        assert response.status_code == 401
-        assert response.json()["message"] == "JWT issued at future"
-
-        headers = jwtauthheader({"aud": "not set"}, SECRET)
-        response = postgrest.session.get("/", headers=headers)
-        assert response.status_code == 401
-        assert response.json()["message"] == "JWT not in audience"
-
-        # partial token, no signature
-        headers = authheader("eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.bm90IGFuIG9iamVjdA")
-        response = postgrest.session.get("/", headers=headers)
-        assert response.status_code == 401
-        assert response.json()["message"] == "Expected 3 parts in JWT; got 2"
-
-        # complete token but random characters
-        headers = authheader("quifquirndsjagnrgniur.fonvoienqhhdj.iuqvnvhojah")
-        response = postgrest.session.get("/", headers=headers)
-        assert response.status_code == 401
-        assert response.json()["message"] == "JWT cryptographic operation failed"
-
-        # token with algorithm "none"
-        headers = authheader(
-            "eyJ0eXAiOiJKV1QiLCJhbGciOiJub25lIn0.e30.yOBhlOIqn56T-4NvyEXCjfi3UmyQZ-BzXtePMO2NgRI"
-        )
-        response = postgrest.session.get("/", headers=headers)
-        assert response.status_code == 401
-        assert response.json()["message"] == "Wrong or unsupported encoding algorithm"
-        assert (
-            response.json()["details"]
-            == "JWT is unsecured but expected 'alg' was not 'none'"
-        )
-
-    env = {
-        **defaultenv,
-        "PGRST_SERVER_TIMING_ENABLED": "true",
-        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
-        "PGRST_JWT_SECRET": SECRET,
-    }
-
-    # for code coverage with cache enabled and server-timing enabled
-    with run(env=env) as postgrest:
-        response = postgrest.session.get("/authors_only")
-        assert response.status_code == 401
-        assert response.json()["message"] == "permission denied for table authors_only"
-
-    env = {
-        **defaultenv,
-        "PGRST_SERVER_TIMING_ENABLED": "false",
-        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
-        "PGRST_JWT_SECRET": SECRET,
-    }
-
-    # for code coverage with cache enabled and server-timing disabled
-    with run(env=env) as postgrest:
-        response = postgrest.session.get("/authors_only")
-        assert response.status_code == 401
-        assert response.json()["message"] == "permission denied for table authors_only"
-
-
-def test_fail_with_invalid_password(defaultenv):
-    "Connecting with an invalid password should fail without retries."
-    uri = f'postgresql://?dbname={defaultenv["PGDATABASE"]}&host={defaultenv["PGHOST"]}&user=some_protected_user&password=invalid_pass'
-    env = {**defaultenv, "PGRST_DB_URI": uri}
-    with run(env=env, wait_for_readiness=False) as postgrest:
-        exitCode = wait_until_exit(postgrest)
-        assert exitCode == 1
-
-
 def test_connect_with_dburi(dburi, defaultenv):
     "Connecting with db-uri instead of LIPQ* environment variables should work."
     defaultenv_without_libpq = {
@@ -217,67 +59,6 @@ def test_read_dburi_from_stdin_with_eol(dburi, defaultenv):
         pass
 
 
-@pytest.mark.parametrize(
-    "roleclaim", FIXTURES["roleclaims"], ids=lambda claim: claim["key"]
-)
-def test_role_claim_key(roleclaim, defaultenv):
-    "Authorization should depend on a correct role-claim-key and JWT claim."
-    env = {
-        **defaultenv,
-        "PGRST_JWT_ROLE_CLAIM_KEY": roleclaim["key"],
-        "PGRST_JWT_SECRET": SECRET,
-    }
-    headers = jwtauthheader(roleclaim["data"], SECRET)
-
-    with run(env=env) as postgrest:
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == roleclaim["expected_status"]
-
-
-@pytest.mark.parametrize(
-    "jwtaudroleclaim",
-    FIXTURES["jwtaudroleclaims"],
-    ids=lambda claim: claim["key"] + "_" + str(claim["expected_status"]),
-)
-def test_jwt_aud_in_role_claim_key(jwtaudroleclaim, defaultenv):
-    "Allows authorization with JWT aud claim in role-claim-key"
-
-    env = {
-        **defaultenv,
-        "PGRST_JWT_AUD": "postgrest_test_author",
-        "PGRST_JWT_ROLE_CLAIM_KEY": jwtaudroleclaim["key"],
-        "PGRST_JWT_SECRET": SECRET,
-    }
-
-    headers = jwtauthheader(jwtaudroleclaim["data"], SECRET)
-
-    with run(env=env) as postgrest:
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == jwtaudroleclaim["expected_status"]
-
-
-def test_iat_claim(defaultenv):
-    """
-    A claim with an 'iat' (issued at) attribute should be successful.
-
-    The PostgREST time cache leads to issues here, see:
-    https://github.com/PostgREST/postgrest/issues/1139
-
-    """
-
-    env = {**defaultenv, "PGRST_JWT_SECRET": SECRET}
-
-    claim = {"role": "postgrest_test_author", "iat": datetime.now(timezone.utc)}
-    headers = jwtauthheader(claim, SECRET)
-
-    with run(env=env) as postgrest:
-        for _ in range(10):
-            response = postgrest.session.get("/authors_only", headers=headers)
-            assert response.status_code == 200
-
-            time.sleep(0.1)
-
-
 def test_app_settings_flush_pool(defaultenv):
     """
     App settings should not reset when the db pool is flushed.
@@ -324,6 +105,24 @@ def test_flush_pool_no_interrupt(defaultenv):
         t.join()
 
 
+def test_termination_unix_signal_logging(defaultenv):
+    "Server logs when handling termination unix signals."
+
+    with run(env=defaultenv) as postgrest:
+        postgrest.process.send_signal(signal.SIGTERM)
+        lines = postgrest.read_stdout(nlines=1)
+        wait_until_exit(postgrest)
+
+    assert any("SIGTERM" in line for line in lines)
+
+    with run(env=defaultenv) as postgrest:
+        postgrest.process.send_signal(signal.SIGINT)
+        lines = postgrest.read_stdout(nlines=1)
+        wait_until_exit(postgrest)
+
+    assert any("SIGINT" in line for line in lines)
+
+
 def test_random_port_bound(defaultenv):
     "PostgREST should bind to a random port when PGRST_SERVER_PORT is 0."
 
@@ -353,79 +152,6 @@ def test_app_settings_reload(tmp_path, defaultenv):
         assert response.text == '"Jane"'
 
 
-def test_jwt_secret_reload(tmp_path, defaultenv):
-    "JWT secret should be reloaded from file when PostgREST is sent SIGUSR2."
-    config = (CONFIGSDIR / "sigusr2-settings.config").read_text()
-    configfile = tmp_path / "test.config"
-    configfile.write_text(config)
-
-    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
-
-    with run(configfile, env=defaultenv) as postgrest:
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == 401
-
-        # change setting
-        configfile.write_text(config.replace("invalid" * 5, SECRET))
-
-        # reload config
-        postgrest.process.send_signal(signal.SIGUSR2)
-
-        sleep_until_postgrest_config_reload()
-
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == 200
-
-
-def test_jwt_secret_external_file_reload(tmp_path, defaultenv):
-    "JWT secret external file should be reloaded when PostgREST is sent a SIGUSR2 or a NOTIFY."
-    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
-
-    external_secret_file = tmp_path / "jwt-secret-config"
-    external_secret_file.write_text("invalid" * 5)
-
-    env = {
-        **defaultenv,
-        "PGRST_JWT_SECRET": f"@{external_secret_file}",
-        "PGRST_DB_CHANNEL_ENABLED": "true",
-        "PGRST_DB_CONFIG": "false",
-        "PGRST_DB_ANON_ROLE": "postgrest_test_anonymous",  # required for NOTIFY
-    }
-
-    with run(env=env) as postgrest:
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == 401
-
-        # change external file
-        external_secret_file.write_text(SECRET)
-
-        # SIGUSR1 doesn't reload external files, at least when db-config=false
-        postgrest.process.send_signal(signal.SIGUSR1)
-        sleep_until_postgrest_scache_reload()
-
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == 401
-
-        # reload config and external file with SIGUSR2
-        postgrest.process.send_signal(signal.SIGUSR2)
-        sleep_until_postgrest_config_reload()
-
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == 200
-
-        # change external file to wrong value again
-        external_secret_file.write_text("invalid" * 5)
-
-        # reload config and external file with NOTIFY
-        response = postgrest.session.post("/rpc/reload_pgrst_config")
-        assert response.text == ""
-        assert response.status_code == 204
-        sleep_until_postgrest_config_reload()
-
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == 401
-
-
 def test_db_schema_reload(tmp_path, defaultenv):
     "DB schema should be reloaded from file when PostgREST is sent SIGUSR2."
     config = (CONFIGSDIR / "sigusr2-settings.config").read_text()
@@ -1350,6 +1076,56 @@ def test_schema_cache_concurrent_notifications(slow_schema_cache_env):
         assert response.status_code == 200
 
 
+def test_schema_cache_query_sleep_logs(defaultenv):
+    """Schema cache sleep should be reflected in the logged query duration."""
+
+    env = {
+        **defaultenv,
+        "PGRST_INTERNAL_SCHEMA_CACHE_QUERY_SLEEP": "1000",
+    }
+    log_pattern = re.compile(r"Schema cache queried in ([\d.]+) milliseconds")
+
+    with run(env=env, wait_max_seconds=3, no_startup_stdout=False) as postgrest:
+        observed_ms = None
+        collected = []
+
+        lines = postgrest.read_stdout(nlines=10)
+        collected.extend(lines)
+        for line in lines:
+            match = log_pattern.search(line)
+            if match:
+                observed_ms = float(match.group(1))
+                break
+
+        assert observed_ms is not None
+        assert 1000 < observed_ms < 2000
+
+
+def test_schema_cache_load_sleep_logs(defaultenv):
+    """Schema cache load sleep should be reflected in the logged load duration."""
+
+    env = {
+        **defaultenv,
+        "PGRST_INTERNAL_SCHEMA_CACHE_LOAD_SLEEP": "1000",
+    }
+    log_pattern = re.compile(r"Schema cache loaded in ([\d.]+) milliseconds")
+
+    with run(env=env, wait_max_seconds=3, no_startup_stdout=False) as postgrest:
+        observed_ms = None
+        collected = []
+
+        lines = postgrest.read_stdout(nlines=10)
+        collected.extend(lines)
+        for line in lines:
+            match = log_pattern.search(line)
+            if match:
+                observed_ms = float(match.group(1))
+                break
+
+        assert observed_ms is not None
+        assert 1000 < observed_ms < 2000
+
+
 @pytest.mark.parametrize("dburi_type", ["no_params", "no_params_qmark", "with_params"])
 def test_get_pgrst_version_with_uri_connection_string(dburi_type, dburi, defaultenv):
     "The fallback_application_name should be added to the db-uri if it has a URI format"
@@ -1489,90 +1265,6 @@ def test_fail_with_automatic_recovery_disabled_and_terminated_using_query(defaul
         assert exitCode == 1
 
 
-def test_jwt_cache_server_timing(defaultenv):
-    "server-timing duration is exposed for JWT with expiry"
-
-    env = {
-        **defaultenv,
-        "PGRST_SERVER_TIMING_ENABLED": "true",
-        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
-        "PGRST_JWT_SECRET": SECRET,
-        "PGRST_DB_CONFIG": "false",
-    }
-
-    headers = jwtauthheader(
-        {
-            "role": "postgrest_test_author",
-            "exp": int(
-                (datetime.now(timezone.utc) + timedelta(minutes=30)).timestamp()
-            ),
-        },
-        SECRET,
-    )
-
-    with run(env=env) as postgrest:
-        first = postgrest.session.get("/authors_only", headers=headers)
-        second = postgrest.session.get("/authors_only", headers=headers)
-
-        assert first.status_code == 200
-        assert second.status_code == 200
-
-        first_dur = parse_server_timings_header(first.headers["Server-Timing"])["jwt"]
-        second_dur = parse_server_timings_header(second.headers["Server-Timing"])["jwt"]
-
-        # with jwt caching the parse time of second request with the same token
-        # should be at least as fast as the first one
-        assert second_dur <= first_dur
-
-
-def test_jwt_cache_without_server_timing(defaultenv):
-    "JWT cache does not break requests with server-timing disabled"
-
-    env = {
-        **defaultenv,
-        "PGRST_SERVER_TIMING_ENABLED": "false",
-        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
-        "PGRST_JWT_SECRET": SECRET,
-        "PGRST_DB_CONFIG": "false",
-    }
-
-    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
-
-    with run(env=env) as postgrest:
-        first = postgrest.session.get("/authors_only", headers=headers)
-        second = postgrest.session.get("/authors_only", headers=headers)
-
-        assert first.status_code == 200
-        assert second.status_code == 200
-
-
-def test_jwt_cache_without_exp_claim(defaultenv):
-    "server-timing duration is exposed for JWT without expiry"
-
-    env = {
-        **defaultenv,
-        "PGRST_SERVER_TIMING_ENABLED": "true",
-        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
-        "PGRST_JWT_SECRET": SECRET,
-        "PGRST_DB_CONFIG": "false",
-    }
-
-    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)  # no exp
-
-    with run(env=env) as postgrest:
-        first = postgrest.session.get("/authors_only", headers=headers)
-        second = postgrest.session.get("/authors_only", headers=headers)
-
-        assert first.status_code == 200
-        assert second.status_code == 200
-
-        first_dur = parse_server_timings_header(first.headers["Server-Timing"])["jwt"]
-        second_dur = parse_server_timings_header(second.headers["Server-Timing"])["jwt"]
-
-        assert first_dur >= 0
-        assert second_dur >= 0
-
-
 def test_preflight_request_with_cors_allowed_origin_config(defaultenv):
     "OPTIONS preflight request should return Access-Control-Allow-Origin equal to origin"
 
@@ -1805,53 +1497,6 @@ def test_schema_cache_startup_load_with_in_db_config(defaultenv, metapostgrest):
     assert response.status_code == 204
 
 
-def test_jwt_cache_purges_expired_entries(defaultenv):
-    "test expired cache entries are purged on cache miss"
-
-    # The verification of actual cache size reduction is done manually, see https://github.com/PostgREST/postgrest/pull/3801#issuecomment-2620776041
-    # This test is written for code coverage of purgeExpired function
-
-    def relativeSeconds(sec):
-        return int((datetime.now(timezone.utc) + timedelta(seconds=sec)).timestamp())
-
-    def headers(sec):
-        return jwtauthheader(
-            {"role": "postgrest_test_author", "exp": relativeSeconds(sec)}, SECRET
-        )
-
-    env = {
-        **defaultenv,
-        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",
-        "PGRST_JWT_SECRET": SECRET,
-        "PGRST_DB_CONFIG": "false",
-    }
-
-    with run(env=env) as postgrest:
-
-        # Generate two unique JWT tokens
-        # The 1 second sleep is needed for it generate a unique token
-        hdrs1 = headers(5)
-        postgrest.session.get("/authors_only", headers=hdrs1)
-
-        time.sleep(1)
-
-        hdrs2 = headers(5)
-        postgrest.session.get("/authors_only", headers=hdrs2)
-
-        # Wait 5 seconds for the tokens to expire
-        time.sleep(5)
-
-        hdrs3 = headers(5)
-
-        # Make another request which should cause a cache miss and so
-        # the purgeExpired function will be triggered.
-        #
-        # This should remove the 2 expired tokens but adds another to cache
-        response = postgrest.session.get("/authors_only", headers=hdrs3)
-
-        assert response.status_code == 200
-
-
 def test_pgrst_log_503_client_error_to_stderr(defaultenv):
     "PostgREST should log 503 errors to stderr"
 
@@ -1962,41 +1607,6 @@ def test_proxy_status_header(defaultenv, metapostgrest):
         assert data["message"] == "canceling statement due to statement timeout"
 
 
-def test_invalidate_jwt_cache_when_secret_changes(tmp_path, defaultenv):
-    "JWT cache should be emptied after jwt-secret is changed in a config reload"
-
-    headers = jwtauthheader({"role": "postgrest_test_author"}, SECRET)
-
-    external_secret_file = tmp_path / "jwt-secret-config"
-    external_secret_file.write_text(SECRET)
-
-    env = {
-        **defaultenv,
-        "PGRST_JWT_SECRET": f"@{external_secret_file}",
-        "PGRST_DB_CHANNEL_ENABLED": "true",
-        "PGRST_JWT_CACHE_MAX_ENTRIES": "86400",  # enable cache
-        "PGRST_DB_ANON_ROLE": "postgrest_test_anonymous",  # required for NOTIFY
-    }
-
-    with run(env=env) as postgrest:
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == 200  # jwt gets cached
-
-        # change external file
-        external_secret_file.write_text("invalid" * 5)
-
-        # reload config and external file with NOTIFY
-        # jwt-cache should get empty
-        response = postgrest.session.post("/rpc/reload_pgrst_config")
-        assert response.text == ""
-        assert response.status_code == 204
-        sleep_until_postgrest_config_reload()
-
-        # now the request should fail because the cached token is removed
-        response = postgrest.session.get("/authors_only", headers=headers)
-        assert response.status_code == 401
-
-
 def test_allow_configs_to_be_set_to_empty(defaultenv):
     'configs that are explicitly set to empty (= "<empty>") should not throw parse error'
 
@@ -2046,3 +1656,103 @@ def test_log_listener_connection_errors(defaultenv):
             in line
             for line in output
         )
+
+
+def test_db_pre_config_with_pg_reserved_words(defaultenv):
+    "The db-pre-config should not fail unexpectedly when function name is a postgres reserved word"
+
+    env = {
+        **defaultenv,
+        "PGRST_DB_PRE_CONFIG": "true",  # call true function
+    }
+
+    with run(env=env) as postgrest:
+        response = postgrest.session.post("/rpc/true")
+        assert response.status_code == 200
+
+    env = {
+        **defaultenv,
+        "PGRST_DB_PRE_CONFIG": "select",  # no "select" function in our fixtures, fail gracefully at startup
+    }
+
+    with run(env=env, no_startup_stdout=False, wait_for_readiness=False) as postgrest:
+        output = postgrest.read_stdout(nlines=8)
+        assert any(
+            'Failed to query database settings for the config parameters.{"code":"42883","details":null,"hint":"No function matches the given name and argument types. You might need to add explicit type casts.","message":"function select() does not exist"}'
+            in line
+            for line in output
+        )
+
+
+def test_requests_with_resource_embedding_wait_for_schema_cache_reload(defaultenv):
+    "requests that use the schema cache with resource embedding wait long for the schema cache to reload"
+
+    env = {
+        **defaultenv,
+        "PGRST_DB_POOL": "2",
+        "PGRST_INTERNAL_SCHEMA_CACHE_RELATIONSHIP_LOAD_SLEEP": "5100",
+    }
+
+    with run(env=env, wait_max_seconds=30) as postgrest:
+        # reload the schema cache
+        response = postgrest.session.get("/rpc/notify_pgrst")
+        assert response.status_code == 204
+
+        postgrest.wait_until_scache_starts_loading()
+
+        response = postgrest.session.get("/directors?select=id,name,films(title)")
+        assert response.status_code == 200
+
+        assert response.elapsed.total_seconds() > 5
+
+
+def test_requests_without_resource_embedding_wait_for_schema_cache_reload(defaultenv):
+    "requests that use the schema cache without resource embedding wait less for the schema cache to reload"
+
+    env = {
+        **defaultenv,
+        "PGRST_DB_POOL": "2",
+        "PGRST_INTERNAL_SCHEMA_CACHE_LOAD_SLEEP": "1100",
+        "PGRST_INTERNAL_SCHEMA_CACHE_RELATIONSHIP_LOAD_SLEEP": "5000",
+    }
+
+    with run(env=env, wait_max_seconds=30) as postgrest:
+        # reload the schema cache
+        response = postgrest.session.get("/rpc/notify_pgrst")
+        assert response.status_code == 204
+
+        postgrest.wait_until_scache_starts_loading()
+
+        response = postgrest.session.get("/films")
+        assert response.status_code == 200
+
+        assert (
+            response.elapsed.total_seconds() > 1
+            and response.elapsed.total_seconds() < 5
+        )
+
+
+def test_server_timing_transaction_duration(defaultenv, metapostgrest):
+    "server-timing transaction duration should be accurate"
+
+    # just to ensure we don't timeout
+    role = "timeout_authenticator"
+    set_statement_timeout(metapostgrest, role, 3000)  # 3 seconds
+
+    env = {
+        **defaultenv,
+        "PGUSER": role,
+        "PGRST_DB_ANON_ROLE": role,
+        "PGRST_SERVER_TIMING_ENABLED": "true",
+    }
+
+    with run(env=env) as postgrest:
+        response = postgrest.session.get("/rpc/sleep?seconds=2")
+
+        assert response.status_code == 204
+
+        response_dur = parse_server_timings_header(response.headers["Server-Timing"])[
+            "transaction"
+        ]
+
+        assert 2000 <= response_dur < 3000

test/observability/Main.hs

@@ -0,0 +1,70 @@
+module Main where
+
+import qualified Hasql.Pool                 as P
+import qualified Hasql.Pool.Config          as P
+import qualified Hasql.Transaction.Sessions as HT
+
+import Data.Function (id)
+
+import           PostgREST.App             (postgrest)
+import qualified PostgREST.AppState        as AppState
+import           PostgREST.Config          (AppConfig (..))
+import           PostgREST.Config.Database (queryPgVersion)
+import qualified PostgREST.Logger          as Logger
+import qualified PostgREST.Metrics         as Metrics
+import           PostgREST.SchemaCache     (querySchemaCache)
+
+import qualified Observation.JwtCache
+import qualified Observation.MetricsSpec
+
+import ObsHelper
+import PostgREST.Observation (Observation (HasqlPoolObs))
+import Protolude             hiding (toList, toS)
+import Test.Hspec
+
+main :: IO ()
+main = do
+  poolChan <- newChan
+  -- make sure poolChan is not growing indefinitely
+  -- start a thread that drains the channel
+  -- this is necessary because test cases operate on
+  -- copies so poolChan is never read from
+  -- this means we have another thread running for the entire duration of the spec but this shouldn't be a problem since Haskell green threads are lightweight
+  void $ forkIO $ forever $ readChan poolChan
+  metricsState <- Metrics.init (configDbPoolSize testCfg)
+  pool <- P.acquire $ P.settings
+    [ P.size 3
+    , P.acquisitionTimeout 10
+    , P.agingTimeout 60
+    , P.idlenessTimeout 60
+    , P.staticConnectionSettings (toUtf8 $ configDbUri testCfg)
+    -- make sure metrics are updated and pool observations published to poolChan
+    , P.observationHandler $ (writeChan poolChan <> Metrics.observationMetrics metricsState) . HasqlPoolObs
+    ]
+
+  actualPgVersion <- either (panic . show) id <$> P.use pool (queryPgVersion False)
+
+  -- cached schema cache so most tests run fast
+  baseSchemaCache <- loadSCache pool testCfg
+  loggerState <- Logger.init
+
+  let
+    initApp sCache config = do
+      -- duplicate poolChan as a starting point
+      obsChan <- dupChan poolChan
+      stateObsChan <- newObsChan obsChan
+      appState <- AppState.initWithPool pool config loggerState metricsState (Metrics.observationMetrics metricsState <> writeChan obsChan)
+      AppState.putPgVersion appState actualPgVersion
+      AppState.putSchemaCache appState (Just sCache)
+      return (SpecState appState metricsState stateObsChan, postgrest (configLogLevel config) appState (pure ()))
+
+  -- Run all test modules
+  hspec $ do
+    before (initApp baseSchemaCache testCfgJwtCache) $
+      describe "Observation.JwtCacheObs" Observation.JwtCache.spec
+    before (initApp baseSchemaCache testCfg) $
+      describe "Feature.MetricsSpec" Observation.MetricsSpec.spec
+
+  where
+    loadSCache pool conf =
+      either (panic.show) id <$> P.use pool (HT.transaction HT.ReadCommitted HT.Read $ querySchemaCache conf)

test/observability/ObsHelper.hs

@@ -0,0 +1,210 @@
+{-# LANGUAGE AllowAmbiguousTypes       #-}
+{-# LANGUAGE DeriveAnyClass            #-}
+{-# LANGUAGE ExistentialQuantification #-}
+{-# LANGUAGE FlexibleContexts          #-}
+{-# LANGUAGE FlexibleInstances         #-}
+{-# LANGUAGE LambdaCase                #-}
+{-# LANGUAGE RankNTypes                #-}
+{-# LANGUAGE ScopedTypeVariables       #-}
+{-# LANGUAGE TupleSections             #-}
+{-# LANGUAGE TypeApplications          #-}
+{-# LANGUAGE TypeOperators             #-}
+module ObsHelper where
+
+import qualified Data.ByteString                 as BS
+import qualified Data.ByteString.Base64          as B64
+import qualified Data.ByteString.Lazy            as BL
+import qualified Data.List                       as DL
+import           Data.List.NonEmpty              (fromList)
+import           Data.String                     (String)
+import qualified Data.Text                       as T
+import qualified Jose.Jwa                        as JWT
+import qualified Jose.Jws                        as JWT
+import qualified Jose.Jwt                        as JWT
+import           Network.HTTP.Types
+import qualified PostgREST.AppState              as AppState
+import           PostgREST.Config                (AppConfig (..),
+                                                  JSPathExp (..),
+                                                  LogLevel (..),
+                                                  OpenAPIMode (..),
+                                                  parseSecret)
+import qualified PostgREST.Metrics               as Metrics
+import           PostgREST.Observation           (Observation (..))
+import           Prometheus                      (Counter, getCounter)
+import           Protolude                       hiding (get, toS)
+import           System.Timeout                  (timeout)
+import           Test.Hspec
+import           Test.Hspec.Expectations.Contrib (annotate)
+-- helpers used to produce observation diagnostics in waitForObs
+-- Implementing the Show instance for Observation is hard due to having many different parameters so instead we use generic programming (`conName`) to obtain the constructor name as `Text`
+class HasConstructor f where
+  genericConstrName :: f x -> Text
+
+instance HasConstructor f => HasConstructor (D1 c f) where
+  genericConstrName (M1 x) = genericConstrName x
+
+instance (HasConstructor x, HasConstructor y) => HasConstructor (x :+: y) where
+  genericConstrName (L1 l) = genericConstrName l
+  genericConstrName (R1 r) = genericConstrName r
+
+instance Constructor c => HasConstructor (C1 c f) where
+  genericConstrName = T.pack . conName
+
+data SpecState = SpecState {
+  specAppState :: AppState.AppState,
+  specMetrics  :: Metrics.MetricsState,
+  specObsChan  :: ObsChan
+}
+
+data StateCheck st m = forall a. StateCheck (st -> (String, m a)) (a -> a -> Expectation)
+
+data TimeoutException = TimeoutException deriving (Show, Exception)
+
+data ObsChan = ObsChan (Chan Observation) (Chan Observation)
+
+constrName :: (HasConstructor (Rep a), Generic a)=> a -> Text
+constrName = genericConstrName . from
+
+baseCfg :: AppConfig
+baseCfg = let secret = encodeUtf8 "reallyreallyreallyreallyverysafe" in
+  AppConfig {
+    configAppSettings               = []
+  , configDbAggregates              = False
+  , configDbAnonRole                = Just "postgrest_test_anonymous"
+  , configDbChannel                 = mempty
+  , configDbChannelEnabled          = True
+  , configDbExtraSearchPath         = []
+  , configDbHoistedTxSettings       = ["default_transaction_isolation","plan_filter.statement_cost_limit","statement_timeout"]
+  , configDbMaxRows                 = Nothing
+  , configDbPlanEnabled             = False
+  , configDbPoolSize                = 10
+  , configDbPoolAcquisitionTimeout  = 10
+  , configDbPoolMaxLifetime         = 1800
+  , configDbPoolMaxIdletime         = 600
+  , configDbPoolAutomaticRecovery   = True
+  , configDbPreRequest              = Nothing
+  , configDbPreparedStatements      = True
+  , configDbRootSpec                = Nothing
+  , configDbSchemas                 = fromList ["test"]
+  , configDbConfig                  = False
+  , configDbPreConfig               = Nothing
+  , configDbUri                     = "postgresql://"
+  , configFilePath                  = Nothing
+  , configJWKS                      = rightToMaybe $ parseSecret secret
+  , configJwtAudience               = Nothing
+  , configJwtRoleClaimKey           = [JSPKey "role"]
+  , configJwtSecret                 = Just secret
+  , configJwtSecretIsBase64         = False
+  , configJwtCacheMaxEntries        = 10
+  , configLogLevel                  = LogCrit
+  , configLogQuery                  = False
+  , configOpenApiMode               = OAFollowPriv
+  , configOpenApiSecurityActive     = False
+  , configOpenApiServerProxyUri     = Nothing
+  , configServerCorsAllowedOrigins  = Nothing
+  , configServerHost                = "localhost"
+  , configServerPort                = 3000
+  , configServerTraceHeader         = Nothing
+  , configServerUnixSocket          = Nothing
+  , configServerUnixSocketMode      = 432
+  , configDbTxAllowOverride         = True
+  , configDbTxRollbackAll           = True
+  , configAdminServerHost           = "localhost"
+  , configAdminServerPort           = Nothing
+  , configRoleSettings              = mempty
+  , configRoleIsoLvl                = mempty
+  , configInternalSCQuerySleep      = Nothing
+  , configInternalSCLoadSleep       = Nothing
+  , configInternalSCRelLoadSleep    = Nothing
+  , configServerTimingEnabled       = True
+  }
+
+testCfg :: AppConfig
+testCfg = baseCfg
+
+testCfgJwtCache :: AppConfig
+testCfgJwtCache =
+  baseCfg {
+    configJwtSecret = Just generateSecret
+  , configJWKS = rightToMaybe $ parseSecret generateSecret
+  , configJwtCacheMaxEntries = 2
+  }
+
+authHeader :: BS.ByteString -> BS.ByteString -> Header
+authHeader typ creds =
+  (hAuthorization, typ <> " " <> creds)
+
+authHeaderJWT :: BS.ByteString -> Header
+authHeaderJWT = authHeader "Bearer"
+
+generateSecret :: ByteString
+generateSecret = B64.decodeLenient "cmVhbGx5cmVhbGx5cmVhbGx5cmVhbGx5dmVyeXNhZmU="
+
+generateJWT :: BL.ByteString -> ByteString
+generateJWT claims =
+  either mempty JWT.unJwt $ JWT.hmacEncode JWT.HS256 generateSecret (BL.toStrict claims)
+
+-- state check helpers
+stateCheck :: (Show a, Eq a) => (c -> m a) -> (st -> (String, c)) -> (a -> a) -> StateCheck st m
+stateCheck extractValue extractComponent expect = StateCheck (second extractValue . extractComponent) (flip shouldBe . expect)
+
+expectField :: forall s st a c m. (KnownSymbol s, Show a, Eq a, HasField s st c) => (c -> m a) -> (a -> a) -> StateCheck st m
+expectField extractValue = stateCheck extractValue ((symbolVal (Proxy @s),) . getField @s)
+
+checkState' :: (Traversable t, MonadIO m) => st -> t (StateCheck st m) -> m b -> m ()
+checkState' initialState checks act = do
+  expectations <- traverse (\(StateCheck g expect) -> let (msg, m) = g initialState in m >>= createExpectation msg m . expect) checks
+  void act
+  sequenceA_ expectations
+  where
+    createExpectation msg metrics expect = pure $ metrics >>= liftIO . annotate msg . expect
+
+expectCounter :: forall s st m. (KnownSymbol s, HasField s st Counter, MonadIO m) => (Int -> Int) -> StateCheck st m
+expectCounter = expectField @s intCounter
+  where
+    intCounter = ((round @Double @Int) <$>) . getCounter
+
+accumulateUntilTimeout :: Int -> (s -> a -> s) -> s -> IO a -> IO s
+accumulateUntilTimeout t f start act = do
+  tid <- myThreadId
+  -- mask to make sure TimeoutException is not thrown before starting the loop
+  mask $ \unmask -> do
+    -- start timeout thread unmasking exceptions
+    ttid <- forkIOWithUnmask ($ (threadDelay t *> throwTo tid TimeoutException))
+    -- unmask effect
+    unmask (fix (\loop accum -> (act >>= loop . f accum) `onTimeout` pure accum) start)
+      -- make sure we catch timeout if happens before entering the loop
+      `onTimeout` pure start
+      -- make sure timer thread is killed on other exceptions
+      -- so that it won't throw TimeoutException later
+      `onException` killThread ttid
+  where
+    onTimeout m a = m `catch` \TimeoutException -> a
+
+newObsChan :: Chan Observation -> IO ObsChan
+newObsChan = fmap <$> ObsChan <*> dupChan
+
+-- read messages from copy chan and once condition is met drain original to the same point
+-- upon timeout report error and messages remaining in the original chan
+-- that way we report messages since last successful read
+waitForObs :: HasCallStack => ObsChan -> Int -> Text -> (Observation -> Maybe a) -> IO ()
+waitForObs (ObsChan orig copy) t msg f =
+  timeout t (readUntil copy *> readUntil orig) >>= maybe failTimeout mempty
+  where
+    failTimeout = takeUntilTimeout decisecond (readChan orig)
+        >>= expectationFailure . DL.unlines . fmap show . (failureMessageHeader :) . fmap obsDiagMessage
+    failureMessageHeader = "Timeout waiting for " <> msg <> " at " <> loc <> ". Remaining observations:"
+    readUntil = void . untilM (pure . not . null . f) . readChan
+    loc = fromMaybe "(unknown)" . head $ (T.pack . prettySrcLoc . snd <$> getCallStack callStack)
+    -- execute effectful computation until result meets provided condition
+    untilM cond m = fix $ \loop -> m >>= \a -> ifM (cond a) (pure a) loop
+    -- duplicate the provided channel and construct wairFor function binding both channels
+    -- accumulate effecful computation results into a list for specified time
+    takeUntilTimeout t' = fmap reverse . accumulateUntilTimeout t' (flip (:)) []
+    decisecond = 100000
+
+obsDiagMessage :: Observation -> Text
+obsDiagMessage = \case
+  (HasqlPoolObs o) -> show o
+  o@(DBListenStart channel) -> constrName o <> show channel
+  o -> constrName o

test/observability/Observation/JwtCache.hs

@@ -1,33 +1,24 @@
-{-# LANGUAGE AllowAmbiguousTypes       #-}
+{-# LANGUAGE DataKinds        #-}
-{-# LANGUAGE DataKinds                 #-}
+{-# LANGUAGE NamedFieldPuns   #-}
-{-# LANGUAGE ExistentialQuantification #-}
+{-# LANGUAGE TypeApplications #-}
-{-# LANGUAGE FlexibleContexts          #-}
+module Observation.JwtCache where
-{-# LANGUAGE ImpredicativeTypes        #-}
-{-# LANGUAGE OverloadedStrings         #-}
-{-# LANGUAGE ScopedTypeVariables       #-}
-{-# LANGUAGE TypeApplications          #-}
-module Feature.Auth.JwtCacheSpec
-
-where
 
 import Network.Wai (Application)
 
 import Network.HTTP.Types
-import Test.Hspec         (Expectation, SpecWith, describe, it,
+import Test.Hspec         (SpecWith, describe, it)
-                           shouldBe)
 import Test.Hspec.Wai
 
-import Data.String                     (String)
+import ObsHelper
-import PostgREST.Metrics               (MetricsState (..))
+import PostgREST.Metrics   (MetricsState (..))
-import Prometheus                      (getCounter)
 import Protolude
-import SpecHelper
+import Test.Hspec.Wai.JSON (json)
-import Test.Hspec.Expectations.Contrib (annotate)
-import Test.Hspec.Wai.JSON             (json)
 
-spec :: SpecWith (MetricsState, Application)
+spec :: SpecWith (SpecState, Application)
 spec = describe "Server started with JWT and metrics enabled" $ do
   it "Should not have JWT in cache" $ do
+    expectCounters <- checkState' . specMetrics <$> getState
+
     let auth = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe1"}|]
 
     expectCounters
@@ -36,9 +27,11 @@ spec = describe "Server started with JWT and metrics enabled" $ do
       , hits     (+ 0)
       ] $
 
-         request methodGet "/authors_only" [auth] ""
+         request methodGet "/authors_only" [auth] "" `shouldRespondWith` 200
 
   it "Should have JWT in cache" $ do
+    expectCounters <- checkState' . specMetrics <$> getState
+
     let auth = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe2"}|]
 
     expectCounters
@@ -51,6 +44,8 @@ spec = describe "Server started with JWT and metrics enabled" $ do
       *> request methodGet "/authors_only" [auth] "" `shouldRespondWith` 200
 
   it "Should not cache invalid JWTs" $ do
+    expectCounters <- checkState' . specMetrics <$> getState
+
     let auth = authHeaderJWT "some random bytes"
 
     expectCounters
@@ -63,6 +58,8 @@ spec = describe "Server started with JWT and metrics enabled" $ do
       *> request methodGet "/authors_only" [auth] "" `shouldRespondWith` 401
 
   it "Should cache expired JWTs" $ do
+    expectCounters <- checkState' . specMetrics <$> getState
+
     let auth = genToken [json|{"exp": 1, "role": "postgrest_test_author", "id": "jdoe2"}|]
 
     expectCounters
@@ -75,6 +72,8 @@ spec = describe "Server started with JWT and metrics enabled" $ do
       *> request methodGet "/authors_only" [auth] "" `shouldRespondWith` 401
 
   it "Should evict entries from the JWT cache (jwt cache max is 2)" $ do
+    expectCounters <- checkState' . specMetrics <$> getState
+
     let jwt1 = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe3"}|]
         jwt2 = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe4"}|]
         jwt3 = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe5"}|]
@@ -94,6 +93,8 @@ spec = describe "Server started with JWT and metrics enabled" $ do
       *> request methodGet "/authors_only" [jwt3] ""
 
   it "Should not evict entries from the JWT cache in FIFO order" $ do
+    expectCounters <- checkState' . specMetrics <$> getState
+
     let jwt1 = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe6"}|]
         jwt2 = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe7"}|]
         jwt3 = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe8"}|]
@@ -120,6 +121,8 @@ spec = describe "Server started with JWT and metrics enabled" $ do
   -- The test case was added based on coverage report
   -- showing this scenario was not covered by previous tests
   it "Should evict entries even though all were hit" $ do
+    expectCounters <- checkState' . specMetrics <$> getState
+
     let jwt1 = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe9"}|]
         jwt2 = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe10"}|]
         jwt3 = genToken [json|{"exp": 9999999999, "role": "postgrest_test_author", "id": "jdoe11"}|]
@@ -143,25 +146,7 @@ spec = describe "Server started with JWT and metrics enabled" $ do
       *> request methodGet "/authors_only" [jwt3] ""
 
   where
-      counterToInt = second (fmap (round @Double @Int) . getCounter)
-      expectCounters = stateCheck . fmap (\(g, h) -> StateCheck (counterToInt . g) (flip shouldBe . h))
       genToken = authHeaderJWT . generateJWT
-      requests = (,) (getF @"jwtCacheRequests")
+      requests = expectCounter @"jwtCacheRequests"
-      hits = (,) (getF @"jwtCacheHits")
+      hits = expectCounter @"jwtCacheHits"
-      evictions = (,) (getF @"jwtCacheEvictions")
+      evictions = expectCounter @"jwtCacheEvictions"
-
-
--- should be moved to helpers???
-getF :: forall s r a. (KnownSymbol s, HasField s r a) => r -> (String, a)
-getF r = (symbolVal (Proxy @s), getField @s r)
-
-data StateCheck st = forall a. (Show a, Eq a) => StateCheck (st -> (String, WaiSession st a)) (a -> a -> Expectation)
-
-stateCheck :: (Traversable t) => t (StateCheck st) -> WaiSession st a -> WaiSession st ()
-stateCheck checks act = do
-  metrics <- getState
-  expectations <- traverse (\(StateCheck g expect) -> let (msg, m) = g metrics in m >>= createExpectation msg m . expect) checks
-  void act
-  sequenceA_ expectations
-  where
-    createExpectation msg metrics expect = pure $ metrics >>= liftIO . annotate msg . expect

test/observability/Observation/MetricsSpec.hs

@@ -0,0 +1,79 @@
+{-# LANGUAGE DataKinds           #-}
+{-# LANGUAGE FlexibleContexts    #-}
+{-# LANGUAGE MonadComprehensions #-}
+{-# LANGUAGE NamedFieldPuns      #-}
+{-# LANGUAGE TypeApplications    #-}
+
+module Observation.MetricsSpec where
+
+import           Data.List             (lookup)
+import           Network.Wai           (Application)
+import           ObsHelper
+import qualified PostgREST.AppState    as AppState
+import           PostgREST.Config      (AppConfig (configDbSchemas))
+import qualified PostgREST.Metrics     as Metrics
+import           PostgREST.Observation
+import           Prometheus            (getCounter, getVectorWith)
+import           Protolude
+import           Test.Hspec            (SpecWith, describe, it)
+import           Test.Hspec.Wai        (getState)
+
+spec :: SpecWith (SpecState, Application)
+spec = describe "Server started with metrics enabled" $ do
+  it "Should update pgrst_schema_cache_loads_total[SUCCESS]" $ do
+    SpecState{specAppState = appState, specMetrics = metrics, specObsChan} <- getState
+    let waitFor = waitForObs specObsChan
+
+    liftIO $ checkState' metrics [
+        schemaCacheLoads "SUCCESS" (+1)
+      ] $ do
+        AppState.schemaCacheLoader appState
+        waitFor (1 * sec) "SchemaCacheLoadedObs" $ \x -> [ o | o@(SchemaCacheLoadedObs{}) <- pure x]
+
+  it "Should update pgrst_schema_cache_loads_total[ERROR]" $ do
+    SpecState{specAppState = appState, specMetrics = metrics, specObsChan} <- getState
+    let waitFor = waitForObs specObsChan
+
+    liftIO $ checkState' metrics [
+        schemaCacheLoads "FAIL" (+1),
+        schemaCacheLoads "SUCCESS" (+1)
+      ] $ do
+        AppState.getConfig appState >>= \prev -> do
+          AppState.putConfig appState $ prev { configDbSchemas = pure "bad_schema" }
+          AppState.schemaCacheLoader appState
+          waitFor (1 * sec) "SchemaCacheErrorObs" $ \x -> [ o | o@(SchemaCacheErrorObs{}) <- pure x]
+          AppState.putConfig appState prev
+
+        -- wait up to 2 secs so that retry can happen
+        waitFor (2 * sec) "SchemaCacheLoadedObs" $ \x -> [ o | o@(SchemaCacheLoadedObs{}) <- pure x]
+
+  it "Should debounce schema cache loads" $ do
+    SpecState{specAppState = appState, specMetrics = metrics, specObsChan} <- getState
+    let waitFor = waitForObs specObsChan
+
+    liftIO $ checkState' metrics [
+        -- we expect exactly 2 successful schema cache loads
+        schemaCacheLoads "FAIL" (+0),
+        schemaCacheLoads "SUCCESS" (+2)
+      ] $ do
+        AppState.schemaCacheLoader appState
+        -- at this moment there is no dedicated observation emited
+        -- when schema cache load starts
+        -- so we wait for DBConnectedObs which is emited right after successful
+        -- PostgreSQL version query during schema cache loading
+        waitFor (1 * sec) "DBConnectedObs" $ \x -> [ o | o@(DBConnectedObs{}) <- pure x]
+        -- request schema cache load multiple times
+        -- all of them should be handled by a single schema cache load
+        replicateM_ 100 (AppState.schemaCacheLoader appState)
+        -- wait for two expected SchemaCacheLoadedObs events
+        replicateM_ 2 $ waitFor (1 * sec) "SchemaCacheLoadedObs" $ \x -> [ o | o@(SchemaCacheLoadedObs{}) <- pure x]
+        -- wait 1 sec to make sure we capture all potential schema cache loads
+        -- (there should be none but we need to verify that)
+        threadDelay $ 1 * sec
+
+
+  where
+    -- prometheus-client api to handle vectors is convoluted
+    schemaCacheLoads label = expectField @"schemaCacheLoads" $
+      fmap (maybe (0::Int) round . lookup label) . (`getVectorWith` getCounter)
+    sec = 1000000

test/observability/fixtures/database.sql

@@ -0,0 +1,2 @@
+-- Suppress NOTICE: ... messages
+SET client_min_messages TO warning;

test/observability/fixtures/load.sql

@@ -0,0 +1,8 @@
+-- Loads all fixtures for the PostgREST observability tests
+
+\set ON_ERROR_STOP on
+
+\ir database.sql
+\ir roles.sql
+\ir schema.sql
+\ir privileges.sql

test/observability/fixtures/privileges.sql

@@ -0,0 +1,9 @@
+-- Schema test objects
+SET search_path = test, pg_catalog;
+
+GRANT USAGE ON SCHEMA test TO postgrest_test_anonymous;
+GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA test TO postgrest_test_anonymous;
+REVOKE ALL PRIVILEGES ON TABLE authors_only FROM postgrest_test_anonymous;
+
+GRANT USAGE ON SCHEMA test TO postgrest_test_author;
+GRANT ALL ON TABLE authors_only TO postgrest_test_author;

test/observability/fixtures/roles.sql

@@ -0,0 +1,5 @@
+DROP ROLE IF EXISTS postgrest_test_anonymous, postgrest_test_author;
+CREATE ROLE postgrest_test_anonymous;
+CREATE ROLE postgrest_test_author;
+
+GRANT postgrest_test_anonymous, postgrest_test_author TO :PGUSER;

test/observability/fixtures/schema.sql

@@ -0,0 +1,21 @@
+DROP SCHEMA IF EXISTS test;
+
+CREATE SCHEMA test;
+
+SET search_path = test, pg_catalog;
+
+--
+-- Name: authors_only; Type: TABLE; Schema: test; Owner: -
+--
+
+CREATE TABLE authors_only (
+    owner character varying NOT NULL,
+    secret character varying NOT NULL
+);
+
+--
+-- Name: authors_only_pkey; Type: CONSTRAINT; Schema: test; Owner: -
+--
+
+ALTER TABLE ONLY authors_only
+    ADD CONSTRAINT authors_only_pkey PRIMARY KEY (secret);

test/pgbench/README.md

@@ -3,9 +3,9 @@
 Can be used as:
 
 ```
-postgrest-with-postgresql-15 -f test/pgbench/fixtures.sql pgbench -U postgres -n -T 10 -f test/pgbench/1567/old.sql
+postgrest-with-pg-15 -f test/pgbench/fixtures.sql pgbench -U postgres -n -T 10 -f test/pgbench/1567/old.sql
 
-postgrest-with-postgresql-15 -f test/pgbench/fixtures.sql pgbench -U postgres -n -T 10 -f test/pgbench/1567/new.sql
+postgrest-with-pg-15 -f test/pgbench/fixtures.sql pgbench -U postgres -n -T 10 -f test/pgbench/1567/new.sql
 ```
 
 ## Directory structure

test/spec/Feature/ConcurrentSpec.hs

@@ -25,7 +25,7 @@ spec =
       raceTest 10 $
         get "/fakefake"
           `shouldRespondWith`
-          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.factories'","message":"Could not find the table 'test.fakefake' in the schema cache"} |]
+          [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.fakefake' in the schema cache"} |]
           { matchStatus  = 404
           , matchHeaders = []
           }

test/spec/Feature/OpenApi/OpenApiSpec.hs

@@ -157,7 +157,7 @@ spec = describe "OpenAPI" $ do
               "properties": {
                 "id": {
                   "description": "child_entities id comment\n\nNote:\nThis is a Primary Key.<pk/>",
-                  "format": "integer",
+                  "format": "int32",
                   "type": "integer"
                 },
                 "name": {
@@ -167,7 +167,7 @@ spec = describe "OpenAPI" $ do
                 },
                 "parent_id": {
                   "description": "Note:\nThis is a Foreign Key to `entities.id`.<fk table='entities' column='id'/>",
-                  "format": "integer",
+                  "format": "int32",
                   "type": "integer"
                 }
               },
@@ -192,7 +192,7 @@ spec = describe "OpenAPI" $ do
               "properties": {
                 "id": {
                   "description": "child_entities_view id comment\n\nNote:\nThis is a Primary Key.<pk/>",
-                  "format": "integer",
+                  "format": "int32",
                   "type": "integer"
                 },
                 "name": {
@@ -202,7 +202,7 @@ spec = describe "OpenAPI" $ do
                 },
                 "parent_id": {
                   "description": "Note:\nThis is a Foreign Key to `entities.id`.<fk table='entities' column='id'/>",
-                  "format": "integer",
+                  "format": "int32",
                   "type": "integer"
                 }
               }
@@ -232,7 +232,7 @@ spec = describe "OpenAPI" $ do
         referralLink `shouldBe` Just
           [aesonQQ|
             {
-              "format": "integer",
+              "format": "int32",
               "type": "integer",
               "description": "Note:\nThis is a Foreign Key to `second.id`.<fk table='second' column='id'/>"
             }
@@ -337,7 +337,7 @@ spec = describe "OpenAPI" $ do
         referralLink `shouldBe` Just
           [aesonQQ|
             {
-              "format": "integer",
+              "format": "int32",
               "type": "integer",
               "description": "Note:\nThis is a Foreign Key to `pages.link`.<fk table='pages' column='link'/>"
             }
@@ -354,7 +354,7 @@ spec = describe "OpenAPI" $ do
         referralLink `shouldBe` Just
           [aesonQQ|
             {
-              "format": "integer",
+              "format": "int32",
               "type": "integer",
               "description": "Note:\nThis is a Foreign Key to `clients.id`.<fk table='clients' column='id'/>"
             }
@@ -432,7 +432,7 @@ spec = describe "OpenAPI" $ do
         types `shouldBe` Just
           [aesonQQ|
             {
-              "format": "smallint",
+              "format": "int32",
               "type": "integer"
             }
           |]
@@ -447,7 +447,7 @@ spec = describe "OpenAPI" $ do
         types `shouldBe` Just
           [aesonQQ|
             {
-              "format": "integer",
+              "format": "int32",
               "type": "integer"
             }
           |]
@@ -462,7 +462,7 @@ spec = describe "OpenAPI" $ do
         types `shouldBe` Just
           [aesonQQ|
             {
-              "format": "bigint",
+              "format": "int64",
               "type": "integer"
             }
           |]
@@ -850,7 +850,7 @@ spec = describe "OpenAPI" $ do
                 "type": "string"
               },
               {
-                "format": "integer",
+                "format": "int32",
                 "in": "query",
                 "name": "integer",
                 "required": false,
@@ -992,7 +992,7 @@ spec = describe "OpenAPI" $ do
                   "items": {}
                 },
                 "integer": {
-                  "format": "integer",
+                  "format": "int32",
                   "type": "integer"
                 },
                 "json": {

test/spec/Feature/Query/AndOrParamsSpec.hs

@@ -252,21 +252,39 @@ spec =
             [json|[{"id": 7, "entities":null}, {"id": 8, "entities": {"id": 2}}, {"id": 9, "entities": {"id": 3}}]|]
             { matchStatus = 201 }
 
-    context "used with PATCH" $
+    context "used with PATCH" $ do
       it "succeeds when using and/or params" $
         request methodPatch "/grandchild_entities?or=(id.eq.1,id.eq.2)&select=id,name"
           [("Prefer", "return=representation")]
           [json|{ name : "updated grandchild entity"}|] `shouldRespondWith`
           [json|[{ "id": 1, "name" : "updated grandchild entity"},{ "id": 2, "name" : "updated grandchild entity"}]|]
           { matchHeaders = [matchContentTypeJson] }
+      it "succeeds when the filtered column is modified" $
+        request methodPatch "/entities?select=id,name&or=(name.is.null,name.like.*test*)"
+          [("Prefer", "return=representation")]
+          [json|{ "name" : "updated entity" }|] `shouldRespondWith`
+          [json|[{ "id": 4, "name": "updated entity" }]|]
+          { matchHeaders = [matchContentTypeJson] }
+      it "succeeds when the filtered column is not selected in the returned representation" $
+        request methodPatch "/entities?select=id&or=(name.is.null,name.like.*test*)"
+          [("Prefer", "return=representation")]
+          [json|{ "name" : "updated entity" }|] `shouldRespondWith`
+          [json|[{ "id": 4 }]|]
+          { matchHeaders = [matchContentTypeJson] }
 
-    context "used with DELETE" $
+    context "used with DELETE" $ do
       it "succeeds when using and/or params" $
         request methodDelete "/grandchild_entities?or=(id.eq.1,id.eq.2)&select=id,name"
             [("Prefer", "return=representation")]
             ""
           `shouldRespondWith`
             [json|[{ "id": 1, "name" : "grandchild entity 1" },{ "id": 2, "name" : "grandchild entity 2" }]|]
+      it "succeeds when the filtered column is not selected in the returned representation" $
+        request methodDelete "/entities?select=id&or=(name.is.null,name.like.*test*)"
+            [("Prefer", "return=representation")]
+            ""
+          `shouldRespondWith`
+            [json|[{ "id": 4 }]|]
 
     it "can query columns that begin with and/or reserved words" $
       get "/grandchild_entities?or=(and_starting_col.eq.smth, or_starting_col.eq.smth)" `shouldRespondWith` 200

test/spec/Feature/Query/DeleteSpec.hs

@@ -116,7 +116,7 @@ spec =
       it "fails with 404" $
         request methodDelete "/foozle?id=eq.101" [] ""
           `shouldRespondWith`
-          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.foo'","message":"Could not find the table 'test.foozle' in the schema cache"} |]
+          [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.foozle' in the schema cache"} |]
           { matchStatus = 404
           , matchHeaders = []
           }

test/spec/Feature/Query/ErrorSpec.hs

@@ -42,10 +42,10 @@ pgErrorCodeMapping = do
       it "works with SchemaCache error" $
         get "/non_existent_table"
           `shouldRespondWith`
-          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.collision_test_table'","message":"Could not find the table 'test.non_existent_table' in the schema cache"} |]
+          [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.non_existent_table' in the schema cache"} |]
           { matchStatus  = 404
           , matchHeaders = [ "Proxy-Status" <:> "PostgREST; error=PGRST205"
-                           , "Content-Length" <:> "182" ]
+                           , "Content-Length" <:> "129" ]
           }
 
       it "works with Jwt error" $ do
@@ -75,3 +75,30 @@ pgErrorCodeMapping = do
           , matchHeaders = [ "Proxy-Status" <:> "PostgREST; error=123"
                            , "Content-Length" <:> "59" ]
           }
+
+    context "show hint on PGRST205 table not found error" $ do
+      it "show hint when similarity score is at least 75%" $ do
+        get "/projectx" -- at least 75% similar to "projects"
+          `shouldRespondWith`
+          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.projects'","message":"Could not find the table 'test.projectx' in the schema cache"} |]
+          { matchStatus  = 404
+          , matchHeaders = [ "Proxy-Status" <:> "PostgREST; error=PGRST205"
+                           , "Content-Length" <:> "160" ]
+          }
+
+        get "/projecxx" -- at least 75% similar to "projects"
+          `shouldRespondWith`
+          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.projects'","message":"Could not find the table 'test.projecxx' in the schema cache"} |]
+          { matchStatus  = 404
+          , matchHeaders = [ "Proxy-Status" <:> "PostgREST; error=PGRST205"
+                           , "Content-Length" <:> "160" ]
+          }
+
+      it "don't show hint when similarity score is less than 75%" $
+        get "/projxxxx" -- less than 75% similar to "projects"
+          `shouldRespondWith`
+          [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.projxxxx' in the schema cache"} |]
+          { matchStatus  = 404
+          , matchHeaders = [ "Proxy-Status" <:> "PostgREST; error=PGRST205"
+                           , "Content-Length" <:> "119" ]
+          }

test/spec/Feature/Query/InsertSpec.hs

@@ -138,6 +138,7 @@ spec actualPgVersion = do
             , matchHeaders = [ matchHeaderAbsent hContentType
                              , "Location" <:> "/projects?id=eq.11"
                              , "Content-Range" <:> "*/*"
+                             , "Content-Length" <:> "0"
                              , "Preference-Applied" <:> "return=headers-only"]
             }
 
@@ -151,6 +152,7 @@ spec actualPgVersion = do
             , matchHeaders = [ matchHeaderAbsent hContentType
                              , "Location" <:> "/car_models?name=eq.Enzo&year=eq.2021"
                              , "Content-Range" <:> "*/*"
+                             , "Content-Length" <:> "0"
                              , "Preference-Applied" <:> "return=headers-only"]
             }
 
@@ -163,7 +165,8 @@ spec actualPgVersion = do
             ""
             { matchStatus  = 201
             , matchHeaders = [ matchHeaderAbsent hContentType
-                             , matchHeaderAbsent hLocation ]
+                             , matchHeaderAbsent hLocation
+                             , "Content-Length" <:> "0"]
             }
 
     context "from an html form" $
@@ -175,7 +178,8 @@ spec actualPgVersion = do
           `shouldRespondWith`
             ""
             { matchStatus = 201
-            , matchHeaders = [ matchHeaderAbsent hContentType ]
+            , matchHeaders = [ matchHeaderAbsent hContentType
+                             , "Content-Length" <:> "0"]
             }
 
     context "with no pk supplied" $ do
@@ -199,6 +203,7 @@ spec actualPgVersion = do
               ""
               { matchStatus  = 201
               , matchHeaders = [ matchHeaderAbsent hContentType
+                               , "Content-Length" <:> "0"
                                , "Location" <:> "/auto_incrementing_pk?id=eq.2"
                                , "Preference-Applied" <:> "return=headers-only"]
               }
@@ -478,7 +483,7 @@ spec actualPgVersion = do
             {"id": 204, "body": "yyy"},
             {"id": 205, "body": "zzz"}]|]
           `shouldRespondWith`
-          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.articles'","message":"Could not find the table 'test.garlic' in the schema cache"} |]
+          [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.garlic' in the schema cache"} |]
           { matchStatus  = 404
           , matchHeaders = []
           }
@@ -741,6 +746,7 @@ spec actualPgVersion = do
           ""
           { matchStatus = 201
           , matchHeaders = [matchHeaderAbsent hContentType
+                           , "Content-Length" <:> "0"
                            , "Preference-Applied" <:> "return=minimal"]
           }
 
@@ -753,7 +759,8 @@ spec actualPgVersion = do
             ""
             { matchStatus  = 201
             , matchHeaders = [ matchHeaderAbsent hContentType
-                             , matchHeaderAbsent hLocation ]
+                             , matchHeaderAbsent hLocation
+                             , "Content-Length" <:> "0"]
             }
 
       it "returns a location header with pks from both tables" $
@@ -765,6 +772,7 @@ spec actualPgVersion = do
             , matchHeaders = [ matchHeaderAbsent hContentType
                              , "Location" <:> "/with_multiple_pks?pk1=eq.1&pk2=eq.2"
                              , "Content-Range" <:> "*/*"
+                             , "Content-Length" <:> "0"
                              , "Preference-Applied" <:> "return=headers-only"]
             }
 
@@ -778,6 +786,7 @@ spec actualPgVersion = do
             , matchHeaders = [ matchHeaderAbsent hContentType
                              , "Location" <:> "/compound_pk_view?k1=eq.1&k2=eq.test"
                              , "Content-Range" <:> "*/*"
+                             , "Content-Length" <:> "0"
                              , "Preference-Applied" <:> "return=headers-only"]
             }
 
@@ -790,6 +799,7 @@ spec actualPgVersion = do
             , matchHeaders = [ matchHeaderAbsent hContentType
                              , "Location" <:> "/test_null_pk_competitors_sponsors?id=eq.1&sponsor_id=is.null"
                              , "Content-Range" <:> "*/*"
+                             , "Content-Length" <:> "0"
                              , "Preference-Applied" <:> "return=headers-only"]
             }
 
@@ -807,6 +817,7 @@ spec actualPgVersion = do
             , matchHeaders = [ matchHeaderAbsent hContentType
                              , "Location" <:> "/datarep_todos?id=eq.5"
                              , "Content-Range" <:> "*/*"
+                             , "Content-Length" <:> "0"
                              , "Preference-Applied" <:> "return=headers-only"]
             }
 
@@ -862,6 +873,7 @@ spec actualPgVersion = do
             , matchHeaders = [ matchHeaderAbsent hContentType
                              , "Location" <:> "/datarep_todos_computed?id=eq.5"
                              , "Content-Range" <:> "*/*"
+                             , "Content-Length" <:> "0"
                              , "Preference-Applied" <:> "return=headers-only"]
             }
 

test/spec/Feature/Query/MultipleSchemaSpec.hs

@@ -281,7 +281,7 @@ spec =
                     "properties" : {
                       "id" : {
                         "description" : "Note:\nThis is a Primary Key.<pk/>",
-                        "format" : "integer",
+                        "format" : "int32",
                         "type" : "integer"
                       },
                       "name" : {
@@ -310,7 +310,7 @@ spec =
                     "properties" : {
                       "id" : {
                         "description" : "Note:\nThis is a Primary Key.<pk/>",
-                        "format" : "integer",
+                        "format" : "int32",
                         "type" : "integer"
                       },
                       "name" : {
@@ -339,7 +339,7 @@ spec =
                     "properties" : {
                       "id" : {
                         "description" : "Note:\nThis is a Primary Key.<pk/>",
-                        "format" : "integer",
+                        "format" : "int32",
                         "type" : "integer"
                       },
                       "name" : {
@@ -368,7 +368,7 @@ spec =
                     "properties" : {
                       "id" : {
                         "description" : "Note:\nThis is a Primary Key.<pk/>",
-                        "format" : "integer",
+                        "format" : "int32",
                         "type" : "integer"
                       },
                       "another_value" : {

test/spec/Feature/Query/QuerySpec.hs

@@ -26,9 +26,9 @@ spec = do
     it "causes a 404" $
       get "/faketable"
       `shouldRespondWith`
-      [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.private_table'","message":"Could not find the table 'test.faketable' in the schema cache"} |]
+      [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.faketable' in the schema cache"} |]
       { matchStatus = 404
-      , matchHeaders = ["Content-Length" <:> "166"]
+      , matchHeaders = ["Content-Length" <:> "120"]
       }
 
   describe "Filtering response" $ do
@@ -852,7 +852,7 @@ spec = do
       -- the existence of first table, #3869
       it "table not found error if first table does not exist" $
         get "/car_model_sales_202101?select=id,name,car_models(id,name)&order=id.asc" `shouldRespondWith`
-          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.car_model_sales'","message":"Could not find the table 'test.car_model_sales_202101' in the schema cache"} |]
+          [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.car_model_sales_202101' in the schema cache"} |]
           { matchStatus  = 404
           , matchHeaders = [matchContentTypeJson]
           }
@@ -870,7 +870,7 @@ spec = do
 
       it "table not found error if first table does not exist" $
         get "/car_models_default?select=id,name,car_model_sales(id,name)&order=id.asc" `shouldRespondWith`
-          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.car_model_sales'","message":"Could not find the table 'test.car_models_default' in the schema cache"} |]
+          [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.car_models_default' in the schema cache"} |]
           { matchStatus  = 404
           , matchHeaders = [matchContentTypeJson]
           }

test/spec/Feature/Query/RangeSpec.hs

@@ -126,7 +126,8 @@ spec = do
                 "hint":null
               }|]
             { matchStatus  = 416
-            , matchHeaders = ["Content-Range" <:> "*/0"]
+            , matchHeaders = [ "Content-Range" <:> "*/0"
+                             , "Content-Length" <:> "144"]
             }
 
         it "refuses a range requesting start past last item" $
@@ -288,7 +289,8 @@ spec = do
                 "hint":null
               }|]
             { matchStatus  = 416
-            , matchHeaders = ["Content-Range" <:> "*/0"]
+            , matchHeaders = [ "Content-Range" <:> "*/0"
+                             , "Content-Length" <:>  "144"]
             }
 
         it "refuses a range requesting start past last item" $
@@ -470,7 +472,8 @@ spec = do
                 "hint":null
               }|]
             { matchStatus  = 416
-            , matchHeaders = ["Content-Range" <:> "*/0"]
+            , matchHeaders = [ "Content-Range" <:> "*/0"
+                             , "Content-Length" <:> "144"]
             }
 
         it "refuses a range requesting start past last item" $

test/spec/Feature/Query/RpcSpec.hs

@@ -1146,12 +1146,24 @@ spec =
             }
 
       context "single unnamed param" $ do
-        it "can insert json directly" $
+        it "can insert json directly with unnamed parameter" $
           post "/rpc/unnamed_json_param"
               [json|{"A": 1, "B": 2, "C": 3}|]
             `shouldRespondWith`
               [json|{"A": 1, "B": 2, "C": 3}|]
 
+        it "rejects json body when single param has a name" $
+          post "/rpc/named_json_param"
+              [json|{"A": 1, "B": 2, "C": 3}|]
+            `shouldRespondWith`
+              [json|{
+                "code":"PGRST202",
+                "message":"Could not find the function test.named_json_param(A, B, C) in the schema cache",
+                "details":"Searched for the function test.named_json_param with parameters A, B, C or with a single unnamed json/jsonb parameter, but no matches were found in the schema cache.",
+                "hint":null
+              }|]
+              { matchStatus = 404 }
+
         it "can insert text directly" $ do
           request methodPost "/rpc/unnamed_text_param"
             [("Content-Type", "text/plain"), ("Accept", "text/plain")]

test/spec/Feature/Query/UpdateSpec.hs

@@ -18,9 +18,9 @@ spec = do
         request methodPatch "/fake" []
           [json| { "real": false } |]
           `shouldRespondWith`
-          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.factories'","message":"Could not find the table 'test.fake' in the schema cache"} |]
+          [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.fake' in the schema cache"} |]
           { matchStatus = 404
-          , matchHeaders = ["Content-Length" <:> "157"]
+          , matchHeaders = ["Content-Length" <:> "115"]
           }
 
 
@@ -363,9 +363,9 @@ spec = do
             {"id": 204, "body": "yyy"},
             {"id": 205, "body": "zzz"}]|]
           `shouldRespondWith`
-          [json| {"code":"PGRST205","details":null,"hint":"Perhaps you meant the table 'test.articles'","message":"Could not find the table 'test.garlic' in the schema cache"} |]
+          [json| {"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'test.garlic' in the schema cache"} |]
           { matchStatus  = 404
-          , matchHeaders = ["Content-Length" <:> "158"]
+          , matchHeaders = ["Content-Length" <:> "117"]
           }
 
       context "apply defaults on missing values" $ do

test/spec/Feature/RollbackSpec.hs

@@ -20,7 +20,9 @@ postItem =
     `shouldRespondWith`
       ""
       { matchStatus  = 201
-      , matchHeaders = [matchHeaderAbsent hContentType]  }
+      , matchHeaders = [ matchHeaderAbsent hContentType
+                       , "Content-Length" <:> "0" ]
+      }
 
 -- removes Items left over from POST, PUT, and PATCH
 deleteItems =

test/spec/Main.hs

@@ -23,7 +23,6 @@ import qualified Feature.Auth.AsymmetricJwtSpec
 import qualified Feature.Auth.AudienceJwtSecretSpec
 import qualified Feature.Auth.AuthSpec
 import qualified Feature.Auth.BinaryJwtSecretSpec
-import qualified Feature.Auth.JwtCacheSpec
 import qualified Feature.Auth.NoAnonSpec
 import qualified Feature.Auth.NoJwtSecretSpec
 import qualified Feature.ConcurrentSpec
@@ -84,13 +83,12 @@ main = do
 
   -- cached schema cache so most tests run fast
   baseSchemaCache <- loadSCache pool testCfg
-  sockets <- AppState.initSockets testCfg
   loggerState <- Logger.init
   metricsState <- Metrics.init (configDbPoolSize testCfg)
 
   let
     initApp sCache st config = do
-      appState <- AppState.initWithPool sockets pool config loggerState metricsState (Metrics.observationMetrics metricsState)
+      appState <- AppState.initWithPool pool config loggerState metricsState (Metrics.observationMetrics metricsState)
       AppState.putPgVersion appState actualPgVersion
       AppState.putSchemaCache appState (Just sCache)
       return (st, postgrest (configLogLevel config) appState (pure ()))
@@ -275,9 +273,6 @@ main = do
     before pgSafeUpdateApp $
       describe "Feature.Query.PgSafeUpdateSpec.spec" Feature.Query.PgSafeUpdateSpec.spec
 
-    before (initApp baseSchemaCache metricsState testCfgJwtCache) $
-      describe "Feature.Auth.JwtCacheSpec" Feature.Auth.JwtCacheSpec.spec
-
   where
     loadSCache pool conf =
       either (panic.show) id <$> P.use pool (HT.transaction HT.ReadCommitted HT.Read $ querySchemaCache conf)

test/spec/SpecHelper.hs

@@ -1,4 +1,3 @@
-{-# LANGUAGE FlexibleContexts #-}
 module SpecHelper where
 
 import           Control.Lens           ((^?))
@@ -206,14 +205,6 @@ testCfgBinaryJWT =
   , configJWKS = rightToMaybe $ parseSecret generateSecret
   }
 
-testCfgJwtCache :: AppConfig
-testCfgJwtCache =
-  baseCfg {
-    configJwtSecret = Just generateSecret
-  , configJWKS = rightToMaybe $ parseSecret generateSecret
-  , configJwtCacheMaxEntries = 2
-  }
-
 testCfgAudienceJWT :: AppConfig
 testCfgAudienceJWT =
   baseCfg {

test/spec/fixtures/schema.sql

@@ -2356,6 +2356,11 @@ create or replace function test.unnamed_json_param(json) returns json as $$
   select $1;
 $$ language sql;
 
+-- Function with a NAMED json parameter (for testing single param fallback behavior)
+create or replace function test.named_json_param(data json) returns json as $$
+  select data;
+$$ language sql;
+
 create or replace function test.unnamed_text_param(text) returns "text/plain" as $$
   select $1::"text/plain";
 $$ language sql;