InDB config is not working

[mfatihercik]

Hi, I want to use dynamic schema feature, and I followed the documentation but couldn't make it work.
I am getting following error:

(PGRST106) The schema must be one of the following: public

docker-compose file

version: "3.9"

services:
  postgres:
    image: "postgres:13-alpine"
    command: "postgres -c listen_addresses='*'"
    ports:
      - "5432:5432" # expose the port for local dev
    environment:
      POSTGRES_DB: "app"
      POSTGRES_USER: "authenticator"
      POSTGRES_PASSWORD: "password"
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready -U authenticator -d app" ]
      interval: 10s
      timeout: 10s
      retries: 3

  postgrest:
    image: "postgrest/postgrest:latest"
    expose:
      - "8888"
    ports:
      - "8888:8888"
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      PGRST_DB_URI: "postgresql://authenticator:password@postgres:5432/app?sslmode=disable"
      #      PGRST_DB_SCHEMAS: "api"
      PGRST_DB_CONFIG: true
      PGRST_JWT_SECRET_IS_BASE64: "false"
      PGRST_SERVER_PORT: "8888"
      PGRST_LOG_LEVEL: "info"
      PGRST_DB_PRE_CONFIG: postgrest.pre_config
      PGRST_JWT_SECRET: "reallyreallyreallyreallyverysafe"
  #      PGRST_OPENAPI_MODE: "follow-privileges"

  

created api schema:

create schema IF NOT EXISTS api;

-- create a role to api schema
create role api_role nologin;
--grant usage on schema api to api_role;
GRANT USAGE ON SCHEMA api TO api_role;

-- grant privileges to the user for future view and table creation
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT,UPDATE,INSERT,DELETE ON TABLES TO api_role;

CREATE OR REPLACE VIEW api.location AS
SELECT * FROM public.location;

dynamic schema:

create schema postgrest;

grant usage on schema postgrest to authenticator;


create or replace function postgrest.pre_config()
    returns void as $$
select
    set_config('pgrst.db_schemas', 'api', true)
$$ language sql;

I copied the sql query that fetches DB config, and it is showing the schemas.

WITH
    role_setting AS (
        SELECT setdatabase as database,
               unnest(setconfig) as setting
        FROM pg_catalog.pg_db_role_setting
        WHERE setrole = CURRENT_USER::regrole::oid
          AND setdatabase IN (0, (SELECT oid FROM pg_catalog.pg_database WHERE datname = CURRENT_CATALOG))
    ),
    kv_settings AS (
        SELECT database,
               substr(setting, 1, strpos(setting, '=') - 1) as k,
               substr(setting, strpos(setting, '=') + 1) as v
        FROM role_setting
        union
            SELECT
    null as database,
    x as k,
    current_setting(x, true) as v
        FROM pg_catalog.unnest(string_to_array('pgrst.db_schemas','db_schemas2')) x
            join postgrest.pre_config() on true
    )
SELECT DISTINCT ON (key)
    replace(k, '{prefix}', '') AS key,
    v AS value
FROM kv_settings
WHERE k = ANY(string_to_array('pgrst.db_schemas','db_schemas2')) AND v IS NOT NULL
ORDER BY key, database DESC NULLS LAST

Result of the Above Query:
| key | value |
| pgrst.db_schemas | api |

When I run the following queries, it is working but in the documentation, it is mentioned that this is not the recommended way to do it.

ALTER ROLE authenticator SET pgrst.db_schemas = 'public,api';

Can someone help on how to make and in-db configuration work

[laurenceisla]

Hi! The one part I see that's different from the tutorial is:

create schema postgrest;

grant usage on schema api to authenticator;

...

It should be:

grant usage on schema postgrest to authenticator;

It should give you an error when starting the postgrest service, although it won't stop the process. Check the logs, maybe that's the case.

[mfatihercik]

Thanks @laurenceisla, for the answer. I tried running both following queries

grant usage on schema postgrest to authenticator;

and

grant usage on schema postgrest to api_role;

but it didn't work.

I will fix the description of the question. api written mistakenly.

[laurenceisla]

That's strange, it's working OK for me with all the configuration you provided. Did you reload the configuration after creating the pre_config() function?

Inside psql or in an SQL console, execute:

