Thread: psql and pgpass.conf on Windows

Hi,

Trying to write a script that will run on Linux, Windows, and Mac.

The "standard" credentials file contains:
   hostname:port:database:username:password
in:
Linux:   .pgpass
Windows: %APPDATA%\postgresql\pgpass.conf
Mac:     (I'm not there yet...)

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'

Thanks,  
Pierre

Hi,

On Thu, Jun 29, 2023 at 10:42:00PM -0400, pf@pfortin.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, and it's
known to be functional on Windows.

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.

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

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.

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

>Hi,
>
>On Thu, Jun 29, 2023 at 10:42:00PM -0400, pf@pfortin.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

On Thu, 29 Jun 2023 20:27:59 -0700 David G. Johnston wrote:

>On Thu, Jun 29, 2023 at 7:42 PM <pf@pfortin.com> wrote:
>
>> Trying to write a script that will run on Linux, Windows, and Mac.
>>
>
>This seems impossible on its face unless you use WSL within the Windows
>environment.  And if you are doing that, then the pathing would be WSL
>pathing, not native Windows.
>David J.

Sorry, a Python script which has support for each platform.

We have scripts which run on all; the real question is why is psql not
finding/using pgpass.conf...  (see my reply to Julien Rouhaud)

Regards,
Pierre

On Sat, Jul 01, 2023 at 01:40:49AM -0400, Kirk Wolak wrote:
>
> FWIW, I discovered that psql is case sensitive on the dbname, without
> quoting it!

That's on purpose, since shell quoting behavior is entirely different from SQL.
The quotes are discarded by the shell, so it would otherwise require users
doing something like

psql -d '"MyDbName"'

The behavior is the same for all client tools, and other object arguments like
username.