Re: psql and pgpass.conf on Windows

On Fri, 30 Jun 2023 11:16:36 +0800 Julien Rouhaud wrote:

>Hi,
>
>On Thu, Jun 29, 2023 at 10:42:00PM -0400, pf(at)pfortin(dot)com wrote:
>>
>> Windows: %APPDATA%\postgresql\pgpass.conf
>>
>> On Linux, this works. However, on Windows, psql will not read
>> pgpass.conf (tried in just about every location I could think of)
>>
>> Even: "set PGPASSFILE=<path to file>" does not work.
>>
>> Finally, out of frustration, tried:
>> set PGPASSWORD=<password>
>> and that got me past the password issue, only to now get:
>> 'more' is not recognized as an internal or external command,
>> operable program or batch file.
>>
>> Given the number of queries about pgpass.conf and finding no answer that
>> works, is there no bug report on this?
>>
>> Thinking that psql was not adjusted for Windows, tried naming the file:
>> .pgpass
>> .pgpass.conf
>> also in various locations to no avail...
>> What am I (and all the others found in searches) missing? Or are there
>> unresolved bugs in psql?
>> - pgpass.conf
>> - expecting external executable: 'more'
>
>The file should indeed be by default %APPDATA%/postgresql/pgpass.conf,

Will have to check with the owner of that machine where the PG install
came from... maybe a non-official installer...?

>and it's known to be functional on Windows.

Given the number of searches we've done, at least one "Success" message
should have been found; alas...

>The fact that you hit some error with a "more" program makes me think that your
>script setup some environment variables (like PAGER=more, which would explain
>why you hit that error) that maybe interfere with file location and/or name.

Good point; but...
echo %PAGER%
%PAGER% (does Windows normally print var name if empty? I'm a Linux-only
user since 1998) ...apparently, it does:
echo %JUNK%
%JUNK% ;p
vs:
echo %APPDATA%
C:\Users\Pierre\AppData\Roaming

>Now, since setting PGPASSFILE also doesn't work I start to wonder if there's
>another problem. Does the password (or any other field) contain some non-ASCII
>characters?

type %APPDATA%\postgresql\pgpass.conf
127.0.0.1:5432:ncsbe:postgres:<pw>
(only ASCII characters and no trailing space)
was using "localhost" before trying 127.0.0.1
even "*:*:..." failed.

>There could be an encoding issue in the file, or maybe the problem
>is with the presence or absence of a BOM in the file. Another thing you should
>try just in case is to replace backwards slashes with forward slashes.

No [back]slashes at all (except in later testing with PGPASSFILE...but
psql should be looking in the right place without hinting...

>If none of that work, you could also check what file psql is trying to open
>using the equivalent of "strace" for Windows, if such a thing exists.

LOL Knowing it wouldn't work, I actually typed "strace"... to see if
psql was accessing the file, I tried changing the port number; but psql
still mentions 5432 in its error message.

Thanks, will do more digging when I return,
Pierre