Re: COPY command and required file permissions

Hi all,
In migrating an application from sql server to Postgres, I created a ruby
script that extracts csv files from sql server (from a windows box), then
SCPs them into a directory (/home/ruby_process) on the server running
Postgres (a Fedora core 8) and finally runs the Postgres COPY command for
each of the csv files.

When the script runs the COPY commnand, I get the following error (for the
genders table):

ERROR C42501 M could not open file "/home/ruby_process/genders.csv" for
reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError)

My question is, what are the set of permissions required to perform a
postgres copy?

I've set the permissions on the directory so that the postgres group owns
the directory and has read and execute permissions, as follows:
drwxrwxr-x 3 ruby_process postgres

I have also tried moving the files to /tmp and performing the copy from
there, unsuccesfuly.

Any ideas would be appreciated. Thanks,
-Harold

2008/6/12 Harold A. Giménez Ch. <harold(dot)gimenez(at)gmail(dot)com>:
> Hi all,
> In migrating an application from sql server to Postgres, I created a ruby
> script that extracts csv files from sql server (from a windows box), then
> SCPs them into a directory (/home/ruby_process) on the server running
> Postgres (a Fedora core 8) and finally runs the Postgres COPY command for
> each of the csv files.
>
> When the script runs the COPY commnand, I get the following error (for the
> genders table):
>
> ERROR C42501 M could not open file "/home/ruby_process/genders.csv" for
> reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError)
>

> My question is, what are the set of permissions required to perform a
> postgres copy?

user postgres has to have access for read.

regards
Pavel Stehule

>
> I've set the permissions on the directory so that the postgres group owns
> the directory and has read and execute permissions, as follows:
> drwxrwxr-x 3 ruby_process postgres
>
> I have also tried moving the files to /tmp and performing the copy from
> there, unsuccesfuly.
>
> Any ideas would be appreciated. Thanks,
> -Harold
>
>

"=?ISO-8859-1?Q?Harold_A._Gim=E9nez_Ch.?=" <harold(dot)gimenez(at)gmail(dot)com> writes:
> In migrating an application from sql server to Postgres, I created a ruby
> script that extracts csv files from sql server (from a windows box), then
> SCPs them into a directory (/home/ruby_process) on the server running
> Postgres (a Fedora core 8) and finally runs the Postgres COPY command for
> each of the csv files.

> When the script runs the COPY commnand, I get the following error (for the
> genders table):

> ERROR C42501 M could not open file "/home/ruby_process/genders.csv" for
> reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError)

If you have the directory and file permissions straight, then my guess
is that you have SELinux turned on and it's disallowing the postgres
daemon from accessing anything "out of the ordinary". The best fix
is probably to adjust the security labeling on your transfer directory.
I can't give you a cookbook recipe for that, but something along the
line of

/usr/bin/chcon -u system_u -r object_r -t postgresql_db_t /home/ruby_process

might do it. I'm not sure if you'd need to fool with the permissions on
/home as well.

The easiest fix is to disable SELinux, but I wouldn't recommend that
unless the machine is entirely isolated from the internet.

regards, tom lane

Thanks for the info. SELinux was the guilty party...

On Thu, Jun 12, 2008 at 10:48 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "=?ISO-8859-1?Q?Harold_A._Gim=E9nez_Ch.?=" <harold(dot)gimenez(at)gmail(dot)com>
> writes:
> > In migrating an application from sql server to Postgres, I created a ruby
> > script that extracts csv files from sql server (from a windows box), then
> > SCPs them into a directory (/home/ruby_process) on the server running
> > Postgres (a Fedora core 8) and finally runs the Postgres COPY command for
> > each of the csv files.
>
> > When the script runs the COPY commnand, I get the following error (for
> the
> > genders table):
>
> > ERROR C42501 M could not open file "/home/ruby_process/genders.csv"
> for
> > reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError)
>
> If you have the directory and file permissions straight, then my guess
> is that you have SELinux turned on and it's disallowing the postgres
> daemon from accessing anything "out of the ordinary". The best fix
> is probably to adjust the security labeling on your transfer directory.
> I can't give you a cookbook recipe for that, but something along the
> line of
>
> /usr/bin/chcon -u system_u -r object_r -t postgresql_db_t
> /home/ruby_process
>
> might do it. I'm not sure if you'd need to fool with the permissions on
> /home as well.
>
> The easiest fix is to disable SELinux, but I wouldn't recommend that
> unless the machine is entirely isolated from the internet.
>
> regards, tom lane
>

