Thread: COPY permissions problem - Windows XP
<p align="LEFT"><span lang="en-us"><font face="Arial" size="2">I</font></span><span lang="en-us"></span><span lang="en-us"><fontface="Arial" size="2">’</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2">mnew to postgresql and I</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2">’</font></span><spanlang="en-us"></span><span lang="en-us"><font face="Arial" size="2">m trying to test the copycommand with a csv file. I keep getting an error which states that I don</font></span><span lang="en-us"></span><spanlang="en-us"><font face="Arial" size="2">’</font></span><span lang="en-us"></span><span lang="en-us"><fontface="Arial" size="2">t have read permissions.</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"size="2">My database is owned by postgres, and I</font></span><span lang="en-us"></span><span lang="en-us"><fontface="Arial" size="2">’</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2">msigned in as postgres. The table that I</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial"size="2">’</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2">m placing thedata into has a different owner. </font></span><span lang="en-us"></span><span lang="en-us"> <font face="Arial" size="2">I</font></span><spanlang="en-us"></span><span lang="en-us"><font face="Arial" size="2">’</font></span><span lang="en-us"></span><spanlang="en-us"><font face="Arial" size="2">ve tried changing privileges in windows to allow all accessto everyone on this machine access. I</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial"size="2">’</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2">ve even sharedthe machine with external users. I</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2">’</font></span><spanlang="en-us"></span><span lang="en-us"><font face="Arial" size="2">ve move the file to differentdirectorys and created a tablespace</font></span><span lang="en-us"></span><span lang="en-us"> <font face="Arial"size="2">with my new table in it,</font></span><span lang="en-us"></span><span lang="en-us"> <font face="Arial"size="2">in</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2">to</font></span><spanlang="en-us"></span><span lang="en-us"><font face="Arial" size="2"> the postgres data directory</font></span><spanlang="en-us"></span><span lang="en-us"><font face="Arial" size="2">.</font></span><p align="LEFT"><spanlang="en-us"><font face="Arial" size="2">I see this type of error on many boards, but they tend to be witha flavor of unix</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2">. </font></span><spanlang="en-us"></span><span lang="en-us"> <font face="Arial" size="2">I</font></span><span lang="en-us"></span><spanlang="en-us"><font face="Arial" size="2">’</font></span><span lang="en-us"></span><span lang="en-us"><fontface="Arial" size="2">m really not getting the premise behind</font></span><span lang="en-us"></span><spanlang="en-us"> <font face="Arial" size="2">what is going on and why.</font></span><span lang="en-us"></span><spanlang="en-us"> <font face="Arial" size="2"></font></span><span lang="en-us"></span><span lang="en-us"> <fontface="Arial" size="2"> C</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial"size="2">ould you please assist</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial"size="2">?</font></span><span lang="en-us"></span><span lang="en-us"></span><p align="LEFT"><span lang="en-us"><fontface="Arial" size="2">Terry Nolen</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">SabreAirline Solutions</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">682-605-1173</font></span><spanlang="en-us"></span><span lang="en-us"></span>
On Wed, Dec 21, 2005 at 04:32:17PM -0600, Nolen, Terry wrote: > I'm new to postgresql and I'm trying to test the copy command with a csv > file. I keep getting an error which states that I don't have read > permissions. > > My database is owned by postgres, and I'm signed in as postgres. The > table that I'm placing the data into has a different owner. I've tried > changing privileges in windows to allow all access to everyone on this > machine access. I've even shared the machine with external users. I've > move the file to different directorys and created a tablespace with my > new table in it, into the postgres data directory. > > I see this type of error on many boards, but they tend to be with a > flavor of unix. I'm really not getting the premise behind what is going > on and why. Could you please assist? We need to know exactly what command you're trying to run, if it's in psql or something else, and the exact error message. -- Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
<div class="Section1"><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Jim, </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">First of all, thanks for your fast response. I'm doing a benchmark and your quick turn around will be noted in myfindings.</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">I'm trying to use the COPY command. I've tried executing it using pgadmin and pgsql and receive the same messageabout no read permissions. Here is the syntax?</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><spanstyle="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">COPY as_benchmark.midt FROM 'C:/postgres_data/midt_short.csv' WITH CSV</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Here is the error I receive.</span></font><p class="MsoPlainText"><b><font face="Courier New" size="2"><span style="font-size:10.0pt;font-weight:bold">ERROR: could not open file "c:/postgres_data/midt_short.csv" for reading: Permissiondenied</span></font></b><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">I understand that postgres must have access, but how do I make sure it is granted in Windows?</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Fyi...the use of "/" or "\\" in Windows could use some documentation explaining the acceptable ways to use them.</span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Thx again,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Terry Nolen</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">-----Original Message-----<br /> From: Jim C. Nasby [mailto:decibel@decibel.org] <br /> Sent: Wednesday, December21, 2005 7:10 PM<br /> To: Nolen, Terry<br /> Cc: pgadmin-support@postgresql.org<br /> Subject: Re: [pgadmin-support]COPY permissions problem - Windows XP</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><spanstyle="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">On Wed, Dec 21, 2005 at 04:32:17PM -0600, Nolen, Terry wrote:</span></font><p class="MsoPlainText"><font face="CourierNew" size="2"><span style="font-size: 10.0pt">> I'm new to postgresql and I'm trying to test the copy command with a csv</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">> file. I keep getting an error which states that I don't have read</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">> permissions.</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">> My database is owned by postgres, and I'm signed in as postgres. The</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">> table that I'm placing the data into has a different owner. I've tried</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">> changing privileges in windows to allow all access to everyone on this</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">> machine access. I've even shared the machine with external users. I've</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">> move the file to different directorys and created a tablespace with my</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">> new table in it, into the postgres data directory.</span></font><p class="MsoPlainText"><font face="CourierNew" size="2"><span style="font-size: 10.0pt">> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">> I see this type of error on many boards, but they tend to be with a</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">> flavor of unix. I'm really not getting the premise behind what is going</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">> on and why. Could you please assist?</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><spanstyle="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">We need to know exactly what command you're trying to run, if it's in</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">psql or something else, and the exact error message.</span></font><p class="MsoPlainText"><font face="Courier New"size="2"><span style="font-size: 10.0pt">-- </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Jim C. Nasby, Database Architect decibel@decibel.org </span></font><p class="MsoPlainText"><font face="CourierNew" size="2"><span style="font-size: 10.0pt">Give your computer some brain candy! www.distributed.net Team #1828</span></font><p class="MsoPlainText"><font face="CourierNew" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Windows: "Where do you want to go today?"</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><spanstyle="font-size: 10.0pt">Linux: "Where do you want to go tomorrow?"</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><spanstyle="font-size: 10.0pt">FreeBSD: "Are you guys coming, or what?"</span></font></div>
On Thu, Dec 22, 2005 at 08:53:42AM -0600, Nolen, Terry wrote: > I'm trying to use the COPY command. I've tried executing it using > pgadmin and pgsql and receive the same message about no read > permissions. Here is the syntax? I'm assuming you meant psql, not pgsql... > COPY as_benchmark.midt FROM 'C:/postgres_data/midt_short.csv' WITH CSV > > Here is the error I receive. > > ERROR: could not open file "c:/postgres_data/midt_short.csv" for > reading: Permission denied > > I understand that postgres must have access, but how do I make sure it > is granted in Windows? Probably through the GUI... I'm not a windows expert. An alternative in psql would be: \COPY ... \COPY in psql is equivalent to COPY as_benchmark_midt FROM STDIN, with psql then reading the file and piping it to the backend via standard in (or whatever the equivalent is on Windows). This means that only the psql session needs read access to the file, not the PostgreSQL backend. > Fyi...the use of "/" or "\\" in Windows could use some documentation > explaining the acceptable ways to use them. Do you have any suggested wording and location in docs? > Thx again, > > Terry Nolen > > > > -----Original Message----- > From: Jim C. Nasby [mailto:decibel@decibel.org] > Sent: Wednesday, December 21, 2005 7:10 PM > To: Nolen, Terry > Cc: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] COPY permissions problem - Windows XP > > > > On Wed, Dec 21, 2005 at 04:32:17PM -0600, Nolen, Terry wrote: > > > I'm new to postgresql and I'm trying to test the copy command with a > csv > > > file. I keep getting an error which states that I don't have read > > > permissions. > > > > > > My database is owned by postgres, and I'm signed in as postgres. The > > > table that I'm placing the data into has a different owner. I've > tried > > > changing privileges in windows to allow all access to everyone on this > > > machine access. I've even shared the machine with external users. > I've > > > move the file to different directorys and created a tablespace with my > > > new table in it, into the postgres data directory. > > > > > > I see this type of error on many boards, but they tend to be with a > > > flavor of unix. I'm really not getting the premise behind what is > going > > > on and why. Could you please assist? > > > > We need to know exactly what command you're trying to run, if it's in > > psql or something else, and the exact error message. > > -- > > Jim C. Nasby, Database Architect decibel@decibel.org > > Give your computer some brain candy! www.distributed.net Team #1828 > > > > Windows: "Where do you want to go today?" > > Linux: "Where do you want to go tomorrow?" > > FreeBSD: "Are you guys coming, or what?" > -- Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Same problem here, and same mystification. As someone coming from a basically single-user perspective who's looking at PostgresQL mainly for capacity (Microsoft Access 2003 keeps running out of space even though the tables I'm working with are under 100 MB) and license reasons (I'm uncomfortable with having to completely release everything I'm doing if I offer my software to a friend), it's a very strange beast indeed. I'm kind of guessing that this problem is arising because PostgresQL starts as a service which lacks the permissions of the human user who's using the service, so nothing on the desktop, My Documents folder, etc. is available to PostgresQL. What's odd is that I created a folder right off of the root, well outside the protected directories of any user accounts, and it still couldn't read the file. I presume the resolution to this show-stopper is obvious to users with the relevant expertise--in my many years of using and developing software for Windows and PCs, though, I've never had a need to learn this server-like stuff. Anyone out there know the trick? Is it possible to reinstall PostgresQL using a "Local System Account" in Windows, which allows Desktop access? Do I need to manually move the CSV data files I'm trying to COPY FROM into a particular PostgresQL-preferred directory first? Is there any such folder/directory? Or am I perhaps barking up the wrong tree here? Maybe PostgresQL is targetted more exlusively at *nix or server jockies? Maybe the tool is excellent, but not for my tasks?
unsubscribe-unsubscribe-unsubscribe-unsubscribe unsubscribe-unsubscribe-unsubscribe-unsubscribe unsubscribe-unsubscribe-unsubscribe-unsubscribe Le Lundi 2 Janvier 2006 17:28, john.rylander@gmail.com a écrit : > Same problem here, and same mystification. > > As someone coming from a basically single-user perspective who's > looking at PostgresQL mainly for capacity (Microsoft Access 2003 keeps > running out of space even though the tables I'm working with are under > 100 MB) and license reasons (I'm uncomfortable with having to > completely release everything I'm doing if I offer my software to a > friend), it's a very strange beast indeed. > > I'm kind of guessing that this problem is arising because PostgresQL > starts as a service which lacks the permissions of the human user who's > using the service, so nothing on the desktop, My Documents folder, etc. > is available to PostgresQL. > > What's odd is that I created a folder right off of the root, well > outside the protected directories of any user accounts, and it still > couldn't read the file. > > I presume the resolution to this show-stopper is obvious to users with > the relevant expertise--in my many years of using and developing > software for Windows and PCs, though, I've never had a need to learn > this server-like stuff. > > Anyone out there know the trick? Is it possible to reinstall > PostgresQL using a "Local System Account" in Windows, which allows > Desktop access? Do I need to manually move the CSV data files I'm > trying to COPY FROM into a particular PostgresQL-preferred directory > first? Is there any such folder/directory? > > Or am I perhaps barking up the wrong tree here? Maybe PostgresQL is > targetted more exlusively at *nix or server jockies? Maybe the tool is > excellent, but not for my tasks? > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Serge FAUCILHON site Pôle de Guyane Centre de Ressources Informatiques Universitaires de Guyane : Chef de Projet
Jim, Your reference to \COPY proved extremely useful for me--thanks! At first, I didn't much pay attention to it, because the portion of the docs you excerpted focussed on STDIN; but when I read the whole doc set for the command, it was clear that I could indeed plug in any old file spec as with the regular COPY command. When I did that, everything worked fine. :) (Well, when I did that and remembered to use the HEADER parameter, and managed to properly clean up the CSV file, which strangely had a trailing comma in every record, which comma bothered Access not at all, but very understandably bothered PostgreSQL, or perhaps psql.) Thanks again, Jim.
BTW, copying the relevant CSV file to the "My Documents" folder of the "postgres" user did not make things work with pgAdmin III Query (1.4.1)--same permissions problem as noted in the original message: ERROR: could not open file "C:\Documents and Settings\postgres\My Documents\Cumulative.csv" for reading: Permission denied This is working as designed, I guess, but it's not well documented for us poor Windows-using PostgresQL newbies (and the psql workaround is effective, but, from GUI perspective, rather clumsy). For those Windows users importing data from other local sources, I fear this will be an (at least temporary) unexpected showstopper every time. (But perhaps such users make only an insignificant percentage of the intended audience.)
Jim, I found the issue. I had not created a postgres WINDOWS user on my server when installing, and it was using my user_id. I dropped the database, added the postgres user, and reinstalled the server. Then tried the COPY and everything worked great. Just thought I'd let you know that it is important to create a postgres Windows user on the server before installing. Terry Nolen -----Original Message----- From: Jim C. Nasby [mailto:decibel@decibel.org] Sent: Wednesday, December 21, 2005 7:10 PM To: Nolen, Terry Cc: pgadmin-support@postgresql.org Subject: Re: [pgadmin-support] COPY permissions problem - Windows XP On Wed, Dec 21, 2005 at 04:32:17PM -0600, Nolen, Terry wrote: > I'm new to postgresql and I'm trying to test the copy command with a csv > file. I keep getting an error which states that I don't have read > permissions. > > My database is owned by postgres, and I'm signed in as postgres. The > table that I'm placing the data into has a different owner. I've tried > changing privileges in windows to allow all access to everyone on this > machine access. I've even shared the machine with external users. I've > move the file to different directorys and created a tablespace with my > new table in it, into the postgres data directory. > > I see this type of error on many boards, but they tend to be with a > flavor of unix. I'm really not getting the premise behind what is going > on and why. Could you please assist? We need to know exactly what command you're trying to run, if it's in psql or something else, and the exact error message. -- Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"