Massive data import from remote CSV file, using COPY command within a Edge Function

[leodimieri]

Hi everyone,

Im trying to achieve a massive data insertion from a csv file uploaded into Supabase Storage from the frontend.

The secuence is:

• User uploads a large CSV file to Supabase Storage (about 4MB, like 50.000 rows)
• A "job" row is inserted in "jobs" table in database, with the csv file location in Storage
• A Supabase Edge function is triggered when the job is inserted
• This function get the csv file location and creates a SignedUrl for downloading the csv
• I create a Postgresql client using "postgres" library for Deno (import { Client } from "https://deno.land/x/postgres/mod.ts") and instantiate with Admin authenticated user
• I use COPY command with signedUrl to try to make the insertion:
  COPY reports FROM '${signedUrl}' WITH (FORMAT csv, HEADER true)

I then got the error:
must be superuser or have privileges of the pg_read_server_files role to COPY from a file

When I try to grant that privileges to my user in Supabase SQL Editor:
GRANT pg_read_server_files TO my_user

I got the error:
ERROR: 42501: "pg_read_server_files" role memberships are reserved, only superusers can grant them

So.. Am I supposed to be the superuser or what?
I'm the Supabase project creator, it's my own account.. so what does this all means ? And how can I achieve what I want, if there's some other way.. please help !!

I thank you all from advance,
Regards,

Leo

PD: I've also tried:

• Using local write (with fs writeFile() method) of the CSV and got a Deno supabase function error about impossibility for accesing local
  filesystem
• Using Supabase JS insert() and upsert() methods directly with the rows array and I always get a "CPU Time exceeded" error

[GaryAustin1]

You are not Superuser with hosted Supabase. You have a lot of privileges, but not all.
https://github.com/orgs/supabase/discussions/9314#discussioncomment-4953159
You don't have the ability to do Copy.

[leodimieri]

Hi Gary, thanks for the answer.
Ok, so it looks like I can't download a file directly from database. But, in one thread of the link you send (this one) says something about the possibility of achieving the goal via extensions (pg_net or http). How are one suppose to do that? Won't it be the same problem there?

Do you image a way to solve the problem or achieve the goal of large insertion ?
Thank you,

[GaryAustin1]

Nothing is as fast as Copy as far as I know. I assume this in not an occasional thing, then PSQL would work for you.

I assume the http comment is to load the file and insert to the database from the file. You can use the http extension to load file data from storage with a URL or you can run a download REST API call to storage.

You would then need to process then data and insert it. This would all be done in SQL and there should be examples on the web. I've never done it.

https://www.crunchydata.com/blog/holy-sheet-remote-access-csv-files-from-postgres#remote-access-with-http is an example of loading a file by url.

[coreywhiting]

Hi @leodimieri

Did you find a solution to this? I'm looking to add something similar to a project. Thank you!