4

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

h3llca7
  • 61
  • 1
  • 8

2 Answers2

6

I managed to make it work like this:

cat myfile.csv | psql -d mydb -c "COPY landing_tbl(field01, field02...) FROM STDIN CSV;"
AStopher
  • 4,207
  • 11
  • 50
  • 75
Daniel Burgess
  • 61
  • 1
  • 1
5

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Yes I know it. But how can I load from csv (via STDIN)? – h3llca7 Oct 31 '17 at 09:02
  • You can only do that if the file is on the client side. For details I'd need to know what client or API you are using to access PostgreSQL. – Laurenz Albe Oct 31 '17 at 09:16
  • I use RazorSQL to connect to remote DB – h3llca7 Oct 31 '17 at 11:09
  • Using the [import tool](http://razorsql.com/features/postgresql_import.html) should do the trick. – Laurenz Albe Oct 31 '17 at 11:20
  • That means that no any way to import csv data in function without superuser privileges? – h3llca7 Oct 31 '17 at 11:28
  • That import tool probably uses `COPY FROM STDIN`. How do you want to use `COPY FROM STDIN` in a function? I guess I don't understand your problem. What do you want to achieve? Write a program? Do a one-time import into the database by hand? – Laurenz Albe Oct 31 '17 at 11:40
  • Okay. I need to write a function which will load data from csv to multiple tables. And I also planned to call this function from simple sql script. No one-time import, because if I need only it, I will hadn't asked this question here. – h3llca7 Oct 31 '17 at 15:09
  • I was confused because you mentioned RazorSQL. You cannot access data from a client machine inside a fumction. You cam use `COPY FROM STDIN` in an SQL script by including the literal data, but that's not what you want. I think you will have to write a program that does this load, an SQL script won't do the trick. – Laurenz Albe Oct 31 '17 at 15:52
  • Thank you very much for the answers! – h3llca7 Nov 01 '17 at 06:36