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

On Thu, 18 Jan 2024 at 07:22, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> 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.

Yes, this seems better. I think we can just skip this particular test
case using SKIP: like in 002_database test file.

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

I felt the current test was simpler by using the default hba.conf to
CREATE SUBSCRIPTION and make the change hba config only for the actual
test verification REFRESH PUBLICATION. Using non default hba conf for
CREATE SUBSCRIPTION might require identifying the CURRENT USER like
for me it runs as vignesh that is running and setting the hba for this
user too and removing the trust for all users. We need to remove the
trust from all users as otherwise we will hit "Non-superuser cannot
connect if the server does not request a password". I did not find an
easy way to do this. Maybe you have a simpler approach for this.If you
have an easy approach do you not mind posting a patch for this?

Regards,
Vignesh