[postgresql] Setting postgresql__server has unexpected side effects

As noted in the discussion for PR #2295...setting postgresql__server can have unexpected side effects on the host running the actual PostgreSQL DB.

Imagine two hosts, client.example.com and server.example.com.

Without postgresql__server set, client and server will have these lines in /etc/postgresql-common/user_clusters:

# USER         GROUP          VERSION    CLUSTER              DATABASE
*              *              13         main                 *

That's fine for server (which will use the local unix socket to connect to the DB), but not for client.

The logic thing to do would then be to define postgresql__server as server.example.com, which means that both client and server will have these lines in /etc/postgresql-common/user_clusters:

# USER         GROUP          VERSION    CLUSTER                 DATABASE
*              *              13         server.example.com:5432 *

This works for client, but it introduces a change in behaviour for server.

Note that /etc/postgresql/13/main/pg_hba.conf on a default installation will contain these lines near the top:

# TYPE	   DATABASE        USER            ADDRESS                      METHOD

# Database superuser account, do not disable
local      all             postgres                                     peer map=system

# Block remote connections to admin account
host       all             postgres        all                          reject

The above change to /etc/postgresql-common/user_clusters will then mean that passwordless psql access for the postgres account will no longer work out of the box on server (because the connection will default to an actual IP connection to server.example.com, meaning that the type is host rather than local).

One option would be to detect in the Ansible role that server.example.com == localhost for server and use the first format for /etc/postgresql-common/user_clusters (which means all users, not only postgres would use unix sockets). The disadvantage being that we "force" (as a default) a unix connection for all users, meaning that connections by user foobar will use different mechanisms when initiated from server or from client.

Another option would be to add extra lines in /etc/postgresql-common/user_clusters on server (one per peer defined in peer map=system), something like this:

postgres       *              13         main                 *
system_user_1  *              13         main                 *
system_user_2  *              13         main                 *
*              *              13         server.example.com:5432 *

I think I'd suggest going with the first option though. And I think a short blurb might be in order in the docs for the postgresql__server option.

Start by tracing the Ansible role handling the postgresql__server option and how it generates /etc/postgresql-common/user_clusters. Compare the generated entries with /etc/postgresql/13/main/pg_hba.conf and the client/server cases described here. Done means local postgres peer access remains functional on the database host while clients use the configured server address, with the chosen behavior documented.