Permission Denied error when using PostgreSQL's COPY FROM/TO command

Getting a permission denied error when using COPY? Here's why and how to fix it.

Neil Sainsbury

2 minute read

COPY FROM is commonly used to import data from a file (CSV, plain text, etc.) in to PostgreSQL. Likewise, COPY TO is used to export data from a table or a query result to a file. When you’re using these commands if you’re getting a “Permission Denied” error, here’s the likely reason why and how to fix it.

What the error means

The most common reason permission will be denied is because the Postgres server cannot access the file at the location you specified. The COPY command is executed from the viewpoint of the server, not the client. If the Postgres server is running locally on your computer, it will be running under a user account that’s different from your own. It therefore can’t read or write to files that, for example, reside under your own personal home directory. Similarly, if the server is running remotely on another computer, it won’t be able to access files for reading or writing on your own computer.

How to fix it

The simplest option is to change the file location to one the server does have access to. If Postgres is on your computer, and you’re running Windows for example, move the file you’re trying to read temporarily to ‘C:\Users\Public’ or write your file out to this directory. The Public user home directory is readable and writeable by all users on the system. If you’re on a Mac or Linux, use the ‘/tmp’ directory - again, this is accessible by all users. And if the server is running remotely, make sure that the file path is something that can be accessed by the Postgres server user.

If you’re in a hurry, the above option is the simplest to get you going.

Alternatively, you can use either psql \copy command (psql is a popular command line tool for interacting with Postgres) or the pgAdmin GUI. Using either tool, you will be able to specify a file path from the client-side perspective. Behind the scenes, both tools will invoke COPY FROM STDIN or COPY TO STDOUT, allowing you to work with files accessible to you without worrying about what the Postgres server can or can’t access.