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?