DB_SCHEMAS does not display all schemas in Swagger

[corinabioinformatic]

I am reading and doing all searches in the official documentation, in this github project and even in google, because I struggle about best practise when creating my docker-compose reading multiple schemas (I set up in the database and in the environment variables the schemas i want to read from), as described in here: https://postgrest.org/en/v12/references/api/schemas.html#multiple-schemas
I also did the curl to get
And I am about to modify the nginx config as suggested here or here
Or should I create a different container for each schema like mentioned [here] (#1106) or here?

Environment

• PostgreSQL version: postgres/postgres:16.2
• PostgREST version: postgrest/postgrest:v12.0.2
• Operating system: Linux RedHat

Description of issue

1. I end up displaying one schema, not two, in swagger.
   

2. Curl calls to switch schemas usin Accept-Profile and Content-Profile headers works, but I cannot switch schemas in swagger:

curl -X 'GET' \
  'http://localhost:3000/table_test1' \
  -H 'accept: application/json' \
  -H 'Range-Unit: items'  \
 -H 'Accept-Profile: public' \
 -H 'Content-Profile: public' 

curl -X 'GET' \
  'http://localhost:3000/table_test1b' \
  -H 'accept: application/json' \
  -H 'Range-Unit: items'  \
 -H 'Accept-Profile: public2' \
 -H 'Content-Profile: public2' 

Works fine. so I guess main issue I got is displaying all schemas I want and enable in Swagger

when I paste in my URL : localhost:8080 I see swagger
when I paste in my URL : localhost:8080/tabletest1 I got the JSON string i want

when I paste in my URL: localhost:8080/table_test1b I get this error:

when I paste in my URL: localhost:8080/public/table_test1 I got an empty string

3. Which one is the current recommended approach- the one that would work to display all schemas I enabled in swagger and via curl calls?

=========================================================
(Steps to reproduce: Include a minimal SQL definition plus how you make the request to PostgREST and the response body)

docker-compose.yaml

services:

  ##################
  # postgresqldb #
  ##################
  #
  # PostgreSQL database
  #
  postgresqldb:
    image: postgres
    container_name: postgresqldb
    ports:
      - ${POSTGRES_PORT}
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./initdb:/docker-entrypoint-initdb.d
    environment:
      - POSTGRES_DB
      - POSTGRES_USER
      - POSTGRES_PASSWORD
      - PGPASSWORD
      - POSTGRES_INITDB_ARGS
      - DB_ANON_ROLE
      - DB_SCHEMA
    env_file:
      - ./.env
    networks:
      - postgresqldb_network
      - docknet
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $POSTGRES_USER -d $POSTGRES_DB"]
      interval: 5s
      timeout: 5s
      retries: 5

  #############
  # postgREST #
  #############
  #
  # Standalone web server that turns your PostgreSQL database directly into a RESTful API.
  # https://postgrest.org/en/v12/references/configuration.html#config-full-list
  #
  postgrest:
    container_name: postgrest
    image: postgrest/postgrest
    ports:
      - ${PGRST_PORT}
    # Available environment variables documented here:
    # https://postgrest.org/en/latest/configuration.html#environment-variables
    environment:
      # The standard connection URI format, documented at
      # https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING
      # "postgres://app_user:password@localhost/postgres" 
      - PGRST_DB_URI=postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_DB}:${POSTGRES_PORT}/${POSTGRES_DB}
      - CI_COMMIT_REF_SLUG
      - PGRST_ADDRESS
      - PGRST_DB_URI=postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_DB}:${POSTGRES_EXPOSED_PORT}/${POSTGRES_DB}
      ###############################################
      - PGRST_DB_SCHEMA=public,public2 ############ ONLY TAKES THE FIRST IN THE LIST
      ###############################################
      - PGRST_DB_ANON_ROLE=${DB_ANON_ROLE}
      # Overrides the base URL used within the OpenAPI self-documentation hosted at the API root path. changed to 3001 because port 3000 is used in Watchlist
      - PGRST_OPENAPI_SERVER_PROXY_URI=http://localhost:3000
      - PGRST_DB_POOL=100
      - PGRST_DB_POOL_ACQUISITION_TIMEOUT=10
      - PGRST_JWT_SECRET=${TODO_SECRET}
      #- PGRST_DB_ROOT_SPEC="root" # checkc if rewriteing OPENAI (https://postgrest.org/en/v12/references/api/openapi.html) !!! IN which files goes these steps from my dockerfile?
    #command: postgrest ./postgrest_proxy/postgrest.conf # An alternative way to call postgrest variables. here we will use the ENVIRONMENT VARIABLES CALL
    networks:
      - postgresqldb_network
      #- docknet
    restart: always

    healthcheck:
      test: ["CMD-SHELL", "postgrest --help"]
      interval: 5s
      timeout: 5s
      retries: 5
    depends_on:
      postgresqldb:
        condition: service_healthy

  ##############
  # swagger-ui #
  ##############
  swagger-ui:
    container_name: swagger-ui
    image: swaggerapi/swagger-ui:latest
    ports:
      - "8080:8080"
    expose:
      - "8080"
    environment:
      - API_URL=http://localhost:3000/
    restart: always
    networks:
      - postgresqldb_network
      #- docknet
    depends_on:
      postgresqldb:
        condition: service_healthy



