Обсуждение: ssl connections to postgresql
PostgreSQL 8.1.9 CentOS 4.5 I have setup postgresql to use ssl and have configured and restarted the service without error. I am connecting a web app from a remote httpd host and I would like to verify that the connection between these two machines is indeed employing ssl for the application in question. I can connect from the httpd host to the postgresql host using psql and it shows that an ssl connection with a 256 bit key is in use. However, I would like to verify that the web app is also using ssl and I cannot seem to find any logging setting or other mechanism that will explicitly state that the connection made by this application is using ssl. Is there some confidence check that I can invoke via a log setting or something else to have the postgresql server state with certainty that the connection between the app host and the db host is via ssl? I subscribe to the digest version so the favour of a direct reply in addition to any sent to this list is requested Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
James B. Byrne wrote:
> PostgreSQL 8.1.9
> CentOS 4.5
>
> I have setup postgresql to use ssl and have configured and restarted the
> service without error. I am connecting a web app from a remote httpd host
> and I would like to verify that the connection between these two machines
> is indeed employing ssl for the application in question.
just enforce hostssl in your pg_hba.conf and nothing else. If you can
connect, you are good :)
Joshua D. Drake
>
> I can connect from the httpd host to the postgresql host using psql and it
> shows that an ssl connection with a 256 bit key is in use. However, I
> would like to verify that the web app is also using ssl and I cannot seem
> to find any logging setting or other mechanism that will explicitly state
> that the connection made by this application is using ssl.
>
> Is there some confidence check that I can invoke via a log setting or
> something else to have the postgresql server state with certainty that the
> connection between the app host and the db host is via ssl?
>
> I subscribe to the digest version so the favour of a direct reply in
> addition to any sent to this list is requested
>
>
> Regards,
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
James B. Byrne wrote: > > I have setup postgresql to use ssl and have configured and > restarted the service without error. I am connecting a web app > from a remote httpd host and I would like to verify that > the connection between these two machines is indeed > employing ssl for the application in question. If you set log_min_messages and log_min_error_statement to debug5, you will get something like this in your log: 2007-07-04 10:20:24.348 CEST [unknown]@ (468b5848.48e:0@[unknown]) LOG: connection received: host=10.153.13.5 port=48360 2007-07-04 10:20:24.348 CEST @ (:@) DEBUG: forked new backend, pid=1166 socket=7 2007-07-04 10:20:24.420 CEST [unknown]@10.153.13.5(48360) (468b5848.48e:0@[unknown]) DEBUG: SSL connection from "(anonymous)" 2007-07-04 10:20:24.421 CEST erich@10.153.13.5(48360) (468b5848.48e:0@typo3) DEBUG: received password packet 2007-07-04 10:20:24.421 CEST erich@10.153.13.5(48360) (468b5848.48e:0@typo3) LOG: connection authorized: user=erich database=typo3 Yours, Laurenz Albe
On Wed, July 25, 2007 03:13, Albe Laurenz wrote: > James B. Byrne wrote: >> >> I would like to verify that the connection between these >> two machines is indeed employing ssl for the application in question. > > If you set log_min_messages and log_min_error_statement to debug5, > you will get something like this in your log: > > 2007-07-04 10:20:24.348 CEST [unknown]@ (468b5848.48e:0@[unknown]) LOG: > connection received: host=10.153.13.5 port=48360 > 2007-07-04 10:20:24.348 CEST @ (:@) DEBUG: forked new backend, pid=1166 > socket=7 > 2007-07-04 10:20:24.420 CEST [unknown]@10.153.13.5(48360) > (468b5848.48e:0@[unknown]) DEBUG: SSL connection from "(anonymous)" > 2007-07-04 10:20:24.421 CEST erich@10.153.13.5(48360) > (468b5848.48e:0@typo3) DEBUG: received password packet > 2007-07-04 10:20:24.421 CEST erich@10.153.13.5(48360) > (468b5848.48e:0@typo3) LOG: connection authorized: user=erich > database=typo3 > > Yours, > Laurenz Albe > Thank you. I infer that this setting would generate an awful lot of log entries regarding a wide range of process actions and that your intention is that it only be used temporarily to confirm a valid ssl configuration works as expected. I was actually hoping to find some way of logging the type of connection made without having to get excessive (for my purpose) details about other activities in unrelated ares of the server process. But, this is nonetheless valuable information. Thanks. Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
On Tue, July 24, 2007 18:29, Joshua D. Drake wrote: > > just enforce hostssl in your pg_hba.conf and nothing else. If you can > connect, you are good :) > > Joshua D. Drake Thanks, I will probably end up doing this. What I am really looking for is an audit trail for all DBM host connections to show the security compliance team that the network links are in fact secured. I call it a confidence check setting because that is really what it is, a statement in the logs to engender confidence in people who have limited knowledge of the detailed workings of the server process (which includes me at the moment). What is the process to make a suggestion to the pg maintainers to add a configurable logging option like this? Is there a way to use a key larger than 256 bits and is there any reason why this would not be useful in practice? Our standard key sizes here seem to by either 1024 or 2048. Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
James B. Byrne wrote: > On Wed, July 25, 2007 03:13, Albe Laurenz wrote: >> James B. Byrne wrote: >>> I would like to verify that the connection between these >>> two machines is indeed employing ssl for the application in question. >> If you set log_min_messages and log_min_error_statement to debug5, >> you will get something like this in your log: [snip] > Thank you. I infer that this setting would generate an awful lot of log > entries regarding a wide range of process actions and that your intention > is that it only be used temporarily to confirm a valid ssl configuration > works as expected. I was actually hoping to find some way of logging the > type of connection made without having to get excessive (for my purpose) > details about other activities in unrelated ares of the server process. > But, this is nonetheless valuable information. Thanks. You could probably do something like set the default log-level high and then override it for the database/users in question (alter database set ...) Of course, the simplest solution would be to change the code yourself. Shouldn't be more than a line or two, you just want to add one piece of information to an existing log line. That's also the simplest way to ask core to tweak the line too: I want to change line nnn in file fff to add the following... -- Richard Huxton Archonet Ltd
James B. Byrne wrote: > On Tue, July 24, 2007 18:29, Joshua D. Drake wrote: >> just enforce hostssl in your pg_hba.conf and nothing else. >> If you can connect, you are good :) > > Thanks, I will probably end up doing this. > > What I am really looking for is an audit trail for all DBM host > connections to show the security compliance team that the > network links are in fact secured. This is more a philosophical question. If you only allow hostssl connections in pg_hba.conf AND forbid all host connections (with one last 'reject' line), PostgreSQL will reject all connections that are not via SSL. If your "security compliance team" does not trust PostgreSQL to enforce that, they'll probably have a very bad feeling about PostgreSQL in general - why then should they trust a log entry that PostgreSQL writes? But I guess you're more after something that "looks good" to make your security guys happy, which I can understand... > What is the process to make a suggestion to the pg > maintainers to add a configurable logging option like this? Write to this group, as you did, I guess. This would in fact be interesting for database servers that are configured to use SSL, but do not enforce it. You can then see which incoming connections are encrypted and which not. Yours, Laurenz Albe
On Fri, July 27, 2007 04:20, Albe Laurenz wrote: > > This is more a philosophical question. > > If you only allow hostssl connections in pg_hba.conf AND forbid > all host connections (with one last 'reject' line), PostgreSQL > will reject all connections that are not via SSL. > > If your "security compliance team" does not trust PostgreSQL to > enforce that, they'll probably have a very bad feeling about PostgreSQL > in general - why then should they trust a log entry that PostgreSQL > writes? > Because configuration files can be expected to be modified over time and having an explicit log entry tells one what modes were in effect for a specific connection at the time, whatever the configuration file says now. It is not confidence in the software but in the diligence of the system administrator (me) that is at question for the audit team. In any case, I personally like these sort of direct confidence log entries. I feel that it makes for easier configuration changes as often you quickly can see the consequence when you have done something stupid. I think that if the maintainers decide it worth doing at all, and I simply do not have the time to bring myself up to speed on the code base of a project the size of postgresql to do it myself, then such a feature would be best added as a new special value (%e) option for log_line_prefix. # %e = connection encryption strength (none/ssl-256/ssl-512 etc.) Which again raises a question that I posed earlier: Is there any benefit to increasing the key size for a host connection from 256 and, if so, how is this done? Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
At 09:26 PM 7/26/2007, James B. Byrne wrote: >Is there a way to use a key larger than 256 bits and is there any reason >why this would not be useful in practice? Our standard key sizes here >seem to by either 1024 or 2048. Hi, There's a difference between a symmetric key, and a public key. 256 bit is decent for shared key, and too small for public key crypto. For symmetric (shared) key encryption, if 256 bit keys are not big enough it usually means the encryption is broken and you should be switching to something else ASAP. For public key encryption, you get sizes like 1024, 2048 or even 4096. The reason why these keys are bigger is because of the sort of math involved, e.g. for RSA the difficulty of cracking is related to the difficulty of finding the factors of large numbers. You do need quite large numbers to make things hard. Whereas cracking decent shared key crypto is more like trying all the combinations so it should be harder per bit, e.g. on average it should take you 2^255 tries to crack a 256 bit key. The advantage of public key encryption is you can freely give out the public key, you just need to keepthe private key secret. Whatever the public key encrypts, the private key can decrypt. Whatever the private key encrypts (signs) the public key can decrypt (verify). Whereas for shared key encryption, both sides need the same key. Public key crypto is quite slow, so it is usually used to transfer the smaller symmetric (shared) keys (which should be random and changing). Then the shared keys are used to encrypt the connections. You could probably refer to wikipedia or google for more details. Regards, Link.
At 05:13 AM 7/25/2007, James B. Byrne wrote: >I can connect from the httpd host to the postgresql host using psql and it >shows that an ssl connection with a 256 bit key is in use. However, I >would like to verify that the web app is also using ssl and I cannot seem >to find any logging setting or other mechanism that will explicitly state >that the connection made by this application is using ssl. The way to verify it is to look at the traffic (e.g. use tcpdump or ethereal or sniffer) and see what is actually happening. Using SSL is good, but if the network between the database and webserver is "fairly trusted/secured" in my opinion it is usually much easier to break into the webserver remotely than sniff traffic between a webserver and the database server remotely (unless you are doing something seriously wrong ;) ). If the situation really calls for SSL, you might as well use certs on both sides to prevent MITM attacks. Because usually once you get onto the webserver, whatever the webapp can do, you can do. The other way to get unauthorized access to data, is to steal/copy the database backup tapes, or work as a cleaner and stick strange little devices between the keyboards and the computers and fetch the passwords later. Lastly if an attacker is in a position to sniff traffic between your webserver and database server, you're in trouble already. Regards, Link.