POST to a new table returns a 404, but GET on the same table returns a 200

[jbstewart]

Environment

• PostgreSQL version: Postgres 15.5 running on my local development machine on port 5432
• PostgREST version: PostgREST 12.0.2 running on my local machine from an unmodified docker image pulled from postgrest/postgrest
• Operating system: macos 12.6.1

Description of issue

I added a table ApiVersion to my pmg schema through a database migration. My problem is that when I try to POST a record in Postman to /ApiVersion, I get a 404 with no error body. I have another request in Postman that is a GET /ApiVersion and I get a 200 on that route.

• I did try both sending the server a SIGUSR1 signal (multiple times), and completely restarting the server, with no success.
• I have tried posting as both a single record and an array containing the record with the same results.
• I have tried POSTing to other tables in other schemas and that works with no problems
• I am wondering if there is some kind of database permissions problem causing this - but I would expect in that case to return a 403 and an error body describing the problem - not a 404 with an empty error body.
• I checked and my user db_admin has both USAGE and CREATE privileges on the pmg schema
• I checked the table permissions relating to db_admin and got: db_admin,pmg,ApiVersion,{db_admin=arwdDxt/db_admin}
  So, the db_admin role has been granted INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER privileges on the table.
• I also checked the sequence permissions for the sequence used in the table index: db_admin,pmg,ApiVersion_id_seq,{db_admin=rwU/db_admin}. So, the db_admin role has been granted SELECT, UPDATE, and USAGE privileges on the sequence.

Summary

I would expect to be able to insert records in the pmg."ApiVersion" table with a POST request, but I get a 404. I can do a GET on the table with a 200.

Steps to Reproduce

Table Creation

I create the table using a Knex migration, but the equivalent SQL (supplied by TablePlus) is below:

CREATE SEQUENCE IF NOT EXISTS pmg."ApiVersion_id_seq";
CREATE TABLE "pmg"."ApiVersion" (
"id" int4 NOT NULL DEFAULT nextval('pmg."ApiVersion_id_seq"'::regclass),
"name" varchar NOT NULL,
"activeDate" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ("id")
);

Populate the table

SET ROLE db_admin;
INSERT INTO pmg."ApiVersion" ("name") VALUES ('weird-wallaby-92');

GET request

curl --location 'http://my.server/ApiVersion?order=id.desc&limit=1'
--header 'Accept-Profile: pmg'
--header 'Authorization: Bearer (token containing the db_admin role)

Result:

[
    {
        "id": 1,
        "name": "weird-wallaby-92",
        "activeDate": "2024-02-16T13:48:10.548943-07:00"
    }
]

POST request

curl --location 'http://my.server/ApiVersion'
--header 'Accept-Profile: pmg'
--header 'Content-Type: application/json'
--header 'Authorization: Bearer (token containing the db_admin role)
--data '[
{
"name": "weird-wallaby-93"
}
]'

Result:
{}

[laurenceisla]

Ah, for methods different than GET the header to access another schema is Content-Profile instead of Accept-Profile. Does this work for you:

curl --location 'http://my.server/ApiVersion'
--header 'Content-Profile: pmg'
--header 'Content-Type: application/json'
--header 'Authorization: Bearer (token containing the db_admin role)
--data '[
{
"name": "weird-wallaby-93"
}
]'

[jbstewart]

I should add that I went through all the reproduction steps above, but instead of using the pmg schema I used a different schema (one that I've been using for a little longer) and this problem did NOT manifest. So it seems to be something connected to the pmg schema. Could be a permissions issue, but I've checked everything I can think of permissions-wise. And I still believe if it's a permissions issue it should manifest as a 403 with an error body.

[jbstewart]

@laurenceisla YES! That fixed it. But the weird thing is the other POST requests I have in Postman against tables in a different schema ('industrial'), worked with the Accept-Profile header, which is quite strange. Thank you so much!

[steve-chavez]

worked with the Accept-Profile header, which is quite strange

Two headers is always confusing, agree. We'll fix this on #2927.

[mtovmassian]

Does this behavior described by @jbstewart can be explained by something else than the schema selection in the header? I'm facing the exact same problem while following the tutorial. After creating the todos tables and applying the dedicated privileges I have a 200 OK with GET /todos but a 404 Not Found with POST /todos. However if I restart the Docker container (I'm running postgrest/postgrest:v12.0.3 by the way) the POST /todos is successful.

[wolfgangwalther]

Things like that can always be explained by Schema Cache Reloading - or a lack thereof: https://postgrest.org/en/v12/references/schema_cache.html

[mtovmassian]

@wolfgangwalther thank you for the hint. So I will take care of schema reloading, and add the NOTIFY pgrst, 'reload schema'; to my table creation procedure.