Re: [SQL] COPY command and required file permissions
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.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY command and required file permissions
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): ERRORC42501 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.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY command and required file permissions
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): > > ERRORC42501 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY command and required file permissions
Thanks for the info. SELinux was the guilty party... On Thu, Jun 12, 2008 at 10:48 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > "=?ISO-8859-1?Q?Harold_A._Gim=E9nez_Ch.?=" <[EMAIL PROTECTED]> > 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): > > > ERRORC42501 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 >
Re: [SQL] COPY command and required file permissions
"=?ISO-8859-1?Q?Harold_A._Gim=E9nez_Ch.?=" <[EMAIL PROTECTED]> 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): > ERRORC42501 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY command and required file permissions
2008/6/12 Harold A. Giménez Ch. <[EMAIL PROTECTED]>: > 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): > > ERRORC42501 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 > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
