2

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?

  • I am not sure either. Can you try connection by using a command like CREATE SUBSCRIPTION ..CONNECTION 'dbname=.. host=.. sslmode=require ' PUBLICATION ...; – Sahap Asci Sep 22 '17 at 11:15
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.