Thread: permissions failure to copy csv data
Readers, The following command was entered from normal user account where csv files are: COPY newdatabasenametable FROM '/path/to/file.csv' WITH CSV; ERROR: could not open file "/path/to/file.csv" for reading: Permission denied Superuser X created by linux root account but unable to read csv files even though permissions are read-execute for others (tried to change to read-write-execute, no success): su root su postgres createuser X createdb -U X newdatabasename When changing to postgres account: pwd: /path/to/ su root su postgres psql newdatabasename could not change directory to "/path/to Welcome to psql 8.2.13, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit How do I configure the permissions correctly? Yours, postgresql@conference.jabber.org postgresql8213 mandriva2008
e-letter <inpost@gmail.com> writes: > The following command was entered from normal user account where csv files are: > COPY newdatabasenametable FROM '/path/to/file.csv' WITH CSV; > ERROR: could not open file "/path/to/file.csv" for reading: Permission denied The postgres account either doesn't have permission to read that file, or doesn't have permission to search one of the directories in the path. > su root > su postgres > psql newdatabasename > could not change directory to "/path/to This would work better with "su -l postgres", so that you don't end up with a situation where psql is being started in a directory it has no permissions for. regards, tom lane
On 09/10/2010, Tom Lane <tgl@sss.pgh.pa.us> wrote: > e-letter <inpost@gmail.com> writes: >> The following command was entered from normal user account where csv files >> are: > >> COPY newdatabasenametable FROM '/path/to/file.csv' WITH CSV; >> ERROR: could not open file "/path/to/file.csv" for reading: Permission >> denied > > The postgres account either doesn't have permission to read that file, > or doesn't have permission to search one of the directories in the path. > Using the command: ls -ao file.csv showed: -rwxr-xr-x This suggests to me that 'others' have permission to search the directory for the file. >> su root >> su postgres >> psql newdatabasename >> could not change directory to "/path/to > > This would work better with "su -l postgres", so that you don't end > up with a situation where psql is being started in a directory it > has no permissions for. > Please advise documentation that explains the '-l' command. I tried 'man su' but no terminal response.
On 10 October 2010 21:51, e-letter <inpost@gmail.com> wrote: > On 09/10/2010, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> e-letter <inpost@gmail.com> writes: >>> The following command was entered from normal user account where csv files >>> are: >> >>> COPY newdatabasenametable FROM '/path/to/file.csv' WITH CSV; >>> ERROR: could not open file "/path/to/file.csv" for reading: Permission >>> denied >> >> The postgres account either doesn't have permission to read that file, >> or doesn't have permission to search one of the directories in the path. >> > Using the command: > > ls -ao file.csv > > showed: > > -rwxr-xr-x > > This suggests to me that 'others' have permission to search the > directory for the file. No. That means that if other users have access to the directory, then they will be able to read (and execute) the file. Try running ls -ld on all the directories leading up to that directory. e.g. if the file is in /path/to/dir/file.csv then run: ls -ld / ls -ld /path ls -ld /path/to ls -ld /path/to/dir All of them should have at least an x bit for "other". >>> su root >>> su postgres >>> psql newdatabasename >>> could not change directory to "/path/to >> >> This would work better with "su -l postgres", so that you don't end >> up with a situation where psql is being started in a directory it >> has no permissions for. >> > Please advise documentation that explains the '-l' command. I tried > 'man su' but no terminal response. -, -l, --login Provide an environment similar to what the user would expect had the user logged in directly. When - is used, it must be specified as the last su option. The other forms (-l and --login) do not have this restriction. Basically, if you are in /home/username and you type "su" ("root" is redundant) you will be in /home/username still. When you then run "su postgres" you will still be in /home/username and the postgres user may not have access to that directory. This may cause error messages to be displayed when you run commands like psql. Using "su - postgres" or "su -l postgres" makes sure you start in the postgres user's home directory and also sets various environment variables like HOME and USERNAME and runs the postgres user's login scripts. This is generally what you want to do. -- Michael Wood <esiotrot@gmail.com>
On 10/10/2010, Michael Wood <esiotrot@gmail.com> wrote: > On 10 October 2010 21:51, e-letter <inpost@gmail.com> wrote: >> On 09/10/2010, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> e-letter <inpost@gmail.com> writes: >>>> The following command was entered from normal user account where csv >>>> files >>>> are: >>> >>>> COPY newdatabasenametable FROM '/path/to/file.csv' WITH CSV; >>>> ERROR: could not open file "/path/to/file.csv" for reading: Permission >>>> denied >>> >>> The postgres account either doesn't have permission to read that file, >>> or doesn't have permission to search one of the directories in the path. >>> >> Using the command: >> >> ls -ao file.csv >> >> showed: >> >> -rwxr-xr-x >> >> This suggests to me that 'others' have permission to search the >> directory for the file. > > No. That means that if other users have access to the directory, then > they will be able to read (and execute) the file. > > Try running ls -ld on all the directories leading up to that directory. > > e.g. if the file is in /path/to/dir/file.csv then run: > > ls -ld / > ls -ld /path > ls -ld /path/to > ls -ld /path/to/dir > > All of them should have at least an x bit for "other". > I used the command: chmod 755 /path/to This solved the problem, thank you.