Thread: BUG #2904: COPY FROM -> permisson denied

The following bug has been logged online:

Bug reference:      2904
Logged by:          Izsak Rudolf
Email address:      rudolf_izsak@yahoo.de
PostgreSQL version: 8.1.6
Operating system:   Fedora Core 6
Description:        COPY FROM -> permisson denied
Details:

I wanted to COPY the content of the sample data file /home/csakmost.txt to
my data table t_crdc1 using the following command:

exp03038=# COPY t_crdc1 FROM '/home/csakmost.txt' WITH DELIMITER ' ' NULL
'NULL';

But I have got the error message:

ERROR:  could not open file "/home/csakmost.txt" for reading: Hozzáférés
megtagadva

The letter two words "Hozzáférés megtagadva" stay for "access denied" in
Hungarian.

The same procedure worked fine under my system RedHat 9 and PostgreSQL 7.3
before.

I have found some material to similar issues on the web, but all of them
could be solved by changing the permissions of the data file.
I have set the permissons of the file "/home/csakmost.txt" also, so that it
can be read by any users (both of the file and the directory). Also if I try
to read the file as user postgres, it works:
[root@yifter ~]# su postgres
bash-3.1$ cat /home/csakmost.txt
4053 1 4 2007-1-17 NULL NULL

Also, it works if I use the command \copy instead of COPY.

I first thought that the owner of the SQL server is an other user than
postgres. But according to the permisson tables of the "/home" and of the
"/home/csakmost.txt", they can be read by all users:
drwxr-xr-x 5 root root 4096 jan 17 23.37 home
-rw-r--r-- 1 root root   29 jan 17 23.52 csakmost.txt

I think, it is a bug, but if it is a feature, I'm sory for that, and I would
be very grateful if You could help me in that case.

Sincerely Yours,

Rudolf Izsak
ELTE, Hungary

"Izsak Rudolf" <rudolf_izsak@yahoo.de> writes:
> Operating system:   Fedora Core 6
> Description:        COPY FROM -> permisson denied

> I wanted to COPY the content of the sample data file /home/csakmost.txt to
> my data table t_crdc1 using the following command:
> exp03038=# COPY t_crdc1 FROM '/home/csakmost.txt' WITH DELIMITER ' ' NULL
> 'NULL';

> But I have got the error message:
> ERROR:  could not open file "/home/csakmost.txt" for reading: Hozzáférés
> megtagadva

> I have set the permissons of the file "/home/csakmost.txt" also, so that it
> can be read by any users (both of the file and the directory). Also if I try
> to read the file as user postgres, it works:

You're getting burnt by SELinux restrictions: network-accessible daemons
such as postgresql are normally constrained to not be able to read or
write portions of the filesystem outside what they're "supposed" to be
touching, which for postgres is just /var/lib/pgsql/.  This is
considered a good thing since it limits the damages if someone is able
to subvert one of those server processes.

The quick answer might be to temporarily turn off SELinux (see
"setenforce") while you load the file.  A more security-aware approach
would be to modify the SELinux policy to let postgres read some
chosen directory for dropping files-to-load into.

            regards, tom lane