QPSQL (PostgreSQL): checking servers certificate?

[Mobility]

Hi,

After trying out SQLite and MS SQL Server and figuring out the actual needs that my program has, I selected PostgreSQL to go forward with. I've got it (temporarily) installed on Windows and I managed to compile the QPSQL driver with help of some posts of this forum. The next step was to establish SSL secured connection to the data base from my app using following functions:

db.setDatabaseName("test");
    db.setHostName("localhost");
    db.setUserName("tester");
    db.setPassword("****");
    db.setPort(5432);
    db.setConnectOptions("requiressl=1");
   if (!db.open()) {
         QMessageBox::critical(0, tr("Cannot open database"),
                 tr("Unable to establish a database connection.\n"
                    ), QMessageBox::Cancel);
         return;
    }

db.setDatabaseName("test");
    db.setHostName("localhost");
    db.setUserName("tester");
    db.setPassword("****");
    db.setPort(5432);
    db.setConnectOptions("requiressl=1");
   if (!db.open()) {
         QMessageBox::critical(0, tr("Cannot open database"),
                 tr("Unable to establish a database connection.\n"
                    ), QMessageBox::Cancel);
         return;
    }

To copy to clipboard, switch view to plain text mode 

So far so good. Connection is established and SQL commands work as expected. However, I was wondering that how to check the servers certificates? Isn't there some function for that? I know that the connection is ciphered even without checking the certificate, but it would good also to know that my app is not connecting to some middle-man. I know checking the certificate is possible if one uses SSL Sockets, but what about with QPSQL?

Second question: I have enabled the SSL by modifying the postgresql.conf file and as I said the connection succeeds with SSL. But how to force it to use SSL in the server side? Now it accepts both connection requests using SSL and those that are not using SSL. Not sure if this is important, because in my app I'll use the "requiressl=1" option anyway and it does not matter whether some unwanted connection attempts use SSL or not - I think...

Cheers!

[ChrisW67]

If the server certificate is not valid, i.e. cannot be verified against a CA trusted by the client, an SSL connection will not be established in the first place. If you want to test this try giving the server a self-signed certificate: unless you tell the client to trust that certificate as a CA a connection attempt will fail.

AFAICT limiting server connections to SSL only is done with an entry in pg_hba.conf like:

// For any user from any host trying to access your DB via TCP/IP
hostnossl  yourDBName  all  all  reject 
// or for all databases
hostnossl  all  all  all  reject

// For any user from any host trying to access your DB via TCP/IP
hostnossl  yourDBName  all  all  reject 
// or for all databases
hostnossl  all  all  all  reject

To copy to clipboard, switch view to plain text mode 

See section 19.1 of the PostgreSQL manual for (much) more

[Mobility]

Thanks! I got the connections now to be limited to support only SSL, other connection requests are rejected.

About the certificate. I'm actually using at this point a self-signed certificate created by myself. With SSL Socket the same certificate is not accepted unless I use ptr_socket->ignoreSslErrors(); but now I have not done anything else than the lines I added into the first post. I would expect failure in the db.open().

According to this: http://www.postgresql.org/docs/devel...libpq-ssl.html , I understand I should somehow get the sslmode parameter set to "verify-ca". Is this even possible with QPSQL?

[ChrisW67]

Try:

db.setConnectOptions("sslmode=verify-ca");
// or 
db.setConnectOptions("sslmode=verify-full");

db.setConnectOptions("sslmode=verify-ca");
// or 
db.setConnectOptions("sslmode=verify-full");

To copy to clipboard, switch view to plain text mode 

before you open the connection (requiressl is deprecated according to your manual). Regardless of the Qt docs listing a small set of "supported" options, these values are simply passed to the underlying database client as-is.

[Mobility]

Yes, got it working now. Difference between requiressl=1 and sslmode=verify-ca is that with the first one you don't need root certificate file but if it exists, it will behave similar than sslmode=verify-ca.

Thanks for you time!