PostgreSQL

PostgreSQL COPY Command Permission Denied on Server

A permission denied error on COPY almost always comes down to confusing server-side COPY (which reads/writes files on the database server's own filesystem, requiring superuser or specific role privileges) with client-side \copy in psql, which reads/writes files on your local machine using your own regular file permissions instead.

The Problem

Running a COPY command to import or export a file fails with:

ERROR:  could not open file "/path/to/file.csv" for reading: Permission denied
ERROR:  must be superuser or a member of the pg_read_server_files role to COPY from a file

The file clearly exists and is readable when checked directly on the machine you're working from, which makes the permission error confusing at first.

Why It Happens

PostgreSQL has two genuinely different mechanisms that look similar but behave very differently regarding file access:

  • Server-side COPY (the plain SQL command, run via any client) reads or writes files using the PostgreSQL server process's own filesystem access and permissions — the file path is resolved on the database server, not on your local machine, and by default this requires superuser privileges (or membership in pg_read_server_files/pg_write_server_files roles on newer Postgres versions) specifically because it could otherwise be used to read or write arbitrary files on the server
  • Client-side \copy (a psql-specific meta-command, not part of SQL itself) reads or writes files on the machine running psql, using your own regular user file permissions, and doesn't require any special database-level privilege
  • Confusing these two is extremely common, especially since the syntax looks nearly identical — the only difference is the backslash prefix and which machine's filesystem is actually involved

The Fix

In most cases — importing or exporting a file that lives on your own local machine, not on the database server itself — the correct tool is \copy, not COPY:

\copy orders TO '/local/path/on/your/machine/orders.csv' WITH CSV HEADER;

Note there's no semicolon required issue here specific to backslash commands, and critically, this reads/writes relative to wherever psql itself is running, using your own OS-level file permissions — no special Postgres privilege is needed for this.

If you specifically do need server-side COPY (for example, as part of an automated process running directly on the database server with a file already present there), confirm the connecting role has the necessary privilege:

GRANT pg_read_server_files TO your_role;
-- or, for writing:
GRANT pg_write_server_files TO your_role;

These roles are available from PostgreSQL 11 onward as a more granular alternative to requiring full superuser privileges just for file access — using them instead of granting superuser is the more appropriately scoped fix if server-side file access is genuinely needed for a specific limited purpose.

Also confirm the PostgreSQL server process's own operating system user (commonly postgres) has filesystem permission to read or write the specific file path, separate from any database-level role privilege — having the pg_read_server_files role doesn't help if the underlying OS-level file permissions on the actual file or directory don't allow the postgres system user to access it:

ls -la /path/to/file.csv
# confirm the postgres OS user (not your own OS user) has read access

Still Not Working?

If you're working with a managed/cloud PostgreSQL service (like a hosted database platform), server-side COPY from an arbitrary filesystem path often isn't available at all, regardless of role privileges, since you don't have direct filesystem access to the underlying server in a managed environment. In that case, \copy from the client side remains the correct approach for local files, and for very large datasets that don't fit comfortably through a client connection, the managed provider's own bulk import tooling (often built around streaming data through their API or a dedicated import feature) is typically the intended alternative rather than trying to force traditional server-side file access to work.

You might also like