Jim,

First of all, thanks for your fast response. I'm doing a benchmark and
your quick turn around will be noted in my findings.



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?



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?



Fyi...the use of "/" or "\\" in Windows could use some documentation
explaining the acceptable ways to use them.



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?"

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?"

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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?

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.

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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.)