Multi Schema Configuration Not Working

[NickEmpetvee]

Hi guys,

I have the following setup in my postgrest.conf, and Postgrest was restarted after the edit:

...
db-schemas = "api, api2"
...

When I issue a curl in a Windows CMD shell:

curl http://localhost:14300/todos -H "Accept-Profile: api2"

The below error results:

{"code":"42501","details":null,"hint":null,"message":"permission denied for schema api"}

It's not looking in api2. Any suggestions? Thanks so much in advance.

[laurenceisla]

Hi, what version of PostgREST are you using? IIRC this is related to issue #3660, which was fixed in v12.2.3.

[NickEmpetvee]

The executable is dated July 29, 2024. Let me see if that's v12.2.3.

C:\esdev\react\EnterSight\Postgrest>dir
 Volume in drive C is Windows
 Volume Serial Number is B25B-60FC

 Directory of C:\esdev\react\EnterSight\Postgrest

07/29/2024  05:29 PM    <DIR>          .
07/29/2024  05:30 PM    <DIR>          ..
08/21/2024  05:39 PM               497 postgrest.conf
07/29/2024  05:29 PM        58,726,400 postgrest.exe

[laurenceisla]

Ah, you can check the version with postgrest.exe --version.

[NickEmpetvee]

I just upgraded to the latest version (12.2.3). It launches with thie below.

C:\esdev\react\EnterSight\Postgrest>postgrest.exe postgrest.conf
21/Aug/2024:18:30:33 -0400: Starting PostgREST 12.2.3 (519615d)...
21/Aug/2024:18:30:33 -0400: Listening on port 14300
21/Aug/2024:18:30:33 -0400: Listening for notifications on the "pgrst" channel
21/Aug/2024:18:30:33 -0400: Successfully connected to PostgreSQL 16.2, compiled by Visual C++ build 1937, 64-bit
21/Aug/2024:18:30:33 -0400: Config reloaded
21/Aug/2024:18:30:33 -0400: Schema cache queried in 0.0 milliseconds
21/Aug/2024:18:30:33 -0400: Schema cache loaded 2 Relations, 1 Relationships, 4 Functions, 0 Domain Representations, 4 Media Type Handlers, 597 Timezones

When I run curl http://localhost:14300/todos -H "Accept-Profile: api2" I still get the same error.

[laurenceisla]

Hmm... the error if the schema is not in db-schemas is different. It doesn't seem to be sending the header, maybe something to do with curl in Windows?

Does this give you a different error?:

curl http://localhost:14300/todos -H "Accept-Profile: thisschemadoesnotexist"

Something like: "The schema must be one of the following: api, api2"

[NickEmpetvee]

Yes that command returns {"code":"PGRST106","details":null,"hint":null,"message":"The schema must be one of the following: api, api2"}

[wolfgangwalther]

How is api2.todos defined?

[NickEmpetvee]

CREATE SCHEMA IF NOT EXISTS api2
    AUTHORIZATION some_role;

GRANT ALL ON SCHEMA api2 TO some_role;

GRANT USAGE ON SCHEMA api2 TO some_role2;

Additionally authenticator has access to some_role2:

GRANT some_role2 TO authenticator;

[wolfgangwalther]

Yeah, but how is the todos table/view in that schema defined?

[NickEmpetvee]

It works now. The problem is this line in the conf file:

db-pre-request = "api.check_token" - check_token is the code referenced here.

When that line is active, requests to api2.todos results in {"code":"42501","details":null,"hint":null,"message":"permission denied for schema api"}. When I comment out that line in the conf, it allows access to api normally as well as api.todos.

The problem then is that if multi-schemas are used, one can't run pre-requests. Have also noticed that the latest iteration of PostgREST will fail to restart at times. It will partially start but not load the schema, or crash after fully starting. Have never had that with previous versions of this tool.

For reference here is the todos table code:

CREATE TABLE IF NOT EXISTS api2.todos
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    done boolean NOT NULL DEFAULT false,
    task text COLLATE pg_catalog."default" NOT NULL,
    due timestamp with time zone,
    CONSTRAINT todos_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS api2.todos
    OWNER to some_role;

REVOKE ALL ON TABLE api2.todos FROM web_anon;

GRANT ALL ON TABLE api2.todos TO some_role;

GRANT ALL ON TABLE api2.todos TO some_role2;

GRANT SELECT ON TABLE api2.todos TO web_anon;

[wolfgangwalther]

The problem then is that if multi-schemas are used, one can't run pre-requests.

That doesn't make sense. You just need to make sure to put the pre-request function in a schema that all users can use and execute that function. This does not need to be one of the exposed schemas, it can be a third.

Have also noticed that the latest iteration of PostgREST will fail to restart at times. It will partially start but not load the schema, or crash after fully starting.

What kind of "crash"? What error message are you getting? What does "partially start, but not load the schema" mean, what happens exactly in that case? Can you show the log output?

If there is indeed a problem, we'd like to know about it. Can you somehow reproduce it?

[NickEmpetvee]

What kind of "crash"? What error message are you getting? What does "partially start, but not load the schema" mean, what happens exactly in that case? Can you show the log output?

If there is indeed a problem, we'd like to know about it. Can you somehow reproduce it?

This is happening on a Win11 system.

By crash, I mean it sometimes returns to the prompt per below snippet without any message. In others, it will hang on the row 23/Aug/2024:16:48:01 -0400: Listening for notifications on the "pgrst" channel for several seconds and not respond to requests, and then show the remaining message below and then exit. When either of these happens, after a few tries it will start normally.

C:\Users\jayjo>cd \esdev\react\EnterSight\Postgrest

C:\esdev\react\EnterSight\Postgrest>postgrest.exe postgrest.conf
23/Aug/2024:16:43:27 -0400: Starting PostgREST 12.2.3 (519615d)...
23/Aug/2024:16:43:27 -0400: Listening on port 3000
23/Aug/2024:16:43:27 -0400: Successfully connected to PostgreSQL 16.2, compiled by Visual C++ build 1937, 64-bit
23/Aug/2024:16:43:27 -0400: Config reloaded
23/Aug/2024:16:43:27 -0400: Schema cache queried in 0.0 milliseconds
23/Aug/2024:16:43:27 -0400: Schema cache loaded 2 Relations, 1 Relationships, 4 Functions, 0 Domain Representations, 4 Media Type Handlers, 597 Timezones

C:\esdev\react\EnterSight\Postgrest>

It might be that it is taking more time than before to exit the process and releast the port. It just never happened before - I've always been able to restart without waiting and been using PostgREST for a few years.

Please let me know if I can provide any more information.