Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Postgresql force SSL connection
Forum Updated to NodeBB v4.3 + New Features

Postgresql force SSL connection

Scheduled Pinned Locked Moved General and Desktop
13 Posts 3 Posters 10.1k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M Offline
    M Offline
    mbnoimi
    wrote on 11 Jul 2013, 16:17 last edited by
    #1

    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
    1 Reply Last reply
    0
    • C Offline
      C Offline
      ChrisW67
      wrote on 12 Jul 2013, 03:40 last edited by
      #2

      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?

      1 Reply Last reply
      0
      • M Offline
        M Offline
        mbnoimi
        wrote on 12 Jul 2013, 11:32 last edited by
        #3

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

        1 Reply Last reply
        0
        • C Offline
          C Offline
          ChrisW67
          wrote on 12 Jul 2013, 22:31 last edited by
          #4

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

          1 Reply Last reply
          0
          • M Offline
            M Offline
            mbnoimi
            wrote on 12 Jul 2013, 22:41 last edited by
            #5

            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");@

            1 Reply Last reply
            0
            • C Offline
              C Offline
              ChrisW67
              wrote on 13 Jul 2013, 06:37 last edited by
              #6

              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.

              1 Reply Last reply
              0
              • M Offline
                M Offline
                mbnoimi
                wrote on 13 Jul 2013, 13:12 last edited by
                #7

                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]

                1 Reply Last reply
                0
                • C Offline
                  C Offline
                  ChrisW67
                  wrote on 13 Jul 2013, 23:23 last edited by
                  #8

                  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?

                  1 Reply Last reply
                  0
                  • M Offline
                    M Offline
                    mbnoimi
                    wrote on 16 Jul 2013, 13:09 last edited by
                    #9

                    [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!

                    1 Reply Last reply
                    0
                    • M Offline
                      M Offline
                      mbnoimi
                      wrote on 16 Jul 2013, 13:13 last edited by
                      #10

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

                      1 Reply Last reply
                      0
                      • M Offline
                        M Offline
                        mbnoimi
                        wrote on 18 Jul 2013, 17:20 last edited by
                        #11

                        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?

                        1 Reply Last reply
                        0
                        • M Offline
                          M Offline
                          mbnoimi
                          wrote on 6 Aug 2013, 13:13 last edited by
                          #12

                          May you please help me to fix this issue guys?

                          1 Reply Last reply
                          0
                          • V Offline
                            V Offline
                            vaychick
                            wrote on 15 Oct 2014, 06:06 last edited by
                            #13

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

                            1 Reply Last reply
                            0

                            • Login

                            • Login or register to search.
                            • First post
                              Last post
                            0
                            • Categories
                            • Recent
                            • Tags
                            • Popular
                            • Users
                            • Groups
                            • Search
                            • Get Qt Extensions
                            • Unsolved