Connect with database standing on host which requires SSH connection using Qt
-
I try to make application with PostgreSQL database. It is located on remote server which is not visible in public but requires establishment of tunnel over SSH. When I want to make a connection in TablePlus, configuration looks like:
and it works perfectly. But in QtCreator I have following code which generates error:#include "mainwindow.h" #include <QApplication> #include <QSqlDatabase> #include <QDebug> #include "QSqlError" int main(int argc, char *argv[]) { QApplication a(argc, argv); QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL"); db.setHostName("host.with.database.com"); db.setDatabaseName("database"); db.setPassword("******"); db.setUserName("userFromAboveHost"); if(db.open()) { qDebug() <<"opened" ; db.close(); } else { qDebug() << db.lastError().text(); exit(1); } MainWindow w; w.show(); return a.exec(); }
"could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "host.with.database.com" and accepting TCP/IP connections on port 5432? QPSQL: Unable to connect"
To inform you in advance, host accepts such kind of connections because connection in TablePlus is possible without obstacles.
My questions are: What should I add to my code? Is it overally possible to establish that connection using Qt? Or am I forced to change the technology?
I tried:
QProcess *proc = new QProcess(); proc->start("ssh -L 5433:localhost:5432 userFromAbove@host.with.database.com");
and
QTcpSocket sock; sock.setProxy(QNetworkProxy::NoProxy); sock.connectToHost("userFromAboveServer@host.with.database.com", 5433);
but none of them works. Please help :) Thanks.
-
@marcus74 said in Connect with database standing on host which requires SSH connection using Qt:
Is it overally possible to establish that connection using Qt?
afaik it's not possible
-
It's possible (I do it with a remote (only accessible with ssh) Microsoft's SQLServer)
Establish the tunnel using ssh, preferably with 5432 on both sides, e.g.
ssh -L 5432:localhost:5432 userFromAbove@host.with.database.com
then start a QSqlDatabase connected to localhost:5432
-
It's possible (I do it with a remote (only accessible with ssh) Microsoft's SQLServer)
Establish the tunnel using ssh, preferably with 5432 on both sides, e.g.
ssh -L 5432:localhost:5432 userFromAbove@host.with.database.com
then start a QSqlDatabase connected to localhost:5432
@hskoglund Thanks, seems like connection was created, but now I receive new error
psql: FATAL: Ident authentication failed for user "userFromAboveHost"
despite of passing valid credentials. How to avoid that?
-
@hskoglund Oh I see, it argues with postgres on my local machine and when I set hostname to "localhost", it tries to connect local database rather than remote. In fact
ssh -L 5432:localhost:5432 userFromAboveHost@host.with.database.com
seems to have no effect.
Generally, db tries to establish connection with local machine postgres all the time. Local postgres listen on 5432. Any ideas how to solve that? I think that I'm going to achieve the goal but it's still far away... :(
-
@marcus74 said in Connect with database standing on host which requires SSH connection using Qt:
Any ideas how to solve that?
Use another port - you can't open a ssh tunnel on a port which is already in use.
-
Hi,
@Christian-Ehrlicher suggested to change the port on your local host side to avoid the clash no the two. You can also disable temporarily your local PostgreSQL during the testing.
-
Hi,
@Christian-Ehrlicher suggested to change the port on your local host side to avoid the clash no the two. You can also disable temporarily your local PostgreSQL during the testing.
-
@marcus74 said in Connect with database standing on host which requires SSH connection using Qt:
I've done it on screen and it didn't work as well
ssh -L 5000:localhost:22 userFromAboveHost@host.with.database.comNo, you're now connecting to port 22 on your database server
-
The procedure is pretty good (and officially) documented by PostgreSQL
-
@marcus74 said in Connect with database standing on host which requires SSH connection using Qt:
I've done it on screen and it didn't work as well
ssh -L 5000:localhost:22 userFromAboveHost@host.with.database.comNo, you're now connecting to port 22 on your database server
@Christian-Ehrlicher On screen code I tried in second line:
ssh -L 5000:localhost:5432 userFromAboveHost@host.with.database.com
and it gave above output.
-
Again hmm,, in the screenshot above you're using
server.using.tunnel.com
(not ```host.with.database.com``)?Edit: I mean you should try this SSH:
ssh -L 5000:host.with.database.com:5432 userFromAboveServer@server.using.tunnel.com
@hskoglund Again, I receive errors about not accepting TCP/IP connections. Maybe I should pass by some way a password in process object for
userFromAboveServer@server.using.tunnel.com
because in Windows cmd I am requested for password after doing this:
ssh -L 5000:host.with.database.com:5432 userFromAboveServer@server.using.tunnel.com
-
Yeah, if you're establishing the SSH tunnel via a command line, then there's no stable way to submit the password.
I think putty can be used in an automatic way, or, if you have a admin rights on server.using.tunnel.com you could create keys, so that no password is needed, more for example here -
Ok, after long perturbations I used program:
https://geekflare.com/create-port-listener-in-windows-or-linux/
and created listening port 5000, then ran Qt project and everything is fine.
Thanks for all your patience :)To conclude, passing passoword as I mentioned above is not mandatory.