0

I am running the following Copy command via npgsql (.Net) from a client on the network: 

COPY work_narcra00_2017_q2.a2_aevent FROM '//BRAHMA//EnvironProcessing//dbprocessfixedasciiimport.tmp' WITH CSV

I am getting this error: 

ERROR: could not open file "//BRAHMA//EnvironProcessing//dbprocessfixedasciiimport.tmp" for reading: Permission denied
SQL state: 42501

I also tried the command from PgAdmin III and I get the same error.

The obvious answer is the file/folder permissions, but both the share and NTFS permissions have read/write permissions to Everyone.

Another post suggested adding permissions for Network Service but that makes little sense for accessing a network share because I cannot add the DBServer/NetworkService account to the client computer's share (though I tried adding the local Network Service account just in case)

Moving the file to a local folder on the server of course works, but that is not a viable long-term option right now.

Edit: I can see and edit the file just fine from within Windows using Notepad.

Improve this question
2
  • Does this help? dba.stackexchange.com/a/29773/6219
    – András Váczi
    Jul 10, 2017 at 23:37
  • I saw that and tried various combinations of slashes. It doesn't seem to matter, Postgres fixes it behind the scenes to whatever it needs. And I am already using single quotes. And just to clarify, I am not using psql.
    – Brad Mathews
    Jul 11, 2017 at 0:08

2 Answers 2

Reset to default
2

With COPY from a named file, the file is read on the server by the user who owns the database cluster (usually a service account) not on the client. So the server (both machine and account) is what needs to have access to it. If your file is readable on the client, you can use COPY...FROM STDIN in conjunction with the support functions described here.

Improve this answer
5
  • Wording slightly unclear - the access is done as the user that the database server is running as. Usually a service user.
    – Craig Ringer
    Jul 11, 2017 at 2:28
  • But isn't the user who owns the database file required to be the one it runs as? I thought "runs as" might be confused with the postgres user logged into the backend.
    – jjanes
    Jul 11, 2017 at 4:04
  • I have a few other places where that technique may be better than the more generic ADO.Net focused method I use for Excel, dBase and other file types, but for text files, the COPY command is King. 150,000 records in <600ms King which other methods cannot come close to matching. I am on the trail of how to give Network Service access to the file, but not being part of a domain is complicating that. I'll post when I finish figuring it out.
    – Brad Mathews
    Jul 12, 2017 at 16:49
  • Well, looks like I am SOL on this unless I want to run the Postgres service under a named account. I do not know the ramifications of doing that so am not prepared to at this time. I'll stick with programmaticaly copying the files to the server for the time being.
    – Brad Mathews
    Jul 14, 2017 at 18:50
  • As to using COPY...FROM STDIN I had to try that because my (very old) ADO.Net technique was throwing out of memory errors on a really large Excel spreadsheet I was trying to import. Using DataReader to avoid the memory issues and BeginBinaryImport was 2-3 times faster than previous technique and the code is a lot shorter and cleaner.
    – Brad Mathews
    Jul 14, 2017 at 18:56
-1

Postgres COPY TO command works correctly if we specify the file name as shown below. copy (SELECT ROW_TO_JSON(t) FROM (SELECT * FROM film) t) TO 'C:/Users/xyz/abc/data.xls'

Improve this answer
1
  • This doesn't answer the question: you are copying to a local file -- not a network share.
    – Colin 't Hart
    May 30, 2018 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.