Thread: Force ssl connection
Howdy, I want to prevent any Postgresql database connection other than SSL but it didn't work and my client can connect without SSL! * How can fix this issue? P.S. To force SSL connection I made the following steps: On server side (ubuntu 12.10 x64 - Postgresql 9.1) 1) Created server.key and server.crt 2) Modified pg_hba.conf and added "hostnossl all all 0.0.0.0/0 reject" 3) Modified postgresql.conf "ssl = on" 4) Restarted the server On client side (Qt 5.1) db.setDatabaseName("mydb"); db.setHostName("localhost"); db.setUserName("postgres"); db.setPassword("****"); db.setPort(5432); db.setConnectOptions("requiressl=1"); if (!db.open()) { qDebug() << "Unable to connect!"; } -- Best Regards, Muhammad Bashir Al-Noimi
On 07/08/2013 05:32 PM, Muhammad Bashir Al-Noimi wrote: > > Howdy, > > I want to prevent any Postgresql database connection other than SSL but > it didn't work and my client can connect without SSL! > > * How can fix this issue? > > P.S. To force SSL connection I made the following steps: > > On server side (ubuntu 12.10 x64 - Postgresql 9.1) > 1) Created server.key and server.crt > 2) Modified pg_hba.conf and added "hostnossl all all 0.0.0.0/0 reject" Actually something more along lines of hostssl all all 0.0.0.0/0 http://www.postgresql.org/docs/9.2/interactive/auth-pg-hba-conf.html hostssl This record matches connection attempts made using TCP/IP, but only when the connection is made with SSL encryption. To make use of this option the server must be built with SSL support. Furthermore, SSL must be enabled at server start time by setting the ssl configuration parameter (see Section 17.9 for more information). Also remember in pg_hba.conf first match wins, so if there is a more permissive entry before your restrictive one, the permissive one will take precedence. > 3) Modified postgresql.conf "ssl = on" > 4) Restarted the server > > On client side (Qt 5.1) > db.setDatabaseName("mydb"); > db.setHostName("localhost"); > db.setUserName("postgres"); > db.setPassword("****"); > db.setPort(5432); > db.setConnectOptions("requiressl=1"); > if (!db.open()) { > qDebug() << "Unable to connect!"; > } > -- Adrian Klaver adrian.klaver@gmail.com
On Tue, Jul 9, 2013 at 2:46 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > Also remember in pg_hba.conf first match wins, so if there is a more > permissive entry before your restrictive one, the permissive one will take > precedence. Thanks for reply but you just quted some line from the documentation which I already read it before posting in mailing list so this didn't help me out to fix the issue! So may you please be more specific, what's wrong in my configurations? My pg_hba.conf content is: local all postgres peer local all all peer host all all 127.0.0.1/32 md5 host all all ::1/128 md5 host all all 0.0.0.0/0 md5 hostssl all all 0.0.0.0/0 md5 hostnossl all all 0.0.0.0/0 reject -- Best Regards Muhammad Bashir Al-Noimi
On Tue, Jul 9, 2013 at 10:02 AM, Muhammad Bashir Al-Noimi <mbnoimi@gmail.com> wrote: > > So may you please be more specific, what's wrong in my configurations? > > My pg_hba.conf content is: > > local all postgres peer > local all all peer > host all all 127.0.0.1/32 md5 > host all all ::1/128 md5 > host all all 0.0.0.0/0 md5 > hostssl all all 0.0.0.0/0 md5 > hostnossl all all 0.0.0.0/0 reject The line below accepts all connections, whether ssl or nossl: host all all 0.0.0.0/0 md5 It takes precedence over the reject line, as it occurs in the file before the reject. If you remove that line, then you don't need the reject line at all. AFAICT, having a reject as the last line in the file is useless. Anything that has reached that point is going to be rejected anyway. Cheers, Jeff
On 07/09/2013 07:54 PM, Jeff Janes wrote: > On Tue, Jul 9, 2013 at 10:02 AM, Muhammad Bashir Al-Noimi > <mbnoimi@gmail.com> wrote: >> So may you please be more specific, what's wrong in my configurations? >> >> My pg_hba.conf content is: >> >> local all postgres peer >> local all all peer >> host all all 127.0.0.1/32 md5 >> host all all ::1/128 md5 >> host all all 0.0.0.0/0 md5 >> hostssl all all 0.0.0.0/0 md5 >> hostnossl all all 0.0.0.0/0 reject > The line below accepts all connections, whether ssl or nossl: > > host all all 0.0.0.0/0 md5 > > It takes precedence over the reject line, as it occurs in the file > before the reject. > > If you remove that line, then you don't need the reject line at all. I commented it and restarted the server but I still get same result! How can I absolutely be sure that my server rejects not ssl connections? -- Best Regards, Muhammad Bashir Al-Noimi
On Tue, Jul 9, 2013 at 1:55 PM, Muhammad Bashir Al-Noimi <mbnoimi@gmail.com> wrote: > On 07/09/2013 07:54 PM, Jeff Janes wrote: >> >> On Tue, Jul 9, 2013 at 10:02 AM, Muhammad Bashir Al-Noimi >> <mbnoimi@gmail.com> wrote: >>> >>> So may you please be more specific, what's wrong in my configurations? >>> >>> My pg_hba.conf content is: >>> >>> local all postgres peer >>> local all all peer >>> host all all 127.0.0.1/32 md5 >>> host all all ::1/128 md5 >>> host all all 0.0.0.0/0 md5 >>> hostssl all all 0.0.0.0/0 md5 >>> hostnossl all all 0.0.0.0/0 reject >> >> The line below accepts all connections, whether ssl or nossl: >> >> host all all 0.0.0.0/0 md5 >> >> It takes precedence over the reject line, as it occurs in the file >> before the reject. >> >> If you remove that line, then you don't need the reject line at all. > > I commented it and restarted the server but I still get same result! db.setHostName("localhost"); > > How can I absolutely be sure that my server rejects not ssl connections? > > > -- > Best Regards, > Muhammad Bashir Al-Noimi >
On 07/09/2013 01:55 PM, Muhammad Bashir Al-Noimi wrote: > On 07/09/2013 07:54 PM, Jeff Janes wrote: >> On Tue, Jul 9, 2013 at 10:02 AM, Muhammad Bashir Al-Noimi >> <mbnoimi@gmail.com> wrote: >>> So may you please be more specific, what's wrong in my configurations? >>> >>> My pg_hba.conf content is: >>> >>> local all postgres peer >>> local all all peer >>> host all all 127.0.0.1/32 md5 >>> host all all ::1/128 md5 >>> host all all 0.0.0.0/0 md5 >>> hostssl all all 0.0.0.0/0 md5 >>> hostnossl all all 0.0.0.0/0 reject >> The line below accepts all connections, whether ssl or nossl: >> >> host all all 0.0.0.0/0 md5 >> >> It takes precedence over the reject line, as it occurs in the file >> before the reject. >> >> If you remove that line, then you don't need the reject line at all. > I commented it and restarted the server but I still get same result! Where are you connecting from? If you are connecting locally using sockets(local above) or host(line 3,4,5 above) then you are bypassing ssl. > > How can I absolutely be sure that my server rejects not ssl connections? > Make sure you use only hostssl not host or local. hostssl forces ssl only connections. pg_hba.conf is powerful but the interactions can be somewhat confusing. It took me several passes through the docs before I began to understand. -- Adrian Klaver adrian.klaver@gmail.com
On Tue, Jul 9, 2013 at 1:55 PM, Muhammad Bashir Al-Noimi <mbnoimi@gmail.com> wrote: > On 07/09/2013 07:54 PM, Jeff Janes wrote: >> >> On Tue, Jul 9, 2013 at 10:02 AM, Muhammad Bashir Al-Noimi >> <mbnoimi@gmail.com> wrote: >>> >>> So may you please be more specific, what's wrong in my configurations? >>> >>> My pg_hba.conf content is: >>> >>> local all postgres peer >>> local all all peer >>> host all all 127.0.0.1/32 md5 >>> host all all ::1/128 md5 >>> host all all 0.0.0.0/0 md5 >>> hostssl all all 0.0.0.0/0 md5 >>> hostnossl all all 0.0.0.0/0 reject >> >> The line below accepts all connections, whether ssl or nossl: >> >> host all all 0.0.0.0/0 md5 >> >> It takes precedence over the reject line, as it occurs in the file >> before the reject. >> >> If you remove that line, then you don't need the reject line at all. > > I commented it and restarted the server but I still get same result! From your original email: db.setHostName("localhost"); So localhost is probably matching "127.0.0.1/32" or "::1/128", which are explicitly allowed. > How can I absolutely be sure that my server rejects not ssl connections? Delete or comment out every line of pg_hba.conf which you either don't want, or don't understand. You could move the reject line to the top of the file, but that is no substitute for understanding every line. Cheers, Jeff
On Tue, Jul 9, 2013 at 11:16 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > Where are you connecting from? If you are connecting locally using sockets(local above) or host(line 3,4,5 above) thenyou are bypassing ssl. I'm connecting from 192.168.0.74 and I commented line5 as following: local all postgres peer local all all peer host all all 127.0.0.1/32 md5 host all all ::1/128 md5 #local replication postgres peer #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5 #host all all 0.0.0.0/0 md5 hostnossl all all 0.0.0.0/0 reject hostssl all all 0.0.0.0/0 md5 -- Best Regards Muhammad Bashir Al-Noimi
On Tue, Jul 9, 2013 at 11:21 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > From your original email: > db.setHostName("localhost"); > > So localhost is probably matching "127.0.0.1/32" or "::1/128", which > are explicitly allowed. I'm using this and still able to connect without SSL db.setHostName("192.168.0.74"); db.setPort(5433); // set requiressl=1 to enable SSL db.setConnectOptions("requiressl=0"); db.setDatabaseName("testDB"); db.setUserName("postgres"); db.setPassword("****"); if (!db.open()) qDebug() << "Unable to connect!"; else qDebug() << "connected."; -- Best Regards Muhammad Bashir Al-Noimi
On Wed, Jul 10, 2013 at 12:04 PM, Muhammad Bashir Al-Noimi <mbnoimi@gmail.com> wrote: > On Tue, Jul 9, 2013 at 11:21 PM, Jeff Janes <jeff.janes@gmail.com> wrote: >> From your original email: >> db.setHostName("localhost"); >> >> So localhost is probably matching "127.0.0.1/32" or "::1/128", which >> are explicitly allowed. > > I'm using this and still able to connect without SSL If you want to make sure you can *never* connect without SSL, replace all entries of "host" with "hostssl". It makes no sense to require SSL over localhost, but if that's what you want (or just for testing), replace those too. > db.setHostName("192.168.0.74"); > db.setPort(5433); > // set requiressl=1 to enable SSL > db.setConnectOptions("requiressl=0"); requiressl=0 doesn't mean what you think it means, and that's one reason it has been deprecated since at least 8.2. requiressl=0 means "negotiate. use ssl if the server asks for it, but accept not using ssl". So this will connect without an error both with and without ssl. If you want to enforce ssl, use sslmode=require. If you want to enforce non-ssl, use sslmode=disable. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Wed, Jul 10, 2013 at 12:16 PM, Magnus Hagander <magnus@hagander.net> wrote: > requiressl=0 doesn't mean what you think it means, and that's one > reason it has been deprecated since at least 8.2. > > requiressl=0 means "negotiate. use ssl if the server asks for it, but > accept not using ssl". So this will connect without an error both with > and without ssl. > > If you want to enforce ssl, use sslmode=require. > If you want to enforce non-ssl, use sslmode=disable. This is exactly what I'm looking for... thanks a lot it works perfectly. -- Best Regards Muhammad Bashir Al-Noimi
On 07/10/2013 03:20 AM, Muhammad Bashir Al-Noimi wrote: > On Wed, Jul 10, 2013 at 12:16 PM, Magnus Hagander <magnus@hagander.net> wrote: >> requiressl=0 doesn't mean what you think it means, and that's one >> reason it has been deprecated since at least 8.2. >> >> requiressl=0 means "negotiate. use ssl if the server asks for it, but >> accept not using ssl". So this will connect without an error both with >> and without ssl. >> >> If you want to enforce ssl, use sslmode=require. >> If you want to enforce non-ssl, use sslmode=disable. > > > This is exactly what I'm looking for... thanks a lot it works perfectly. Which is what you had if you followed your own comments:) db.setHostName("192.168.0.74"); db.setPort(5433); // set requiressl=1 to enable SSL <-------- db.setConnectOptions("requiressl=0"); > > -- > Best Regards > Muhammad Bashir Al-Noimi > -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Jul 10, 2013 at 3:04 AM, Muhammad Bashir Al-Noimi <mbnoimi@gmail.com> wrote: > On Tue, Jul 9, 2013 at 11:21 PM, Jeff Janes <jeff.janes@gmail.com> wrote: >> From your original email: >> db.setHostName("localhost"); >> >> So localhost is probably matching "127.0.0.1/32" or "::1/128", which >> are explicitly allowed. > > I'm using this and still able to connect without SSL > > db.setHostName("192.168.0.74"); But that is just another spelling for 127.0.0.1, correct? Cheers, Jeff