Connect with database standing on host which requires SSH connection using Qt

marcus74

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.

Christian Ehrlicher

@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

hskoglund

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

marcus74

@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

Hmm in the screenshot above it's ""userFromAboveServer" not ""userFromAboveHost"?

marcus74

@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... :(

Christian Ehrlicher

@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.

marcus74

Still does not work.

ssh -L 5000:localhost:22 userFromAboveHost@host.with.database.com

have no effect too.

SGaist

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

@SGaist I've done it on screen and it didn't work as well

Christian Ehrlicher

@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.com

No, you're now connecting to port 22 on your database server

hskoglund

The procedure is pretty good (and officially) documented by PostgreSQL

marcus74

@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.

hskoglund

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

marcus74

@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

hskoglund

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

marcus74

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.