Re: Improvement to psql's connection defaults

To summarize possible enhancements to the current patch:

a- Don't hide failed attempted connections when defaults are used.
b- Attempt to connect via other common socket locations "/tmp".
c- New: Display the complete command used once a successful connection
has been made, so running plain psql would print
"Connecting with psql -h /var/run/postgresql" in most cases, psql -h
/tmp in others, psql -h localhost -p 5432 in others.

I could write a patch with them if it were to get implemented.

Regards.

El lun., 16 de dic. de 2019 a la(s) 12:54, Tomas Zubiri
(me@tomaszubiri.com) escribió:
>
> 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@sss.pgh.pa.us) escribió:
> >
> > Tomas Zubiri <me@tomaszubiri.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