NOTIFY pgrst, 'reload config';
NOTIFY pgrst, 'reload schema'; -- this may not be needed

Then try the request again.

[laurenceisla]

Btw this grant usage on schema postgrest to api_role; is not needed, only the authenticator should have the access to that private schema, because it is the one that executes the postgrest.pre_config function.

[mfatihercik]

I did configurain reload too. even I removed Postgrest docker container and redeployed it again but still didn't make it work.

I changed view name from location to location_api, and I got the following error:

CREATE TABLE public.location
(
    id UUID PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
    name VARCHAR(50) NOT NULL
);

CREATE OR REPLACE VIEW api.location_api AS
SELECT * FROM public.location;

Make postgrest call:

GET http://localhost:8888/location_api
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXBpX3JvbGUifQ._aCM_LIRw6E6UJtN26qgvYx7o2h1joc0t5zRlKnc50s

{
  "code": "42P01",
  "details": null,
  "hint": null,
  "message": "relation \"public.location_api\" does not exist"
}

but when I provide schema name in the environment variable, it is working.

PGRST_DB_SCHEMAS: "api"

[laurenceisla]

I see, I'm at a loss then. I'd suggest doing it step by step. In my local environment I did this in a terminal:

mkdir ~/pgrst
cd ~/pgrst
# created the docker compose file exactly as you described here, then:
sudo docker compose up

It logs this at the end:

pgrst-postgrest-1  | 14/Sep/2023:21:43:32 +0000: {"code":"3F000","details":null,"hint":null,"message":"schema \"postgrest\" does not exist"}
pgrst-postgrest-1  | 14/Sep/2023:21:43:32 +0000: Listening for notifications on the pgrst channel
pgrst-postgrest-1  | 14/Sep/2023:21:43:32 +0000: Config reloaded
pgrst-postgrest-1  | 14/Sep/2023:21:43:32 +0000: Schema cache loaded

In another terminal:

sudo docker exec -it pgrst-postgres-1 psql -U authenticator -d app

Then I copied to that terminal all the SQL commands with some changes to the table/view and a NOTIFY at the end:

create schema IF NOT EXISTS api;

-- create a role to api schema
create role api_role nologin;
--grant usage on schema api to api_role;
GRANT USAGE ON SCHEMA api TO api_role;

-- grant privileges to the user for future view and table creation
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT,UPDATE,INSERT,DELETE ON TABLES TO api_role;

CREATE TABLE public.location_test(
id int primary key,
name text
);

INSERT INTO public.location_test(id,name) VALUES(1,'test');

CREATE OR REPLACE VIEW api.location AS
SELECT * FROM public.location_test;

create schema postgrest;

grant usage on schema postgrest to authenticator;

create or replace function postgrest.pre_config()
    returns void as $$
select
    set_config('pgrst.db_schemas', 'api', true)
$$ language sql;

NOTIFY pgrst, 'reload schema'; 

Verified that the first terminal logged this after doing the NOTIFY:

pgrst-postgrest-1  | 14/Sep/2023:21:44:54 +0000: Starting PostgREST 11.2.0...
pgrst-postgrest-1  | 14/Sep/2023:21:44:54 +0000: Attempting to connect to the database...
pgrst-postgrest-1  | 14/Sep/2023:21:44:54 +0000: Connection successful
pgrst-postgrest-1  | 14/Sep/2023:21:44:54 +0000: Config reloaded
pgrst-postgrest-1  | 14/Sep/2023:21:44:54 +0000: Schema cache loaded

Then I executed this curl command in another terminal and it worked:

curl "http://localhost:8888/location" -H "Accept-Profile: api" -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwicm9sZSI6ImFwaV9yb2xlIiwiaWF0IjoxNTE2MjM5MDIyfQ._XMuk8vEOIq-4QTmP2uOxn_-XnTnN6oJ8UlTCVN5mpU"

[{"id":1,"name":"test"}]

Check if there's any different results between my steps and yours.

[mfatihercik]

I found the issue.

I am using Mac M1. My docker pulls the image for the platform Linux/arm64/v8.

When I change the platform in the docker compose to linux.amd64, it starts working.

Looks like there is a problem related to arm64 image.

This is new image and config for docker

  postgrest:
    image: "postgrest/postgrest:v11.2.0"
    platform: "linux/amd64"