I have an script with many "\copy" commands. For example:

\copy privilegios from '/work/eclipse-workspace/Canoan
Server/database/load/privilegios.dat'
\copy privilegios_de_usuarios from '/work/eclipse-workspace/Canoan
Server/database/load/privilegios_de_usuarios.dat'
\copy classificacoes from '/work/eclipse-workspace/Canoan
Server/database/load/classificacoes.dat'
\copy tipo_de_produto from '/work/eclipse-workspace/Canoan
Server/database/load/tipos.dat'

This lines are OK. But when I remove the preceding backslash from them I get
the error that you mentioned.

Harold A. Giménez Ch. wrote:
>
> Hi all,
> In migrating an application from sql server to Postgres, I created a ruby
> script that extracts csv files from sql server (from a windows box), then
> SCPs them into a directory (/home/ruby_process) on the server running
> Postgres (a Fedora core 8) and finally runs the Postgres COPY command for
> each of the csv files.
>
> When the script runs the COPY commnand, I get the following error (for the
> genders table):
>
> ERROR C42501 M could not open file "/home/ruby_process/genders.csv"
> for
> reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError)
>
> My question is, what are the set of permissions required to perform a
> postgres copy?
>
> I've set the permissions on the directory so that the postgres group owns
> the directory and has read and execute permissions, as follows:
> drwxrwxr-x 3 ruby_process postgres
>
> I have also tried moving the files to /tmp and performing the copy from
> there, unsuccesfuly.
>
> Any ideas would be appreciated. Thanks,
> -Harold
>
>

--
View this message in context: http://old.nabble.com/COPY-command-and-required-file-permissions-tp17800484p28976565.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On 06/23/2010 02:01 PM, bruno.scovoli wrote:
>
> I have an script with many "\copy" commands. For example:
>
> \copy privilegios from '/work/eclipse-workspace/Canoan
> Server/database/load/privilegios.dat'
> \copy privilegios_de_usuarios from '/work/eclipse-workspace/Canoan
> Server/database/load/privilegios_de_usuarios.dat'
> \copy classificacoes from '/work/eclipse-workspace/Canoan
> Server/database/load/classificacoes.dat'
> \copy tipo_de_produto from '/work/eclipse-workspace/Canoan
> Server/database/load/tipos.dat'
>
> This lines are OK. But when I remove the preceding backslash from them I get
> the error that you mentioned.
>
>
> Harold A. Giménez Ch. wrote:
>>
>> Hi all,
>> In migrating an application from sql server to Postgres, I created a ruby
>> script that extracts csv files from sql server (from a windows box), then
>> SCPs them into a directory (/home/ruby_process) on the server running
>> Postgres (a Fedora core 8) and finally runs the Postgres COPY command for
>> each of the csv files.
>>
>> When the script runs the COPY commnand, I get the following error (for the
>> genders table):
>>
>> ERROR C42501 M could not open file "/home/ruby_process/genders.csv"
>> for
>> reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError)
>>
>> My question is, what are the set of permissions required to perform a
>> postgres copy?
>>
>> I've set the permissions on the directory so that the postgres group owns
>> the directory and has read and execute permissions, as follows:
>> drwxrwxr-x 3 ruby_process postgres
>>
>> I have also tried moving the files to /tmp and performing the copy from
>> there, unsuccesfuly.
>>
>> Any ideas would be appreciated. Thanks,
>> -Harold
>>
>>
>

Are you connecting to the database as a superuser to run the COPY command?

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On Friday 25 June 2010 6:07:35 pm Bruno Scovoli Santos wrote:
> * Are you connecting to the database as a superuser to run the COPY
> command? *
>
> Yes. This \copy command is in the database build script (create table
> blablabla....). So it almost (I think) must have to be a superuser.
>
> But hey, my intent was just to guide the guy (Harold A. Giménez Ch.) to
> follow my practice (to use a preceding backslash). I dont bother to have to
> add that preceding backslash in my script. ;-)
>
>

Actually my question was directed to Harold, I put my reply in the wrong place.
In any case COPY and \copy are different commands. COPY is done from the
server's perspective, \copy from the clients. Hence the permission issues will
be different. See below for more detail.

http://www.postgresql.org/docs/8.4/interactive/sql-copy.html
http://www.postgresql.org/docs/8.4/interactive/app-psql.html

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com