Обсуждение: 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/ > 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: > 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