Solved!
The solution was to migrate from the SD library to mySD, which seems to be a SdFat wrapper for ESP32.
You can see in the file mySD.h that the FILE_WRITE mode is defined as:
#define FILE_WRITE (F_READ | F_WRITE | F_CREAT)
Which means that it allows random access to the file for writing (F_WRITE instead of FILE_APPEND).
PS: I want to remark @Juraj comment since the forum is mainly for Arduino board questions
the SD library in ESP32 boards support package is different than the standard Arduino SD library