Improvement to psql's connection defaults
Hello, this week I decided to pursue an error a bit further than usual, even after having fixed it for myself, I found that I could fix it for future newcomers, especially those running containerized distributions. 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"? Now, I eventually found a way around this by specifying the host with the following command 'psql -h localhost -p 5432'. However, the answers I found on google didn't suggest this simple fix at all, I found a lot of confused users either exposing the sockets from their containers, or worse, bashing into their containers and running psql from inside :*( https://stackoverflow.com/questions/27673563/how-to-get-into-psql-of-a-running-postgres-container/59296176#59296176 I also found this is a common error in postgres docs: https://www.postgresql.org/docs/9.1/server-start.html https://www.postgresql.org/docs/10/tutorial-createdb.html So I wondered, since psql went through the trouble of guessing my unix socket, it could guess my hostname as well. Indeed I would later find that the tcp defaults were already implemented on non-unix builds, additionally psql already has a mechanism to try multiple connections. So my humble change is for unix builds to try to connect via unix socket, and if that fails, to connect via localhost. This would save headaches for newbies trying to connect for the first time. Attached you will find my patch. Below you can find the form required for submitting patches. Project name: Not sure, psql? Uniquely identifiable file name, so we can tell the difference between your v1 and v24: Running-psql-without-specifying-host-on-unix-systems.patch What the patch does in a short paragraph: When psql is not supplied a host or hostname, and connection via default socket fails, psql will attempt to connect via default tcp, probably localhost. Whether the patch is for discussion or for application: Application, but further testing is required. Which branch the patch is against: master Whether it compiles and tests successfully, so we know nothing obvious is broken: Compiles and works successfully in my linux machine, however I can't test whether this works on non-unix machines, I will need some help there. I didn't see any automated tests, hopefully I didn't miss any. Whether it contains any platform-specific items and if so, has it been tested on other platforms: Yes, connection via socket is only available on unix systems. I need help testing on other platforms. Confirm that the patch includes regression tests to check the new feature actually works as described.: make check runs successfully, there seems to be a test called psql_command that confirms that psql can connect without specifying host. But I didn't add a test for connecting via tcp. Include documentation on how to use the new feature, including examples: The docs already describe the correct behaviour in /doc/src/sgml/ref/psql-ref.sgml "If you omit the host name psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets." Describe the effect your patch has on performance, if any: OS without unix socket support still won't try to connect via unix socket so they will be unaffected. This change should only affect paths where connection via socket failed and the user would have been shown an error. One could argue that, some users might suffer a slight performance hit by not being told that they are connecting via a subpar method, but this is a sub-tenth of a second latency difference for local connections I believe. If this is an issue, a warning could be added. Thank you for time, Tomas. From 6e4d305b6a011df4ea20f606431e50b3462a18c7 Mon Sep 17 00:00:00 2001 From: Tomas Zubiri Date: Sun, 15 Dec 2019 02:46:19 -0300 Subject: [PATCH] Running psql without specifying host on unix systems should try connecting via tcp if unix socket is unavailable. --- src/interfaces/libpq/fe-connect.c | 28 1 file changed, 20 insertions(+), 8 deletions(-) diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c index 66a9128605..5307a99dfc 100644 --- a/src/interfaces/libpq/fe-connect.c +++ b/src/interfaces/libpq/fe-connect.c @@ -994,7 +994,9 @@ connectOptions2(PGconn *conn) /* * Allocate memory for details about each host to which we might possibly * try to connect. For that, count the number of elements in the hostaddr - * or host options. If neither is given, assume one host. + * or host options. If neither is given, assume 2 hosts on systems with + * unix socket support, try to connect via sockets first, if that fails, +
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 (m...@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, > >
Re: Improvement to psql's connection defaults
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 (t...@sss.pgh.pa.us) escribió: > > Tomas Zubiri 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
