MySQL foreign data wrapper: use SSH parameters for SSL connection?

Question

this might be a very rare usecase, but what I'm trying to do is:

Connect to a MySQL-Databse (of my website) from a PostgreSQL Database to create a foreign table that gets the website-user information from the MySQL Database.

So what I did so far is installed the MySQL foreign data wrapper (fdw) for PostgreSQL and tried setting up the foreign server. The problem is, the webhost only allows SSH access to the database while the mysql_fdw only accepts additional SSL parameters:

{ "ssl_key",        ForeignServerRelationId },
{ "ssl_cert",       ForeignServerRelationId },
{ "ssl_ca",         ForeignServerRelationId },
{ "ssl_capath",     ForeignServerRelationId },
{ "ssl_cipher",     ForeignServerRelationId }

So my question is, can I somehow use my SSH login credentials (SSH user/pw or key pair) to generate the necessary SSL parameters?

Does anyone have a clue on how to achieve this?

Thanks and kind regards,

Michael

Richard Huxton · Accepted Answer · 2018-07-20 08:09:12Z

You almost certainly aren't accessing MySQL via ssh. What you are doing is accessing the server it is on via ssh and then connecting to MySQL. You might be doing this just by running the mysql command-line client or by forwarding a port to your local machine.

So - if you want to connect to MySQL from your PostgreSQL server it will need to be accessible from the internet. You should open up just PostgreSQL's IP address for MySQL's port on your firewall. Then, your GRANT statements on MySQL can the user login to that remote hostname too. Have a separate user just for this remote connection.

It might also be possible to require a known client certificate from the PostgreSQL connection. That will further secure access.

Now - it might be that you are on a service that doesn't allow remote MySQL access at all. In which case, you will either need to set up a permanent ssh tunnel (fiddly, can cause problems when intermittently connection drops) or push data from the mysql end.

Collectives™ on Stack Overflow

MySQL foreign data wrapper: use SSH parameters for SSL connection?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related