API does not match config and DB state

[zoltan-spire]

Questions

Three things seem to be wrong about the setup and behaviour described below:

• why doesn't postgrest pick up the db-schemas parameter from config.conf?
• why does the API return a non-empty schema, if the db-schema points to the empty public schema?
• if postgrest is pointing to the non-empty odin schema, why doesn't it reflect the recent changes?

I have restarted, even re-created all containers containers several times, so I guess it can't be about stale schema cache.

Would greatly appreciate any help.

Setup and Behaviour

Environment

• PostgREST 11.0.1 (4197d2f)
• PostgreSQL 14.1 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit
• Running using docker compose with images, respectively:
  • postgrest/postgrest
  • postgres:14.1-alpine

Database

In my database I have two schemas:

• public, which is empty
• odin that have four tables, two of which I created recently.

Config

My postgrest.conf file contains this:

db-schemas = "odin"
db-anon-role = "web_anon"
openapi-mode = "follow-privileges"

Which is then mapped to config.conf in the postgrest container, and the container executes postgrest config.conf, as per the docker-compose.yaml config:

  ...
  server:
    image: postgrest/postgrest
    ports:
      - "3000:3000"
    command: ["postgrest", "config.conf"]
    volumes:
      - ./postgrest.conf:/config.conf:ro

I can confirm that the file is present in the container:

$ docker exec -it odin-api-server-1 cat config.conf     
db-schemas = "odin"
db-anon-role = "web_anon"
openapi-mode = "follow-privileges"

and postgrest is correctly executed with this file as parameter:

$ docker exec -it odin-api-server-1 ps -x | grep postgrest
    1 ?        Ssl    0:03 postgrest config.conf

However, postgrest does not pick up the db-schemas parameter from config.conf:

$ docker exec -it odin-api-server-1 postgrest --dump-config
db-anon-role = ""
db-channel = "pgrst"
db-channel-enabled = true
db-extra-search-path = "public"
db-max-rows = ""
db-plan-enabled = false
db-pool = 10
db-pool-acquisition-timeout = 10
db-pool-max-lifetime = 1800
db-pre-request = ""
db-prepared-statements = true
db-root-spec = ""
db-schemas = "public"
db-config = true
db-tx-end = "commit"
db-uri = "postgres://*****:*****@db:5432/odin"
db-use-legacy-gucs = true
jwt-aud = ""
jwt-role-claim-key = ".\"role\""
jwt-secret = "**************"
jwt-secret-is-base64 = false
log-level = "error"
openapi-mode = "follow-privileges"
openapi-security-active = false
openapi-server-proxy-uri = ""
raw-media-types = ""
server-host = "!4"
server-port = 3000
server-trace-header = ""
server-unix-socket = ""
server-unix-socket-mode = "660"
admin-server-port = ""

API Behaviour

When I hit the API curl -X 'GET' 'http://0.0.0.0:3000/' -H 'accept: application/openapi+json' the result is an API doc that describes an earlier state of my odin schema, containing only two resources, corresponding to the schema before I created the two new tables.

[laurenceisla]

• why doesn't postgrest pick up the db-schemas parameter from config.conf?

I think it is taking the paremeters, but it does not show them in the dump-config because config.conf was not included. Doing this should show you the correct parameters:

docker exec -it odin-api-server-1 postgrest config.conf --dump-config  

• why does the API return a non-empty schema, if the db-schema points to the empty public schema?

Related to the previous answer, that's because it is showing the odin schema.

• if postgrest is pointing to the non-empty odin schema, why doesn't it reflect the recent changes?

If it's not a schema cache problem, then maybe the permissions are not set? Verify that you granted any permission on the tables to web_anon and that the tables were created in the odin schema.

[zoltan-spire]

Thanks a lot for this! All makes sense, and of course, I forgot about the permissions. That did the trick.

[zoltan-spire]

I have one follow up question though about privileges.

What is the minimal set of permissions that is required for web_anon in order for an anonymous user, such as swagger-ui to see the schema definitions, but not to be able get the content of the tables? Maybe this topic is worth its own question in the forum, I see some related questions #2716 and #1877, which suggest there may not be a straightforward way of doing this.

[laurenceisla]

Yes, it is as the answers in those discussions mention. In summary, if you keep the openapi-mode = "follow-privileges", the OpenAPI output will show the endpoints according to the privilege of the user making the request, i.e. depending on the JWT token sent (anonymous if no token is sent). Changing the configuration to openapi-mode = "ignore-privileges" will show the endpoints regardless of the permissions that the role making the request has.