psql and pgpass.conf on Windows

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

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 Fri, Jun 30, 2023 at 8:39 AM <pf@pfortin.com> wrote:

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

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.

Pierre, I use my pgpass .conf in windows.

copy that file to your current directory.
and set
PGPASSFILE=pgpass.conf

and try to get in.
Next, please specify the complete command line for psql you are using...
This way you know you are not passing in a strange variable.

If I set PGPASSFILE to a bad filename, I get a password prompt.

But the only time I've seen this was someone not specifying the dbname
correctly.

FWIW, I discovered that psql is case sensitive on the dbname, without
quoting it!

HTH,

Kirk

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.

On Sat, Jul 1, 2023 at 2:07 AM Julien Rouhaud <rjuju123@gmail.com> wrote:

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.

Thanks,
I was mentioning it in case the person who could not figure out why his
pgpass.conf was not being hit correctly,
might have multiple things colluding against them.

It's nice to know it is consistent, and applies to usernames.