Search path not including "public" schema?

[mdr1384]

We have a 10.1.2 installation (FreeBSD) that is working correctly, and a 12.2.9 installation (Ubuntu) that is failing due to the search path not including "public" schema. Both installations are using identical config files that specify db-extra-search-path = "public". When I turn on statement logging in the failing installation, I see that there are several set_config() calls at the begining of the query and the very first one sets search_path to "api", "public", but the second one overrides that and sets search_path to api (no quotes).

LOG:  execute 0: BEGIN ISOLATION LEVEL READ COMMITTED READ WRITE
LOG:  execute 1: select set_config('search_path', $1, true), set_config($2, $3, true), set_config('role', $4, true), set_config('request.jwt.claims', $5, true), set_config('request.method', $6, true), set_config('request.path', $7, true), set_config('request.headers', $8, true), set_config('request.cookies', $9, true)
DETAIL:  parameters: $1 = '"api", "public"', $2 = 'search_path', $3 = 'api', $4 = 'anon', $5 = '{"role":"anon"}', $6 = 'POST', $7 = '/rpc/login', $8 = '...}', $9 = '{}'

I checked the CHANELOG.md to see if there was a regression with db-extra-search-path that was fixed after 12.2.9 but did not see anything... can anyone help with this issue?

[steve-chavez]

@mdr1384 Could you try latest v16 version instead of v12.2.9? I can't remember a change on db-extra-search-path off the top of my head.

[mdr1384]

OK nvm the below stuff, I had not properly activated the new config file so the jwt-secret was wrong. The original problem persists as described in version 16.4. Specifically I cannot use the intarray operators because "public" is not in the search path even though it is set in the config file as db-extra-search-path = "public". This works in version 10. BTW the db-schemas config is set to "api" which is why we need the db-extra-search-path.

I tried using 16.4, but I am having a major problem trying to get the JWT to work as it did before, probably some security improvements are preventing this from working? The JWT I am using has {"alg": "HS256", "typ": "JWT"} - Maybe HS256 is no longer acceptable? There is no log message in the postgresql journal and the error response is 401 Unauthorized with body:

[wolfgangwalther]

v11 introduced "Configurable role settings" (#2561), see: https://docs.postgrest.org/en/v16/references/transactions.html#impersonated-role-settings.

This means that a search_path that is set on the role that is present in the JWT, will be switched into. In your case, this will have the search path set to just api, but not public.

I'd suggest to remove that.

[mdr1384]

Confirmed - this fixed the problem. Thanks