Thread: BUG #17249: Bug in .pgpass search and/or documentation thereof, Ubuntu 13.4-4
BUG #17249: Bug in .pgpass search and/or documentation thereof, Ubuntu 13.4-4
From
PG Bug reporting form
Date:
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
Re: BUG #17249: Bug in .pgpass search and/or documentation thereof, Ubuntu 13.4-4
From
Tom Lane
Date:
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
Re: BUG #17249: Bug in .pgpass search and/or documentation thereof, Ubuntu 13.4-4
From
David Hillman
Date:
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
Re: BUG #17249: Bug in .pgpass search and/or documentation thereof, Ubuntu 13.4-4
From
"David G. Johnston"
Date:
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.
Re: BUG #17249: Bug in .pgpass search and/or documentation thereof, Ubuntu 13.4-4
From
Tom Lane
Date:
"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