Cannot read from /tmp with PostgreSQL COPY, but able to read the same file from another directory with the exact same permissions

Question

I have a file /tmp/foo.csv that looks like this:

1,2
10,20
100,200

I have the same exact file in another directory /mydata/foo.csv. Both directories and files have the same exact permissions, owner, and were created by the same user, yet when I run

COPY foo FROM '/tmp/foo.csv' (FORMAT CSV, DELIMITER ',', HEADER TRUE);

I get this error

ERROR: could not open file "/tmp/foo.csv" for reading: No such file or directory

When I run

COPY foo FROM '/mydata/foo.csv' (FORMAT CSV, DELIMITER ',', HEADER TRUE);

I see COPY 3.

What am I missing here?

Here's the relevant file and directory information:

╭─foo@bar / ‹py27› 
╰─$ ll -d /mydata /mydata/foo.csv /tmp /tmp/foo.csv 1 ↵
drwxrwxrwt 2 root root 4.0K Sep 9 08:26 /mydata
drwxrwxrwt 17 root root 420 Sep 9 08:57 /tmp
-rw-r--r-- 1 root root 17 Sep 6 12:03 /mydata/foo.csv
-rw-r--r-- 1 root root 17 Sep 9 08:36 /tmp/foo.csv
╭─foo@bar / ‹py27› 
╰─$ diff /mydata/foo.csv /tmp/foo.csv
╭─foo@bar / ‹py27› 
╰─$ 

I'm running Arch Linux

NOTE: I've also tried this on another Linux machine running CentOS and I was unable to reproduce the problem.

• András Váczi: COPY reads from the server where the database you connect to is. Can it be that you have foo.csv on the server only in one location, but you show here the listings of your local machine?
• Phillip Cloud: I'm running the server on my local machine.
• András Váczi: And you can do a cat from both files as user postgres?
• Phillip Cloud: Yes, I'm able to do that.
• Kassandry: Do you have SELinux enabled in your Arch install?

Answer 1

On Arch, the systemd script for PostgreSQL (/usr/lib/systemd/system/postgresql.service) enables private /tmp, which means the PostgreSQL server process can't communicate with the user using /tmp.

Two possible solutions:

1. Create a new folder somewhere else and give user postgres read privileges on it: # sudo mount -t tmpfs -o users,rw tmpfs /data
2. (NOT RECOMMENDED) Set PrivateTmp option in postgresql.service to false.