Обсуждение: BUG #17249: Bug in .pgpass search and/or documentation thereof, Ubuntu 13.4-4

The following bug has been logged on the website:

Bug reference:      17249
Logged by:          David Hillman
Email address:      david@kapitaltrading.com
PostgreSQL version: 13.4
Operating system:   Kubuntu 20.04.3 LTS
Description:

There's a bug in either the .pgpass search, or the documentation thereof, in
13.4-4.

 The documentation ( https://www.postgresql.org/docs/13/libpq-pgpass.html )
says:

"The host name field is matched to the host connection parameter if that is
specified, otherwise to the hostaddr parameter if that is specified; if
neither are given then the host name localhost is searched for. The host
name localhost is also searched for when the connection is a Unix-domain
socket connection and the host parameter matches libpq's default socket
directory path.

If that were the situation, the following two cases would produce the same
result.  As you can see, they do not.  In the first case below, "neither
(host connection parameter) are given" which should result in .pgpass being
searched for "localhost" per the above statements.  It does not.


root@dbserver:~# cat ~/.pgpass
localhost:*:*:valid_user:valid_password

root@dbserver:~# psql -d postgres -U valid_user
psql: error: connection to server on socket
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication
failed for user "valid_user"

root@dbserver:~# psql -d postgres -U valid_user -h localhost
psql (13.4 (Ubuntu 13.4-4.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

postgres=# \q

PG Bug reporting form <noreply@postgresql.org> writes:
> There's a bug in either the .pgpass search, or the documentation thereof, in
> 13.4-4.

Perhaps, but this example doesn't prove it.  Your failing case is

> root@dbserver:~# psql -d postgres -U valid_user
> psql: error: connection to server on socket
> "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication
> failed for user "valid_user"

Notice it says "peer authentication", not "password authentication".
So the password was never asked for, and the problem is that you're
not logged in as "valid_user" (rather, it looks like, as "root").

> root@dbserver:~# psql -d postgres -U valid_user -h localhost
> psql (13.4 (Ubuntu 13.4-4.pgdg20.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)

This is evidently going to a different pg_hba.conf line, which
might or might not be selecting password auth --- we can't see it.

            regards, tom lane

On 10/27/21 7:17 PM, Tom Lane wrote:

> Notice it says "peer authentication", not "password authentication".
> So the password was never asked for, and the problem is that you're
> not logged in as "valid_user" (rather, it looks like, as "root").

This is still a bug, since per your description, the -U flag doesn't 
work correctly -- or is meaningless.  And, the -U flag works differently 
depending on which other flags are set ( -h in his case ), as my example 
does show, and that's not documented anywhere, as far as I know.


> This is evidently going to a different pg_hba.conf line, which
> might or might not be selecting password auth --- we can't see it.

There exist no such lines which pertain to "valid_user" here.

Furthermore, if I remove .pgpass, this case's result changes.

root@dbserver:~# cat ~/.pgpass
cat: /root/.pgpass: No such file or directory
root@dbserver:~# psql -d postgres -U valid_user -h localhost
Password for user valid_user:

Clearly, .pgpass is being utilized in the second example, and not in the 
first, contrary to the apparent intent and the documentation.

-- 
David Hillman
david@kapitaltrading.com

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Your original email demonstrates that, absent -h localhost, psql is looking
> for and finding a local socket and thus the first matching entry is a
> "local" one which does not require password entry because it is set for
> "peer".  This is working as intended.

Indeed.  If you don't like the results you're getting, you need to go
adjust your pg_hba.conf file.  Right now, you've got it set to apply
different authentication rules for Unix-socket and localhost connections,
and you apparently don't want that.

            regards, tom lane