skip to content

In PostgreSQL, how does COPY ... FROM '/path/file.csv' differ from COPY ... FROM STDIN, and what privilege does each one need?

level: juniorimportance: must knowfreq 70%

answer

  1. whose filesystem, whose OS account
  2. the server reads the file itself
  3. predefined roles for server files
  4. STDIN rides the client connection

basics

~20 s

COPY FROM a file name makes the PostgreSQL server open the file on the database host as its own OS user, needing superuser or pg_read_server_files; COPY FROM STDIN streams rows over the client connection and needs only INSERT.

solid answer

~50 s

`COPY` is PostgreSQL's bulk-transfer command: it moves rows between a table and a file, a program or the client connection in one statement. With a file name, the **server process** opens the file, so the path is resolved on the database host and read with the OS account the server runs as. Because that lets a role read any file that account can read, it is limited to superusers and members of `pg_read_server_files` (`pg_write_server_files` for `COPY TO` a file, `pg_execute_server_program` for `PROGRAM`); anyone else gets SQLSTATE `42501`. With `STDIN`, the client sends the data over its existing connection, so the file can live anywhere the client can reach and the role needs only `INSERT` (or column privileges) on the target table. For a census CSV that is the default choice unless the file already sits on the database host.

code

console · 4 lines
console
census=> COPY census_staging FROM '/srv/import/census_2026.csv' WITH (FORMAT csv, HEADER);
ERROR:  permission denied to COPY from a file
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may COPY from a file.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

go deeper

for a junior

Recall that COPY with a file name is read by the server on its own host, while COPY FROM STDIN takes the data from your client connection.

for a middle

Explain the privilege split: pg_read_server_files, pg_write_server_files and pg_execute_server_program for file and program forms, plain table privileges for STDIN and STDOUT, and SQLSTATE 42501 otherwise.

for a senior

Show judgement: keep server-file roles away from application logins, stream loads from the client, and remember that triggers and CHECK constraints still run and identity values are copied as given.

for a principal

Frame it as access control versus a network hop: who may touch the database host's filesystem is a security boundary, and bulk loading is not a reason to widen it.

## What COPY is `COPY` is a PostgreSQL command, not part of standard SQL, that moves rows between a table and an external stream in a single statement. `COPY ... FROM` **appends** rows to a table; `COPY ... TO` writes a table, or the result of a parenthesised query, out. It is fast because one command is parsed and planned once and then runs a tight loop that converts each field with its column type's input function and inserts the row. The first thing interviewers probe, though, is not speed but **where the data comes from**, because the two forms behave very differently. ## COPY with a file name: the server does the reading ```sql COPY census_staging FROM '/srv/import/census_2026.csv' WITH (FORMAT csv, HEADER); ``` Here the **PostgreSQL server process** serving your session opens `/srv/import/census_2026.csv` itself. That has three consequences: - The path is resolved **on the database host**, not on the machine where you typed the command. If the CSV is on your laptop and the server is elsewhere, the server simply cannot find it. - The file is opened with the **operating-system account the server runs as**, so that account needs read permission on it. - A relative path is resolved against the server process's working directory, normally the cluster's data directory; `COPY TO` a file insists on an absolute path. Because the server reads the file, a role allowed to do this can read **any file the server's account can read**: configuration files, key files, anything on the host. PostgreSQL therefore limits the file and program forms to superusers and to members of three predefined roles. ## The predefined roles | Form | Needs superuser or | Error otherwise | |---|---|---| | `COPY ... FROM 'file'` | `pg_read_server_files` | `permission denied to COPY from a file` | | `COPY ... TO 'file'` | `pg_write_server_files` | `permission denied to COPY to a file` | | `COPY ... FROM/TO PROGRAM 'cmd'` | `pg_execute_server_program` | `permission denied to COPY to or from an external program` | | `COPY ... FROM STDIN` / `TO STDOUT` | nothing beyond table privileges | none | All three errors carry SQLSTATE `42501` (`insufficient_privilege`) and a hint that anyone can copy from `STDIN` or to `STDOUT`. The manual warns that these roles **bypass database-level permission checks** on file access and could be used to gain superuser-level access, so they belong to trusted administrative roles, never to an application's login role. Watch the near-miss name: `pg_read_all_data` grants `SELECT` on every table, and nothing on the server's filesystem. ## COPY FROM STDIN: the client sends the data ```sql COPY census_staging FROM STDIN WITH (FORMAT csv, HEADER); ``` With `STDIN`, the server switches the connection into the COPY sub-protocol and waits for the client to send rows; with `STDOUT` it streams rows back. The file lives wherever the **client** can read it, and the only privilege checked is the ordinary one: `INSERT` on the target table (column privileges on the listed columns are enough) for `COPY FROM`, `SELECT` for `COPY TO`. Client libraries expose this directly, for example libpq's `PQputCopyData` and `PQputCopyEnd`, and most language drivers offer an equivalent copy API. psql's `\copy` meta-command wraps the same mechanism; its details belong to the psql topic. ## What COPY FROM does to the table either way Both forms run the same server-side insert path, so these hold wherever the bytes come from: 1. **Triggers fire and `CHECK` constraints are checked**; rewrite **rules are not invoked**. 2. Values supplied for an **identity column** are written as given, like `INSERT ... OVERRIDING SYSTEM VALUE`, so the column's sequence does not advance and needs resetting after the load. 3. `COPY FROM` is **not supported on a table with row-level security** enabled; the manual says to use `INSERT` there. 4. The target table is locked in `ROW EXCLUSIVE` mode, the same as `INSERT`, so readers are not blocked. 5. Progress is visible in `pg_stat_progress_copy`, whose `type` column reads `FILE` for a server file, `PROGRAM` for a program and `PIPE` for `STDIN` and `STDOUT`. ## Choosing between them For a census CSV produced by an export job, the practical rule is short: - If the file is **already on the database host** and an administrator runs the load, a server-side `COPY FROM 'file'` saves one network transfer. - In every other case (an application, a CI job, a managed server whose filesystem you cannot reach) use **`COPY FROM STDIN`** from the client, which needs no special role. Granting `pg_read_server_files` to an application role to save that transfer trades a performance detail for a privilege-escalation path, and is the answer interviewers hope you will not give.

  • Where does a relative file path in a PostgreSQL COPY FROM resolve, and can COPY TO use one?
    A relative path in `COPY FROM` is resolved against the server process's working directory, normally the cluster's data directory, never the client's current directory. `COPY TO` a file refuses relative paths and requires an absolute one. The manual recommends always writing absolute paths, because a relative one silently depends on where the server was started.
  • Why not grant pg_read_server_files to the application role that runs the nightly census load?
    Because that role could then read any file the server's OS account can read, including configuration and key files. The manual warns these roles bypass database permission checks and can lead to superuser-level access. An application should stream with `COPY FROM STDIN`, which needs only `INSERT` on the target table.
  • What does PostgreSQL's COPY ... FROM PROGRAM do, and what is the risk?
    The server runs the given shell command as its own OS account and reads the command's standard output as COPY input; `COPY TO PROGRAM` writes to the command's standard input. It requires `pg_execute_server_program` or superuser. The shell interprets the string, so splicing user-supplied text into it is a command-injection hole; use a fixed command.

  • COPY FROM '/home/me/census.csv' reads the file from the machine where I typed it.
  • Any role with INSERT on the table may COPY from a server-side file.
  • pg_read_all_data is the role that allows COPY from a file on the server.
  • COPY FROM STDIN needs superuser because it streams data past permission checks.
  • COPY FROM skips triggers and CHECK constraints to load faster.