Upgrading postgREST from 10 to 11, JWT 'exp' no longer working, 'intarray' extension not functioning

[StephenWall]

I am attempting to upgrade postgREST on a FreeBSD from version 10.1.2 to 11.0.1 (This is the latest available in the FreeBSD ports tree).

JWTs containing an 'exp' were previously recognized as expired when the 'exp' timestamp was passed under 10, but are being allowed to access resources in 11. If I reinstall the 10.1.2 package and restart postgREST, they start working again - the only change is the version of postgREST. I did update the postgrest/default.conf file when changing the version, and I've verified that the 'exp' is indeed present using https://jwt.io/.

Has there been some change in JWT 'exp' processing that needs to be accounted for?

The other problem is that operators in the postgresql intarray extension (https://www.postgresql.org/docs/current/intarray.html) are no longer recognized. The extension is creates with schema 'public', and db-extra-search-path = "public" appears in the 'postgrest/default.conf' config file. Again, postgREST 10 works, 11 does not.

Any hint, suggestions, or debugging help are greatly appreciated.

[laurenceisla]

That's strange, the exp works for me in 11.0.1. Do you have an example with a secret (different from what you use in production) and a JWT that can reproduce this?

For intarray, which operators don't work anymore? Do you have an example (e.g. using curl) and the response to verify this?

[StephenWall]

Token:
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOiAxNzAyODUzMTMyLCAiaWF0IjogMTcwMjg1MzA5MiwgImlzcyI6ICJzd2FsbC10ZXN0LnJlZGNvbS5jb20iLCAianRpIjogIjNnbU9jbExCMDdKZ3Y0dFpqQmhUb2tqaXZWOWgxUmJnS0hOc05jeTVEQlk9IiwgIm5iZiI6IDE3MDI4NTMwNTIsICJyb2xlIjogInJlc3RfdXNlcl9tYXgiLCAicmVkY29tX3NpZ21hX3Jlc3RfcnhwIjogMTcwMjkzOTUwMiwgInJlZGNvbV9zaWdtYV9yZXN0X3ZzX2lkIjogMCwgInJlZGNvbV9zaWdtYV90b2tlbl90eXBlIjogInJlc3QiLCAicmVkY29tX3NpZ21hX3Jlc3RfbW9kZV9pZCI6IDAsICJyZWRjb21fc2lnbWFfcmVzdF91c2VyX2lkIjogMTQxfQ.ayF-scLVbONSeYnkWcN-JOrAEhIu7zYWoBSDajoG3_Q

jwt.io decode:

{
  "exp": 1702853132,  <= 17:45:32
  "iat": 1702853092,
  "iss": "swall-test.redcom.com",
  "jti": "3gmOclLB07Jgv4tZjBhTokjivV9h1RbgKHNsNcy5DBY=",
  "nbf": 1702853052,
  "role": "rest_user_max",
  "redcom_sigma_rest_rxp": 1702939502,
  "redcom_sigma_rest_vs_id": 0,
  "redcom_sigma_token_type": "rest",
  "redcom_sigma_rest_mode_id": 0,
  "redcom_sigma_rest_user_id": 141
}

Key (randomly generated):
cXowtjpC2lI0G3A4VTyrQFv+rchJWQzaKtFVWVMP

Note the current date is after the date in the "exp" field above.

$ date +%s
1702853144  <= 17:45:44
$ curl -k -H 'Accept: application/json' -H "Authorization: Bearer ${TOKEN}" https://swall-test.redcom.com/api/users?username=eq.test
[{"id":141,"comments":......,"force_reset_password":0}]

I also checked that the date on the machine running postgrest and on the machine running the queries were within a second of each other.

Also note that I've had several attempts at reproducing the error that appeared to work correctly, though all the automation test runs I've looked at always fail with postgrest 11 and always succeed with postgrest 10.

I made several attempts, all of which provided the requested data, up to 1702853309 (17:48:29). Then, several minutes later at timestamp 1702853757 (17:55:57), I got the correct expired response:

{"code":"PGRST301","details":null,"hint":null,"message":"JWT expired"}

[laurenceisla]

Thanks for the extra info! Now I see that it may be related to this change introduced in 11.0.1 #2772. It mentions that a skew is used to validate the JWT expiration, so the tokens now expire 30 secs after the exp time. It's based on the Google recommended skew of 30s. I believe your tests should pass if you generate them 30 seconds before the expected exp time.

[StephenWall]

The intarray operation in question is "-", remove values present in one array from another array. It works on the test machine interactively:

ace_db=# select array[1, 2, 3, 4, 5] - array[2, 5, 7];
?column?
----------
{1,3,4}
(1 row)

But a function used by the REST api that was doing this returned a 404 error:

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Details: null
Message: operator does not exist: integer[] - integer[]

Here are the contents of the postgrest/default.conf file in use with both postgrest10 and 11:

db-anon-role = "anon"
db-channel = "pgrst"
db-channel-enabled = true
db-config = true
db-extra-search-path = "public"
db-pool = 10
db-pre-request = "_api.pre_request"
db-prepared-statements = true
db-schemas = "api"
db-tx-end = "commit"
db-uri = "postgresql://postgrest@localhost:5432/ace_db"
db-use-legacy-gucs = false
jwt-role-claim-key = ".role"
jwt-secret = "cXowtjpC2lI0G3A4VTyrQFv+rchJWQzaKtFVWVMP"
jwt-secret-is-base64 = false
log-level = "error"
openapi-mode = "disabled"
openapi-server-proxy-uri = ""
server-host = "127.0.0.1"
server-port = 3000

[laurenceisla]

I couldn't reproduce this. Verify that the extension is created in the public schema, e.g. by checking the subtract operation that you mention:

select routine_schema, routine_name from information_schema.routines
where routine_name like 'intset_subtract';

Also check that an ENV or In-Database configuration is not overriding the db-extra-search-path. To verify the configuration that's being applied use:

postgrest postgrest/default.conf --dump-config