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
On Thu, Oct 28, 2021 at 8:54 AM David Hillman <david@kapitaltrading.com>
wrote:
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:
You've already demonstrated that when connecting via "host" the first
matching entry is a password requiring entry. In your first email pgpass
provided the password. Here you've simply removed pgpass and demonstrated
that you are actually being prompted for the password, as expected.
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. psql decides first where to go talk,
then the pg_hba.conf file ignores any irrelevant lines based upon where the
connection is coming in from and picks the first match among the remaining.
David J.
"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