Postgresql function load csv WITHOUT SUPERUSER

Question

Does any way to load csv into postgresql table via COPY WITHOUT SUPERUSER privileges exist?

Answer 1

I managed to make it work like this:

cat myfile.csv | psql -d mydb -c "COPY landing_tbl(field01, field02...) FROM STDIN CSV;"

Answer 2

You can either use

COPY tabname FROM 'filename'

which requires the superuser privilege or

COPY tabname FROM STDIN

which doesn't require the superuser privilege.

PostgreSQL requires the superuser privilege to access files on the database server for security reasons.

One workaround is to write a database function with SECURITY DEFINER owned by a superuser that does the COPY for you. Then everybody with EXECUTE rights on the function (by default everybody, so change that) can perform the operation.