volumes:
  pgdata:

networks:

  postgresqldb_network:

  docknet:
    name: docknet

initdb/initidb.sql

CREATE TABLE IF NOT EXISTS public.table_test1 (
    id numeric PRIMARY KEY,
    stringtest VARCHAR
);

INSERT INTO public.table_test1 (id, stringtest) VALUES
    (1, 'Cheese'),
    (2, 'Bread'),
    (3, 'Milk');


CREATE SCHEMA IF NOT EXISTS public2.table_test1b (
    id numeric PRIMARY KEY,
    stringtest VARCHAR
)

INSERT INTO public2.table_test1b (id, stringtest) VALUES
    (1, 'Butter'),
    (2, 'Ham'),
    (3, 'Toast');

-- ****************************************
-- create anonymous user for RESTAPI request - for SAMPLE request issue: 
-- *****************************************


create role anon nologin;
--create user anon;

GRANT usage ON SCHEMA public TO anon;
GRANT usage ON SCHEMA public2 TO anon;
--grant select on api.todos to web_anon;

ALTER DEFAULT PRIVILEGES IN SCHEMA public2 GRANT SELECT ON TABLES TO anon;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public2 TO anon;
GRANT SELECT ON ALL TABLES IN SCHEMA public2 TO anon;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO anon;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO anon;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO anon;

create role authenticator noinherit login password 'mysecretpassword';
grant anon to authenticator;
ALTER ROLE authenticator SET pgrst.db_schemas = "public, public2";

After running I get this nice webapp at localhost:8080

It calls the db schema public, but not public2. so that what I though i should intervene at nginx level? to specify the ports?

So at the end I could see in the same page all tables related to all the schemas I am enabled to access (switching the Schemas dropdown button to other names).

Many thanks for this great work!

[laurenceisla]

I'm converting this to a discussion, since the main issue is the same as this one: #1547 (btw, thanks for all the info/reproduction steps, they're useful for future testing).

3. Which one is the current recommended approach- the one that would work to display all schemas I enabled in swagger and via curl calls?

So at the end I could see in the same page all tables related to all the schemas I am enabled to access (switching the Schemas dropdown button to other names).

Yes, this is the ideal scenario and the drop-down button for headers should include the Accept and Content-Profile headers in every request (#1547). We're working on it in the postgrest-openapi project (but there's no ETA yet).

There's also the issue #2157, that you mentioned, which may change the need to use headers/Nginx but the design does not seem to be complete yet.

---

1. And I am about to modify the nginx config as suggested Require schema to be specified in request URL #2157 or Add securityDefinitions to openapi/swagger spec #1082
2. Or should I create a different container for each schema like mentioned [here] (Proposal for allowing multiple schemas in a single instance #1106) or how to use db-schema? postgrest-docs#160 (comment)?

So to answer your questions, both options are good workarounds to the issue. It depends on how you want to handle your schemas. The only difference I see is that if you don't want to manage more than one PostgREST instance, or your design isn't meant to work that way, then the first solution should be better: adding both headers according to the schema path in Nginx.