Thread: COPY command and required file permissions
Hi all,<br />In migrating an application from sql server to Postgres, I created a ruby script that extracts csv files fromsql 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. <br /><br />When the scriptruns the COPY commnand, I get the following error (for the genders table):<br /><br />ERROR C42501 M could notopen file "/home/ruby_process/genders.csv" for reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError)<br/><br />My question is, what are the set of permissions required to perform a postgres copy? <br /><br />I'veset the permissions on the directory so that the postgres group owns the directory and has read and execute permissions,as follows:<br /> drwxrwxr-x 3 ruby_process postgres<br /><br />I have also tried moving the files to /tmp andperforming the copy from there, unsuccesfuly.<br /><br />Any ideas would be appreciated. Thanks,<br />-Harold<br /><br/>
2008/6/12 Harold A. Giménez Ch. <harold.gimenez@gmail.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 > >
"Harold A. Giménez Ch." <harold.gimenez@gmail.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...<br /><br /><div class="gmail_quote">On Thu, Jun 12, 2008 at 10:48 AM,Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br /><blockquote class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><divclass="Ih2E3d">"Harold A. Giménez Ch." <<a href="mailto:harold.gimenez@gmail.com">harold.gimenez@gmail.com</a>>writes:<br /> > In migrating an application fromsql server to Postgres, I created a ruby<br /> > script that extracts csv files from sql server (from a windows box),then<br /> > SCPs them into a directory (/home/ruby_process) on the server running<br /> > Postgres (a Fedoracore 8) and finally runs the Postgres COPY command for<br /> > each of the csv files.<br /><br /> > When thescript runs the COPY commnand, I get the following error (for the<br /> > genders table):<br /><br /> > ERROR C42501 M could not open file "/home/ruby_process/genders.csv" for<br /> > reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError)<br /><br /></div>If you have the directory and file permissions straight, then my guess<br/> is that you have SELinux turned on and it's disallowing the postgres<br /> daemon from accessing anything "outof the ordinary". The best fix<br /> is probably to adjust the security labeling on your transfer directory.<br /> Ican't give you a cookbook recipe for that, but something along the<br /> line of<br /><br /> /usr/bin/chcon -u system_u-r object_r -t postgresql_db_t /home/ruby_process<br /><br /> might do it. I'm not sure if you'd need to fool withthe permissions on<br /> /home as well.<br /><br /> The easiest fix is to disable SELinux, but I wouldn't recommend that<br/> unless the machine is entirely isolated from the internet.<br /><br /> regards, tom lane<br/></blockquote></div><br />
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.klaver@gmail.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.klaver@gmail.com