pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
psql and pgpass.conf on Windows
8+ messages / 4 participants
[nested] [flat]

* psql and pgpass.conf on Windows
@ 2023-06-30 02:42  pf@pfortin.com
  0 siblings, 2 replies; 8+ messages in thread

From: pf@pfortin.com @ 2023-06-30 02:42 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: psql and pgpass.conf on Windows
@ 2023-06-30 03:16  Julien Rouhaud <rjuju123@gmail.com>
  parent: pf@pfortin.com
  1 sibling, 1 reply; 8+ messages in thread

From: Julien Rouhaud @ 2023-06-30 03:16 UTC (permalink / raw)
  To: pf@pfortin.com; +Cc: pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: psql and pgpass.conf on Windows
@ 2023-06-30 03:27  David G. Johnston <david.g.johnston@gmail.com>
  parent: pf@pfortin.com
  1 sibling, 1 reply; 8+ messages in thread

From: David G. Johnston @ 2023-06-30 03:27 UTC (permalink / raw)
  To: pf@pfortin.com; +Cc: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: psql and pgpass.conf on Windows
@ 2023-06-30 12:38  pf@pfortin.com
  parent: Julien Rouhaud <rjuju123@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: pf@pfortin.com @ 2023-06-30 12:38 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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







^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: psql and pgpass.conf on Windows
@ 2023-06-30 12:38  pf@pfortin.com
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: pf@pfortin.com @ 2023-06-30 12:38 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: psql and pgpass.conf on Windows
@ 2023-07-01 05:40  Kirk Wolak <wolakk@gmail.com>
  parent: pf@pfortin.com
  0 siblings, 1 reply; 8+ messages in thread

From: Kirk Wolak @ 2023-07-01 05:40 UTC (permalink / raw)
  To: pf@pfortin.com; +Cc: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: psql and pgpass.conf on Windows
@ 2023-07-01 06:06  Julien Rouhaud <rjuju123@gmail.com>
  parent: Kirk Wolak <wolakk@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Julien Rouhaud @ 2023-07-01 06:06 UTC (permalink / raw)
  To: Kirk Wolak <wolakk@gmail.com>; +Cc: pf@pfortin.com; pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: psql and pgpass.conf on Windows
@ 2023-07-03 15:10  Kirk Wolak <wolakk@gmail.com>
  parent: Julien Rouhaud <rjuju123@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Kirk Wolak @ 2023-07-03 15:10 UTC (permalink / raw)
  To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: pf@pfortin.com; pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-07-03 15:10 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-30 02:42 psql and pgpass.conf on Windows pf@pfortin.com
2023-06-30 03:16 ` Julien Rouhaud <rjuju123@gmail.com>
2023-06-30 12:38   ` pf@pfortin.com
2023-07-01 05:40     ` Kirk Wolak <wolakk@gmail.com>
2023-07-01 06:06       ` Julien Rouhaud <rjuju123@gmail.com>
2023-07-03 15:10         ` Kirk Wolak <wolakk@gmail.com>
2023-06-30 03:27 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-30 12:38   ` pf@pfortin.com

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox