Re: Improvement to psql's connection defaults

Tom, Chris, thank you for your responses.

> There's an excellent manpage for psql, which can also be found online:
> https://www.postgresql.org/docs/current/app-psql.html
> I'm a little confused as to why people don't read the documentation and
> turn to the 'net - that's bound to dig up a lot of people who haven't
> read the docs, too.

For many users, Google is our user interface and manual, I can see by
checking my browser history that I googled 'postgresql getting
started' and arrived at this page '
https://www.postgresql.org/docs/10/tutorial-accessdb.html ' which
suggests to use psql without specifying host.
20 minutes later I was here
https://www.postgresql.org/docs/12/app-psql.html which probably means
I found the -h and -p arguments in the manner you suggest.

An alternative reason why someone would not use man psql would be if
they don't know what the client's executable is. Suppose you come from
mysql where the command for logging into your database was mysql, you
can't man psql because that's the command you are looking for, you
might google "postgresql command line client" which returns the psql
doc page.

Finally, you might google the error message that psql returned, which
is a perfectly reasonable thing to do.

> authentication options for TCP connections, even on localhost, are
> often different from those for Unix-domain sockets (e.g. while
> using peer authentication for administration purposes might make
> a lot of sense, TCP connections need some credential-based authentication
> so "any rogue process" cannot simply connect to your database).

We already established that a tcp connection was subpar in terms of
latency, we shall note then that a tcp connection is subpar in terms
of security. Additionally, it is duly noted that connection via tcp
might prompt the user for a password, which would mean that the user
interface for psql could change depending on the connection made.
These are not desirable qualities, but I must reiterate that these
would only happen instead of showing the user an error. I still feel a
subpar connection is the lesser of two evils. Additionally, it's
already possible to have this subpar connection and differing
interface on non-unix platforms.
As a side note,the official postgres image doesn't require a password
for localhost connections.

> Do we have any guarantees that these containers always expose the
> PostgreSQL server on what the host thinks is "localhost:5432"? I'm
> thinking of network namespaces, dedicated container network interfaces
> and all the other shenanigans. And what about the use cases of "more
> than one container" and "database on the host and in a container"?
> My concers is that adding more "magic" into the connection logic
> will result in more confusion instead of less - the distinction
> between the "default case Unix-domain socket" and "TCP" will be lost.

There are answers to these questions, but since Docker containers
don't expect programs to be docker-compliant, these are not things
postgresql should be concerned about. What postgresql should be
concerned about is that it was accesible via tcp on localhost at port
5432, and psql didn't reach it.

Regarding the magic, this is a very valid concern, but I feel it's too
late, someone other than us, (Robert Hass according to Git annotate)
already implemented this magic, the roots of psql magic can probably
be traced back to peer authentication even, that's some magical stuff
that I personally appreciate. I feel like these arguments are directed
towards the initial decision of having psql connect without arguments
vs psql requiring -h and -p arguments (and possibly -d and -U
parameters as well), a sailed ship.

>(a) don't mix-and-match Postgres packages from different vendors,

Since there's a client-server architecture here, I'm assuming that
there's compatibility between different versions of the software. If I
were to connect to an instance provided by an external team, I would
expect any psql to work with any postgres server barring specific
exceptions or wide version discrepancies.

(b) adjust the server's unix_socket_directories parameter so that
it creates a socket where your installed libpq expects to find it.
Nope, I wanted to connect via tcp, not via socket.

> I do not think your proposal would improve matters; it'd just introduce
> yet another variable, ie which transport method did libpq choose.
> As Christoph noted, that affects authentication behaviors, and there
> are a bunch of other user-visible impacts too (SSL, timeouts, ...)

This variable already exists, it just depends on the OS. Again, these
user-visible impacts would
only occur if the user would have received an error instead. Which is
the lesser of two evils?

> If we were going to do something of this sort, what I'd be inclined
> to think about is having an option to probe both of the common socket
> directory choices, rather than getting into TCP-land. But that still
> might be a net negative from the standpoint of confusion vs. number of
> cases it fixes.

I think trying both sockets is a great extension of the idea I'm
presenting, once magic is introduced, the expectation of simplicity
has already been broken, so that cost is only paid once, adding
further magic dilutes that cost and makes it worth it.
Given the concerns regarding user confusion, consider displaying the
failed unix socket connection message, this would mitigate most of the
concerns while still providing a better experience than pure failure.

When you say confusion, do you mean user confusion or developer
confusion? Because I'm interpreting it as developer confusion or
source code complexity, I'm fairly confident that these would be a net
gain for user experience,
perhaps it's modern software backed by billion dollar wall street
conglomerates increasing my expectations but, when I received that
error, it felt like psql could have known what I meant, and it also
felt like it was trying to know what I meant, therefore I tried to
teach it what I actually meant, I'm sorry for antropomorphizing psql,
but it wanted to learn this. Consider this example, if you are away
from home and you tell Google Maps or Uber that you want to go to your
city, does it fail claiming that it doesn't have enough information or
claiming that the route it would take given the subpar information you
gave it would be subpar? Or would it do its best and try to guide you
towards the center of the city?

That said, I undersand that this is a classic tradeoff between
simplicity of user experience vs simplicity of source code. And since
a simpler user experience necessitates more effort on the backend, I
understand if you would decide not to go for this, you know better
than me what the priorities of postgresql are, and it's your time that
will be spent maintaining this change, it's understandable for an open
source product not to be Google grade. But I do want to reaffirm my
stance that this would be a better experience for users, I offer my
patch as a token of this conviction.

Regards.

El lun., 16 de dic. de 2019 a la(s) 11:17, Tom Lane
(tgl(at)sss(dot)pgh(dot)pa(dot)us) escribió:
>
> Tomas Zubiri <me(at)tomaszubiri(dot)com> writes:
> > The problem was that running the command psql without arguments
> > returned the following
> > error message:
> > psql: could not connect to server: No such file or directory
> > Is the server running locally and accepting
> > connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
>
> The reason this failed, most likely, is using a semi-broken installation
> in which libpq has a different idea than the server of where the
> unix socket should be. The right fix is one or the other of
>
> (a) don't mix-and-match Postgres packages from different vendors,
>
> (b) adjust the server's unix_socket_directories parameter so that
> it creates a socket where your installed libpq expects to find it.
>
> I realize that this isn't great from a newbie-experience standpoint,
> but unfortunately we don't have a lot of control over varying
> packager decisions about the socket location --- both the "/tmp"
> and the "/var/run/postgresql" camps have valid reasons for their
> choices.
>
> I do not think your proposal would improve matters; it'd just introduce
> yet another variable, ie which transport method did libpq choose.
> As Christoph noted, that affects authentication behaviors, and there
> are a bunch of other user-visible impacts too (SSL, timeouts, ...).
>
> If we were going to do something of this sort, what I'd be inclined
> to think about is having an option to probe both of the common socket
> directory choices, rather than getting into TCP-land. But that still
> might be a net negative from the standpoint of confusion vs. number of
> cases it fixes.
>
> regards, tom lane