COPY command error in psql.
Hi,
I'm trying to import tables in psql but am getting a permission denied
error. I'm using the '\i file' command as I intend to import 100+ tables
and want to set this up to run overnight (almost 1 GB of data is going in).
My test for this is not working. I have a 'batch' file the contents of
which are (at the moment) only:
COPY ab FROM '/home/ms/db/csv/AB.csv'
Table 'ab' exists as does the csv file and I am logged in as 'postgres'.
I am getting this error when I run "\i batchfile":
psql:batchfile:1: Error: Copy command, running in backend with effective
uid 26, could not open file '/home/ms/db/csv/AB.csv' for reading. Errorno =
permission denied (13).
Now I understand why this happened the first time I ran the command as only
user 'ms' had any kind of permission for AB.csv. But the same error
happened after I changed permissions to allow read, write and execute
access for user, group and others. Finally I changed AS.csv's ownership and
group to user 'postgres' and got the same error.
Anyone know how to resolve this?
Thanks,
Matthew
PS. I don't want to use '\copy' as I can't use this with a batch file to
import lots of tables.
Matthew Stanfield <matthew@propertyknowledge.com> writes:
COPY ab FROM '/home/ms/db/csv/AB.csv'
psql:batchfile:1: Error: Copy command, running in backend with effective
uid 26, could not open file '/home/ms/db/csv/AB.csv' for reading. Errorno =
permission denied (13).
Now I understand why this happened the first time I ran the command as only
user 'ms' had any kind of permission for AB.csv.
What about permissions on the containing directories? That path
requires search (x) permission on each directory traversed to reach the
file...
regards, tom lane
On Tue, Apr 09, 2002 at 02:39:22PM +0100, Matthew Stanfield wrote:
Hi,
I'm trying to import tables in psql but am getting a permission denied
error. I'm using the '\i file' command as I intend to import 100+ tables
and want to set this up to run overnight (almost 1 GB of data is going in).My test for this is not working. I have a 'batch' file the contents of
which are (at the moment) only:COPY ab FROM '/home/ms/db/csv/AB.csv'
Table 'ab' exists as does the csv file and I am logged in as 'postgres'.
You want \copy. COPY asks the backend to do it, which may not have
permission, as you noticed.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Ignorance continues to thrive when intelligent people choose to do
nothing. Speaking out against censorship and ignorance is the imperative
of all intelligent people.
My guess is you've only changed those permissions on the directory &
file in question - not all the directories leading up to it:
chmod a+r /home/ms/db/csv/AB.csv
chmod a+x /home/ms/db/csv /home/ms/db /home/ms
Also, since this not really a PostgreSQL problem, you can test this
without PostgreSQL in the loop:
su - postgres
ls -l /home/ms/db/csv/AB.csv
Lee.
Matthew Stanfield writes:
Show quoted text
I'm trying to import tables in psql but am getting a permission denied
error. I'm using the '\i file' command as I intend to import 100+ tables
and want to set this up to run overnight (almost 1 GB of data is going in).My test for this is not working. I have a 'batch' file the contents of
which are (at the moment) only:COPY ab FROM '/home/ms/db/csv/AB.csv'
Table 'ab' exists as does the csv file and I am logged in as 'postgres'.
I am getting this error when I run "\i batchfile":
psql:batchfile:1: Error: Copy command, running in backend with effective
uid 26, could not open file '/home/ms/db/csv/AB.csv' for reading. Errorno =
permission denied (13).
Now I understand why this happened the first time I ran the command as only
user 'ms' had any kind of permission for AB.csv.What about permissions on the containing directories? That path
requires search (x) permission on each directory traversed to reach the
file...
That's right it was a directory permission problem.
Many thanks for your replies,
Matthew