for HTTP POST stored procedures (/rpc) ignores schema

[mrl5]

Description

For HTTP POST stored procedures (/rpc) ignores schema preference defined in Accept-Profile header (ref: https://postgrest.org/en/v12/references/api/schemas.html#schemas). Interestingly this is not a case for HTTP GET.

Environment

• PostgreSQL version: postgres:16.3
• PostgREST version: postgrest/postgrest:v12.0.3
• Operating system: Linux pc 6.3.11_p1-debian-sources #1 SMP PREEMPT_DYNAMIC Wed Dec 27 15:06:33 CET 2023 x86_64 GNU/Linux

Setup

docker-compose.yml

services:
  db:
    image: postgres:16.3
    restart: unless-stopped
    volumes:
      - db_data:/var/lib/postgresql/data
    ports:
      - 5432:5432
    command: postgres
    environment:
      POSTGRES_PASSWORD: changeme
      POSTGRES_DB: test
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready -U postgres" ]
      interval: 10s
      timeout: 5s
      retries: 5
  api:
    image: "postgrest/postgrest:v12.0.3"
    ports:
      - 3000:3000
    environment:
      PGRST_DB_URI: postgres://postgres:changeme@db:5432/test?sslmode=disable
      PGRST_DB_SCHEMAS: "api, worker"
      PGRST_DB_ANON_ROLE: "anonymous"
    depends_on:
      db:
        condition: service_healthy

volumes:
  db_data: null

bootstrap:

docker compose up db

CREATE ROLE authenticator WITH NOINHERIT LOGIN NOCREATEDB NOCREATEROLE NOSUPERUSER PASSWORD 'changeme';

CREATE SCHEMA api;

CREATE SCHEMA worker;

CREATE ROLE anonymous NOLOGIN;

GRANT anonymous TO authenticator;

GRANT USAGE ON SCHEMA api TO anonymous;

GRANT USAGE ON SCHEMA worker TO anonymous;

CREATE FUNCTION worker.baz () RETURNS int AS $$ select 1 $$ LANGUAGE sql;

CREATE FUNCTION api.foo (timeout int) RETURNS int AS $$ select timeout+1 $$ LANGUAGE sql;

run

docker compose up api

Steps to reproduce vol.1

curl -i -X POST -d '{"timeout":59}' -H "content-type: application/json" http://localhost:3000/rpc/foo
curl -i -H "Accept-Profile: worker" http://localhost:3000/rpc/baz
curl -i -X POST -H "Accept-Profile: worker" http://localhost:3000/rpc/baz

Expected

HTTP/1.1 200 OK

HTTP/1.1 200 OK

HTTP/1.1 200 OK

Actual

HTTP/1.1 200 OK

HTTP/1.1 200 OK

HTTP/1.1 404 Not Found

{"code":"PGRST202","details":"Searched for the function api.baz without parameters or with a single unnamed json/jsonb parameter, but no matches were found in the schema cache.","hint":null,"message":"Could not find the function api.baz without parameters in the schema cache"}

Steps to reproduce vol.2

lets change the schema order in docker-compose.yml

diff --git a/docker-compose.yml b/docker-compose.yml
index cee1897..4611560 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -21,7 +21,7 @@ services:
       - 3000:3000
     environment:
       PGRST_DB_URI: postgres://postgres:changeme@db:5432/test?sslmode=disable
-      PGRST_DB_SCHEMAS: "api, worker"
+      PGRST_DB_SCHEMAS: "worker, api"
       PGRST_DB_ANON_ROLE: "anonymous"
     depends_on:
       db:

kill api container and again run

docker compose up api

curl -i -H "Accept-Profile: api" -X POST -d '{"timeout":59}' -H "content-type: application/json" http://localhost:3000/rpc/foo
curl -i http://localhost:3000/rpc/baz
curl -i -X POST http://localhost:3000/rpc/baz

Expected

HTTP/1.1 200 OK

HTTP/1.1 200 OK

HTTP/1.1 200 OK

Actual

HTTP/1.1 404 Not Found

{"code":"PGRST202","details":"Searched for the function worker.foo with parameter timeout or with a single unnamed json/jsonb parameter, but no matches were found in the schema cache.","hint":null,"message":"Could not find the function worker.foo(timeout) in the schema cache"}

HTTP/1.1 200 OK

HTTP/1.1 200 OK

[mrl5]

ah, damn I just realized that with HTTP POST I should be using Content-Profile instead of Accept-Profile as per https://postgrest.org/en/v12/references/api/schemas.html#other-methods

curl -i -X POST -d '{"timeout":59}' -H "content-type: application/json" http://localhost:3000/rpc/foo
curl -i -H "Accept-Profile: worker" http://localhost:3000/rpc/baz
- curl -i -X POST -H "Accept-Profile: worker" http://localhost:3000/rpc/baz
+ curl -i -X POST -H "Content-Profile: worker" http://localhost:3000/rpc/baz