PostgreSQL 10 – secure remote logical replication (SSH?)

postgresqlreplicationSecuritysshssl

I am exploring Postgresql for a new project, and the upcoming logical replication feature in version 10 interests me.

I would like to use it to have a remote server automatically hold an up-to-date copy of some tables from a different server, across the internet. With proper database design this could make entire webservices unnecessary.

However, the feature requires the use of a plain connection string in the CREATE SUBSCRIPTION command. This means I cannot simply point it to a remote server, not without exposing it to the internet.

First, what secure options do I have available here?

  • Creating a VPN between the two servers. Safest option, most cumbersome.
  • Establish a SSH tunnel between the two servers. Less cumbersome, still safe as long as the SSH server is properly configured. Once the SSH tunnel is up the connection becomes just a normal SQL connection, so it should work with replication, right?
  • Postgresql has native SSL support. I'm not sure if the replication subscriber would automatically use it when connecting to the publisher. But more importantly, while this would protect the data in transfer, it would still require me to expose the server to the internet, use something like fail2ban to protect against intruders, and I'm sure I've missed other potential threats, so all in all it would make me uneasy.
  • Any other option I may have overlooked?

Second, are there any other reasons why using logical replication in such a fashion would be a bad idea?

Best Answer

For native SSL support:

On the subscription side, you can put sslmode=verify-full in the CONNECTION string for your CREATE SUBSCRIPTION, to force ssl to be used.

On the publication side, you can whitelist only the PostgreSQL port and only for the known IP address of the subscriber at the firewall. There shouldn't be a need for fail2ban if you do this. You can also demand that the subscriber side use ssl client cert authentication rather than password authentication, by using cert for the pg_hba method.

Of course you will need a CA to issue certificates, or run your own mini-CA.

Related Question