Postgresql – Why doesn’t postgresql COPY command work with files in mapped drives

copycsvpostgresql

I have a problem copying CSV files from a network shared drive into postgreSQL, at present the fix is to copy the files to a local drive first, but it's another process in a chain which is becoming too messy.

If I run the following command

  COPY test_schema.test FROM 'X:\_Postgres DBs\Testing\test_file.csv' 
  CSV HEADER;

I get:

  ERROR:  could not open file "X:\_Postgres DBs\Testing\test_file.csv" 
  for reading: No such file or directory

ok, so postgreSQL Server doesn't understand windows mapped drives, fine, but if I run:

COPY test_schema.test FROM '\\ws7.domain.com\share-name\_Postgres
DBs\Testing\test_file2.csv' CSV HEADER;

I get:

  ERROR:  could not open file "\\ws7.domain.com\share-name\_Postgres
  DBs\Testing\test_file2.csv" for reading: Permission denied

the file test_file2.csv can be opened locally on the same server with eg notepad/notepad++/wordpad so it doesn't look like there are any file attributes such as 'file in use'

Anyone have any tips how to use COPY with shared network drives?
(Our DB UI is coded in Delphi so I'm looking for ways to do with with postgresql scripts I can call from Delphi otherwise I'll have to write some fiddly Delphi Firedac batchcopy method.)

Best Answer

COPY runs as the PostgreSQL server, which usually runs as NETWORKSERVICE on Windows. This is a local service account that does not have access to your user account's network login credentials for shared drives.

Use the psql command \copy, which reads the file from the client application, running under your user ID and with access to your shared drives, then streams it over the client/server network connection to the postgres server.

I think PgAdmin-III has a similar CSV import command that uses COPY ... FROM STDIN like psql's \copy does, too.

If you're on a Windows AD Domain and you really need PostgreSQL to have direct access to shares, you can run the PostgreSQL server under a domain account instead. But that's usually unnecessary.

Related Question