ApsaraDB RDS for PostgreSQL does not grant superuser privileges, which are required by the server-side COPY command. Use the \copy meta-command in psql instead — it runs on the client side and requires no superuser privileges.
Why this error occurs
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.The standard COPY command reads and writes files directly on the database server and requires superuser privileges to access those server-side file paths. Because ApsaraDB RDS for PostgreSQL does not support superuser access, this command always fails when referencing a file path.
Fix the error
Use psql's \copy meta-command instead. Unlike COPY, \copy routes data through the client (your local machine) rather than reading files on the server. File access and privileges are those of the local user — no superuser privileges required.
Run the following command from inside the psql shell:
\copy <table_name> (<column1>, <column2>, ...) FROM '<path/to/file.csv>' WITH CSVExample using the table from the original error:
\copy mp3 (NAME,city,nation,lat,lng,url,mediatype,type) FROM '/home/alex/tmp/pos.csv' WITH CSVIf the command runs successfully, psql prints the number of rows imported:
COPY 42Alternative: pipe a CSV file through psql
If you are scripting the import or cannot use the interactive psql shell, pipe the file through stdin:
cat <table_name>.csv | psql -h <host> -p <port> -U <username> -c "COPY <table_name> FROM STDIN"Replace the following placeholders with actual values:
| Placeholder | Description |
|---|---|
<table_name> | Name of the target table in the database |
<host> | Hostname or IP address of your RDS instance |
<port> | Port number of your RDS instance |
<username> | Username used to log on to the RDS instance |
For large datasets, this approach passes all data through the client-server connection, which may be slower than a direct server-side COPY. For large imports, consider using pg_dump and pg_restore.
What's next
To migrate data from a self-managed PostgreSQL instance to ApsaraDB RDS for PostgreSQL, see Use pg_dump and pg_restore to migrate data from a self-managed PostgreSQL instance to an ApsaraDB RDS for PostgreSQL instance.