Postgresql force SSL connection

mbnoimi

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

ChrisW67

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?

mbnoimi

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

ChrisW67

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

mbnoimi

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

ChrisW67

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.

mbnoimi

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]

ChrisW67

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?

mbnoimi

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

mbnoimi

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

mbnoimi

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?

mbnoimi

May you please help me to fix this issue guys?

vaychick

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