Re: permissions failure to copy csv data

On 10 October 2010 21:51, e-letter <inpost(at)gmail(dot)com> wrote:
> On 09/10/2010, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> e-letter <inpost(at)gmail(dot)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(at)gmail(dot)com>