How to insert/update/delete data with a GET request

[yevon]

Hi!

I'm getting errors with a "read only transaction" message when I try to do a GET request to a defined function that performs inserts. Is there a way to avoid this? I need this to perform:

• Database side logging
• Security verifications

I tried to set the transaction level to the very first action, but it doesn't allow me to do so. Is there a way to always allow writes regardless of http method?

[steve-chavez]

Hey @yevon,

GET is enforced to run in a read-only tx, this guarantees idempotency - which is useful for things like retries on the client side.

Database side logging

How are you doing this? I believe it's best to leave that to the db logs. You can even query the logs as a table if you enable csv logging + a FDW.

Security verifications

Can you elaborate on this?

[yevon]

I was trying to implement a double jwt authentication flow in a SPA application, with short lived tokens and refresh tokens. The problem with that is that for achieving this, I have to register refresh tokens and use browser redirects in this way:

1. User performs login, and an access_token(short lived) and a refresh_token(long lived) are returned to the browser as http only cookies.
2. The cookies are setup in a way that short lived tokens are sent for every request by the browser, and the refresh token (long lived) only for the /rpc/token_refresh endpoint (which makes the browser to send the refresh token, and it registers tokens for token rotation and token invalidation).
3. The user performs get requests as normal, and when short lived token expires I do return a redirect response 302 to instruct the browser to call to the rpc/token_refresh endpoint. The problem is that the browser performs the redirect as instructed with a GET request to the rpc/token_refresh endpoint, because browser redirects are always performed with the same method as the very first request and I cannot control this.
4. At this point I must register the new provided token for being able to implement token rotation and invalidation before returning it to the client browser, so when I just perform an insert in a table it just fails with this. Here I perform additional validations, like if someone tries to use an already used refresh_token, I do invalidate all user device refresh tokens. Also I perform user device identification and registration.

So I cannot perform or repeat the request as a POST because this is a browser behaviour, which repeats redirect request as GET requests because the very first request was a GET. So I have only two options:

1. Having an option to force this transaction to read write
2. Forcing the reverse proxy to transform this token_refresh as a POST request before sending it to the PostgREST backend. But I'm having lots of problems with this, as is not easy to achieve with kubernetes ingress. I already do read cookies and transform them as Authentication headers before sending the request to the backend. I should continue this way I guess, or is there any other option?

[yevon]

At the end I implemented this at nginx proxy level, by transforming the GET request to a POST before sending it to PostgREST and got it working. Anyway, there are specific use-cases like this one that would need to specify the transaction level for every method even when it is a bad practice.

On the kubernetes side:

It seems that you can use mergeable ingresses for applying specific configs to certain paths, like the endpoint that you would want to transform to a POST. You have to define two ingress, one as the master, and then others as child that would overwrite specific configs from the master. All of them with same host but with different paths defined.

First ingress:

nginx.org/mergeable-ingress-type: master
nginx.ingress.kubernetes.io/configuration-snippet: |
  if ($cookie_access_token) {
    set $auth "Bearer $cookie_access_token";
  }
  proxy_set_header Authorization "${auth}";
hosts:
  - host: yourhost
     paths: [/]

Second ingress:

nginx.org/mergeable-ingress-type: minion
nginx.ingress.kubernetes.io/configuration-snippet: |
  if ($cookie_access_token) {
    set $auth "Bearer $cookie_access_token";
  }
  proxy_set_header Authorization "${auth}";
  proxy_method POST;   #Transform your petition to a post method before sending it to the backend
hosts:
  - host: yourhost
    paths: [/rpc/your_refresh_token_function]