Thread: 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.

## Tomas Zubiri (me@tomaszubiri.com):

> The problem was that running the command psql without arguments

There's an excellent manpage for psql, which can also be found online:
https://www.postgresql.org/docs/current/app-psql.html

In there you'll find a section "Connecting to a Database", with the
following sentences:
: In order to connect to a database you need to know the name of your
: target database, the host name and port number of the server, and what
: user name you want to connect as.  psql can be told about those parameters
: via command line options, namely -d, -h, -p, and -U respectively.

and
: 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.

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.

> 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.

I'd thing that opens a can of worms:
- 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).
- 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.

Regards,
Christoph

-- 
Spare Space

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

Hello,

On 2019-Dec-15, Tomas Zubiri wrote:

> 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:
> [...]

Please, where did you find this "form"?  We don't have a *required* form
for submitting patches; I suspect there's an opinionated page somewhere
that we should strive to fix.  (Those questions you list are appropriate
to answer, but forcing you to repeat what you had already explained in
the first part of your email is pointless bureaucracy.)

Thanks

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

> On 16 Dec 2019, at 15:47, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

> Please, where did you find this "form"?

It seems to be from the wiki:

  https://wiki.postgresql.org/wiki/Submitting_a_Patch#Patch_submission

cheers ./daniel

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

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

## Tomas Zubiri (me@tomaszubiri.com):

> 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.

It's an entirely different thing, I'd argue. I'm not even convinced
that an error message is a bad thing: not specifying connection parameters
gives you the defaults (which are clearly documented - having the doc
maintainers enter into a SEO-contest would be expecting too much); and
if that fails, there's an error. Adding more guesswork on how to connect
to your database server will add confusion instead of reducing it.
(Where does "localhost" resolve to? Does it resolve at all? What
about IPv4 vs. IPv6? Is IP traffic allowed there? That's all stuff
which has been relevant in one way or the other while looking at existing
systems. Real world can deviate quite significantly from what one
whould expect as "sane".) I for one prefer to have clear defaults
and clear error messages in case that does not work.

>  Additionally, it's
> already possible to have this subpar connection and differing
> interface on non-unix platforms.

I think there's only one relevant platform without unix sockets
left (I'm not sure about vxWorks and other embedded systems, but
their applications rarely include full-blown database servers),
and that system has gone great lengths to include a linux subsystem -
that might tell you something.

> >(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.

Generally speaking: yes. But compiled-in defaults may not match
(like the location of the Unix sockets) across different vendor
packages of the same version. And client tools might have a hard
time working against a newer major release of the server: the protocol
does not change (at least, it didn't for a long time, except for some
additions like SCRAM authentication), but the catalog may have changed
between major versions and the client can't get the information it
needs.

>  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?

That's a good example, but not in the way you think: people and vehicles
(trucks and lorries, even) ending up some hundreds of kilometres from
their intended destination because their navigation system "tried it's
best" with an ambiguously entered name is quite a common occurence here.
(For example, there are at least six places called "Forst" and some dozens
"Neustadt" - many more if you count boroughs and similar - in Germany).

Regards,
Christoph

-- 
Spare Space

On 2019-Dec-16, Daniel Gustafsson wrote:

> On 16 Dec 2019, at 15:47, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> 
> > Please, where did you find this "form"?
> 
> It seems to be from the wiki:
> 
>   https://wiki.postgresql.org/wiki/Submitting_a_Patch#Patch_submission

OK, I made a few edits there and in other related pages.  I'm sure more
improvements can be had, if somebody has the time and inclination.

Thanks!

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services