Schema cache not updating after Notify

[yacine-learning]

Environment

• PostgreSQL version: 12.11
• PostgREST version: postgrest/postgrest:v9.0.0
• Operating system: Ubuntu

Description of issue

Notify seems to correctly update the db-schemas parameter on the postgrest container but the API does not seem to see this change unless I restart the container.

Steps to reproduce:

• On the db: ALTER ROLE authenticator SET pgrst.db_schemas = 'nyc, tokyo, london';
• Start postgREST container and check the config with: docker exec -it 8e6b3842eed postgrest --dump-config
• I can see that db-schemas is correctly set: db-schemas = "nyc,tokyo,london"
• Now I modify the pgrst.db_schemas and do a notify:
  ALTER ROLE authenticator SET pgrst.db_schemas = 'nyc,tokyo,london,amsterdam';
NOTIFY pgrst, 'reload schema';
• Check the postgREST config again, and I can see that db-schemas has been updated with the new value:
  db-schemas = "nyc,tokyo,london,amsterdam"
• However the following call:
  curl "http://localhost:3000/message" -H "Authorization: Bearer $TOKEN" -H "Accept-Profile: amsterdam" will give the following error:
  {"message":"The schema must be one of the following: nyc, tokyo, london"}

But curl "http://localhost:3000/message" -H "Authorization: Bearer $TOKEN" -H "Accept-Profile: tokyo" is still working fine.
The only way to fix this is by restarting the container, which I wanted to avoid by using Notify.
Am I doing something wrong?

Here is my postgrest config, right after the container starts:

db-anon-role = "anonymous"
db-channel = "pgrst"
db-channel-enabled = true
db-extra-search-path = "public"
db-max-rows = ""
db-pool = 10
db-pool-timeout = 10
db-pre-request = ""
db-prepared-statements = true
db-root-spec = ""
db-schemas = "nyc,tokyo,london"
db-config = "true"
db-tx-end = "commit"
db-uri = "postgresql://XXXX@XXXX:5432/testing?sslmode=require"
db-use-legacy-gucs = true
jwt-aud = ""
jwt-role-claim-key = ".\"role\""
jwt-secret = "XXXXXXXXX"
jwt-secret-is-base64 = false
log-level = "error"
openapi-mode = "follow-privileges"
openapi-server-proxy-uri = ""
raw-media-types = ""
server-host = "!4"
server-port = 3000
server-unix-socket = ""
server-unix-socket-mode = "660"

And here's the docker-compose:

version: '3'
services:
  server:
    image: postgrest/postgrest:v9.0.0
    ports:
      - "3000:3000"
    environment:
      PGRST_DB_URI: postgresql://XXXX:XXXX@XXXXX:5432/testing?sslmode=require
      PGRST_DB_SCHEMA: public
      PGRST_DB_ANON_ROLE: anonymous
      PGRST_JWT_SECRET: "XXXXXX"

[steve-chavez]

NOTIFY pgrst, 'reload schema';

Can you try with NOTIFY pgrst, 'reload config'?

https://postgrest.org/en/latest/configuration.html#reload-with-notify

[yacine-learning]

Good idea! Just out of curiosity what is the purpose of reload schema, why would someone want to reload the schema without reloading the config?

[steve-chavez]

reload schema reloads the schema cache, which as mentioned in the doc can execute expensive queries. reload config just queries one table and it's cheaper. The main reason of separating the reloads is because you might want to just reload the config in some cases and reloading the schema cache too would be wasteful.

Btw, you can do just: NOTIFY pgrst <- no message, which will reload both.

[yacine-learning]

Oh I see, that makes sense.
NOTIFY pgrst does not reload the config in my case, it seems to only reload the schema like reload schema does. I need to specify NOTIFY pgrst, 'reload config' for the API to see the new schema.
This is not an issue in my case because I'm fine just using NOTIFY pgrst, 'reload config' but just wanted to mention it.

[steve-chavez]

NOTIFY pgrst does not reload the config in my case

Right, sorry about that. I forgot we didn't got around to make that change(code here).

This is not an issue in my case because I'm fine just using NOTIFY pgrst.

Cool. No problem then.

[yacine-learning]

Thanks!
Fantastic tool btw! Loving it so far, easy to configure and use.