Re: [16+] subscription can end up in inconsistent state

On Wed, 2024-01-17 at 15:31 +0530, vignesh C wrote:
> There was a buildfarm failure at [1], I was able to reproduce it in
> my
> environment. The changes suggested by Tom Lane at [2] fixes the
> problem. Apart from that pg_hba.conf should have authentication
> configuration for the host which does not use unix domain sockets.
> The
> attached patch has the changes for the same.

I don't think adding --create-role is quite the right solution. The
point of the test is to distinguish between a password that comes from
the environment ($publisher_connstr1) vs specified in the connection
string ($publisher_connstr2).

There's no reason to use SSPI for that, and therefore no reason to
configure SSPI with --create-role. The change in your patch does allow
the connection initiated as part of CREATE SUBSCRIPTION to succeed, but
by a different path than what we need to test later anyway, so it seems
confusing to me. We might as well just get pg_hba.conf configured
correctly before issuing the CREATE SUBSCRIPTION.

We can't just add a "host" line to the pg_hba.conf, though, because
that violates this idea here (Cluster.pm):

Authentication is set up so that only the current OS user can
access the cluster. On Unix, we use Unix domain socket
connections, with the socket in a directory that's only
accessible to the current user to ensure that. On Windows, we
use SSPI authentication to ensure the same (by pg_regress
--config-auth).

The 001_password.pl test just doesn't run if !$use_unix_sockets and I
think we should do something similar.

We still need to move the pg_hba.conf changes up above the CREATE
SUBSCRIPTION.

Regards,
Jeff Davis