Postgresql force SSL connection



  • I'm trying to force SSL connection to Postgresql. First I configured my database server as following:
    @# pg_hba.conf
    local all postgres peer
    local all all peer
    host all all 127.0.0.1/32 md5
    hostnossl all all 0.0.0.0/0 reject
    hostssl all all 0.0.0.0/0 md5@

    Then configured Qt connection as following:
    @int main(int argc, char *argv[])
    {
    QCoreApplication a(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
    db.setHostName("192.168.0.74");
    db.setPort(5433);
    // sslmode=disable to use TCP/IP
    //db.setConnectOptions("sslmode=require");
    db.setDatabaseName("testDB");
    db.setUserName("postgres");
    db.setPassword("***");
    if (!db.open())
        qDebug() << "Unable to connect!";
    else
        qDebug() << "connected.";
    
    return a.exec(&#41;;
    

    }@

    But it could connect the database?!!!

    P.S.

    • I created SSL certificate and enabled SSL on server side.
    • I'm using Postgresql 9.1


  • I assume you mean it could not connect to the database.
    What does QSqlDatabase::lastError() tell you?
    Does the connection to that server work without the SSL requirement?



  • [quote author="ChrisW67" date="1373600409"]I assume you mean it could not connect to the database.[/quote]
    No, it can connect although I commented the 9th line!
    I want to prevent connection without SSL requirement.



  • So the problem is that the PostgreSQL server is accepting connections that are not secured by SSL. This is not a Qt problem.



  • but it will not connect in case I use:
    @db.setConnectOptions("sslmode=disable");@

    So I wonder Does Qt use SSL by default? means I don't need to use:
    @db.setConnectOptions("sslmode=require");@



  • Qt generally will not use SSL on any TCP/IP connection unless told to (and requires OpenSSL libraries installed). What the PostgreSQL client does by default is a matter for PostgreSQL, but I'd be very surprise dif it defaulted to SSL.

    What does QSqlDatabase::lastError() tell you?
    What does your PostgreSQL log tell you?

    We cannot diagnose a problem we cannot see.



  • Maybe I wasn't clear, Qt by default connects to SSL is this right behavior?
    @# pg_hba.conf
    local all postgres peer
    local all all peer
    host all all 127.0.0.1/32 md5
    hostnossl all all 0.0.0.0/0 reject
    hostssl all all 0.0.0.0/0 md5@

    [code]#include <QCoreApplication>
    #include <QtSql>
    #include <QDebug>

    int main(int argc, char *argv[])
    {
    QCoreApplication a(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
    db.setHostName("192.168.0.74");
    db.setPort(5433);
    // sslmode=disable to use TCP/IP
    // db.setConnectOptions("sslmode=disable");
    db.setDatabaseName("testDB");
    db.setUserName("postgres");
    db.setPassword("***");
    if (!db.open())
        qDebug() << db.lastError().text();
    else
        qDebug() << "connected.";
    
    return a.exec&#40;&#41;;
    

    }
    [/code]

    The output:
    [code]connected.[/code]

    but when I unmment line 13 the output becomes:
    [code]"FATAL: pg_hba.conf rejects connection for host "192.168.0.202", user "postgres", database "testDB", SSL off
    QPSQL: Unable to connect"[/code]



  • I have the 64-bit Linux binary distribution of Qt 5.1.
    I have your code with no setConnectOptions() calls.

    I have just installed PostgreSQL 9.2.4, changed nothing on the server except allowing all connections from my LAN:
    @
    local all all trust
    host all all 127.0.0.1/32 trust
    host all all ::1/128 trust
    host all all 192.168.1.0/24 md5
    @
    then watched the connection from the client to server using Wireshark. The PostgreSQL client attempts to use SSL and, because the server is not accepting it, reverts to in-the-clear.

    With the server configured for SSL with a self-signed certificate:
    @

    postgresql.conf

    ssl = on
    ssl_cert_file = '/etc/postgresql-9.2/server.crt"
    ssl_key_file = '/etc/postgresql-9.2/server.key'
    @
    the client was successful in connecting with the entire transaction encrypted.

    When I change pg_hba.conf to
    @
    hostnossl all all 192.168.1.0/24 reject
    hostssl all all 192.168.1.0/24 md5
    @
    The client continues to connect with SSL. If I specify:
    @
    db.setConnectOptions("sslmode=disable");
    @
    the client fails to connect at all.

    Does that answer your question?



  • [quote]With the server configured for SSL with a self-signed certificate:

    # postgresql.conf
    ssl = on
    ssl_cert_file = '/etc/postgresql-9.2/server.crt"
    ssl_key_file = '/etc/postgresql-9.2/server.key'
    

    [/quote]
    I've Postgresql issue with new SSL configurations because I've PG9.1 while your snippet uses PG9.2!



  • @ssl_cert_file, ssl_key_file@
    They aew new features in Postgresql 9.2



  • I upgraded my Postgresql to 9.2 and still get same behavior from Qt side it can connect to the server even if I don't add:
    @db.setConnectOptions("sslmode=require");@

    I want to be sure that my connection is safe by SSL so I tried to use Wireshark with these filters but I'm not from the result:
    [code]ip.dst == 192.168.0.74 && tcp.port==5433[/code]

    How can I be sure that Qt connects safely by SSL?



  • May you please help me to fix this issue guys?



  • [quote]//db.setConnectOptions("sslmode=require");

    But it could connect the database?!!![/quote]

    http://www.postgresql.org/docs/9.1/static/libpq-connect.html:

    sslmode

    This option determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server. There are six modes:
    

    disable - only try a non-SSL connection

    allow - first try a non-SSL connection; if that fails, try an SSL connection

    prefer (default) - first try an SSL connection; if that fails, try a non-SSL connection

    require - only try an SSL connection. If a root CA file is present, verify the certificate in the same way as if verify-ca was specified

    verify-ca - only try an SSL connection, and verify that the server certificate is issued by a trusted certificate authority (CA)

    verify-full - only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the server host name matches that in the certificate

    Driver uses "prefer" mode by default.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.