could not initiate GSSAPI security context on Postgres 14 logical replication

Question

I am following this post to enable ssl on Postgres 14 for logical replication. Then try to make connection on client:

CREATE SUBSCRIPTION my-sub
CONNECTION 'host=my-domain.com dbname=my-db user=my-username password=xxxxxx'
PUBLICATION my-pub;

It throws error:

2022-05-12 13:51:36.047 PDT [37340] ERROR:  could not connect to the publisher: connection to server at "my_domain.com" (xxx.xxx.xxx.141), port 5432 failed: could not initiate GSSAPI security context:  The operation or option is not available: Credential for asked mech-type mech not found in the credential handle
    connection to server at "my_domain.com" (xxx.xxx.xxx.141), port 5432 failed: FATAL:  connection requires a valid client certificate
    connection to server at "my-domain.com" (xxx.xxx.xxx.141), port 5432 failed: FATAL:  no pg_hba.conf entry for host "xxx.xxx.xxx.199", user "my-username", database "my-db", no encryption

On my-pub server, one line was added to pg_hba.conf:

hostssl all             all             0.0.0.0/0               scram-sha-256 clientcert=verify-full
 

On sub client, the ca file is setup as below:

ssl_ca_file = '/usr/local/var/postgres/root.crt'. //<<==client cert copied from pub server. 

Answer 1

Mostly people just use serve certs. Using client certs is unusual, I would say especially in the case of a logical replication subscriber. But if you do actually want the publisher to demand client certs, it is not configured incorrectly to that purpose (or at least, not that we can tell from the current data). The publisher is demanding a client cert, but the subscriber is not offering one. The configuration problem is on the subscriber.

Note that in this case the subscriber will be acting as the client to connect to the publisher, not acting in the role of a server. It uses the libpq client library to do that, and so the configuration of it is not based on the contents of postgresql.conf. In particular, ssl_ca_file is a server configuration option, not client configuration.

So the way to do this would be for the CONNECTION to look something like

'host=my-domain.com dbname=my-db user=my-username password=xxxxxx sslcert=/foobar/my-username.crt sslkey=/foobar/my-username.key'

But for this to work, the cert and key would need to be on the subscriber computer, readable to whomever owns the postgres process. Which already renders any security benefit dubious.