Providing postgres windows system permission for copy (windows 8)

Question

I'm looking to copy CSV files using pgadmin iii. Very new to this.

When I run a "copy" command from the query builder, I'm getting the following error:

 ERROR: could not open file 'C:\\Users\\Nick\\Documents\\CDR\\csv1.csv" for reading: Permission denied SQL state: 42501

I've found this mentioned a few other places (here here, and here for example , and the general fix is to add "postgres" to the file permissions (people also advise moving csv to the public folder, but this causes problems for other reasons).

But when I try to add postgres to the people with permissions in Windows 8, when I check the name I get an "the object postgres cannot be found" error.

If I add "Everyone" it works, but for obvious reasons I won't want to leave an important folder with "Everyone" access.

Can anyone please advise on how to give postgres permissions in Windows 8?

Thanks!

Answer 1

Recent versions of PostgreSQL for windows don't use the postgres OS account, they use a NetworkService system account instead. This is specified in the properties of the PostgreSQL service in Windows. That's presumably the reason of the object postgres cannot be found error. Changing the permissions of the file is not really needed anyway.

Recent versions of pgAdmin (1.16+) are able to feed COPY contents from the client to the server without having the server to open the file. Right-click on a table name inside the object browser and check out a menu called Import. Internally this will use the COPY FROM STDIN variant.

If that's not satisying, there's also the the option of using the psql.exe command line tool and its \copy command. This command has the same functionality and syntax as the SQL COPY command except that it streams the file from client to server instead of having the server open it itself. If you're CLI-oriented, make it your premium choice, it's easier than pgAdmin.