Permission denied when trying to import a CSV file from PGAdmin

Question

I'm using PGAdmin 1.14.3.

When I try to execute an import command:

COPY grad(country_code, postal_code, place_name, admin_name1, admin_code1, admin_name2, admin_code2, admin_name3, admin_code3, latitude, longitude, accuracy)
FROM 'C:\\Users\\denis\\Desktop\\BP2Project\\USA\\US.txt';

I get a

ERROR: could not open file "C:\Users\denis\Desktop\BP2Project\USA\US.txt" for reading: Permission denied SQL state: 42501

I did look up other similar questions and none of them solved my issue.

I logged in as user "postgres" who is the superuser. I don't see why I'm missing permissions. I'm on Windows 7.

Answer 1

The permissions article mentioned in the answer by Houari and Flimzy is a good reference material, but a direct answer (the quick fix I used) is:

• Right click the folder containing the data file(s) that permission was denied to and then click Properties.
• In the Folder's Properties window, select the Security tab.
• Click the Edit button.
• In the "Permissions for the folder" window that opened, click the Add... button.
• Type Everyone into the "Enter the object names to select" text area box.
• Click OK and the window will close.
• Verify that the default Read & Execute permissions were set to Allow via the check checkbox in the previous window.
• Click OK and the window will close.
• Click the Apply button in the Folder Properties window.

Now you can run the SQL COPY statement that needs to access those files.

• Once done, return to the Folder's Properties window.
• Click the Edit button.
• Select the Everyone entry in the "Group or user names:" field.
• Click the Remove button.
• Click OK on the remaining open windows.

The permissions have now been returned to what they were.

Answer 2

The user Postgres must have read access on the file from which you are about to copy.

Look at this article to see how to modify files' security access on Windows.

Answer 3

Ok, this is how got COPY command working,to export a table to CSV, step by step. Pls note that I am using pgAdmin 111.

1. Create the target folder you want to export a table to. E.g C:\myExports
2. Set a read/write permission on this folder following the steps below :

Right click the folder containing the data file(s) that permission was denied >to and then click Properties.

In the Folder's Properties window, select the Security tab. Click the Edit button.

In the "Permissions for the folder" window that opened, click the Add... button. Type Everyone into the "Enter the object names to select" text area box.

Click OK and the window will close. Verify that the default Read & Execute permissions were set to Allow via the >check checkbox in the previous window. Click OK and the window will close.

Click the Apply button in the Folder Properties window.

3. This is the tricky part, inside myExports folder create a blank CSV file with your desired name.E.g employee.csv

4. Then run the Copy command like this :

   copy employee to 'C:\myExports\employee.csv' delimiter ',' csv;

employee is the table name in this example..

Hope this helps.

Answer 4

If you don't want to give permissions to Everyone, you can add permissions to the account that started the service. In the Control Panel - Administrative Tools - Services, copy the account name in the 'Log On' tab. (On my system the account is called 'Network Service'.) Then share the folder with the CSV-file with this user as shown in the answer above.

Answer 5

To solve this problem you must give permission to the CSV file because that CSV file present in a COPY command are read directly by the server, but not client application. So to make this file accessible to a server we must give full read-write permission so that Postgresql user can read and write on that file.

Reference: article showing step by step procedure.

Answer 6

I just ran into this error and even after adding postgres to permissions on the file folder and the file itself, it still didn't work. So, I put the file in a public folder. On Windows this was the path: "C:\Users\Public\Documents\census.csv." It worked!