Обсуждение: PosgreSQL Security Architecture
All;
I'm working to secure a PosgreSQL database according to a DoD security guide. It has many very generic requirements that get more toward the internal architecture of the system that wouldn't be apparent to the average admin. I was hoping someone might have some insight to the following requirements:On 02/11/2016 08:30 AM, Lesley Kimmel wrote: > All; > > I'm working to secure a PosgreSQL database according to a DoD security > guide. It has many very generic requirements that get more toward the > internal architecture of the system that wouldn't be apparent to the > average admin. I was hoping someone might have some insight to the > following requirements: > > a) The DBMS must maintain the authenticity of communications sessions by > guarding against man-in-the-middle attacks that guess at Session ID values. > > b) Check DBMS settings and vendor documentation to verify the DBMS > properly handles transactions in the event of a system failure. The > consistent state must include a security configuration that is at least > as restrictive as before the system failure. This must be guaranteed. Might want to take a look at these threads: http://www.postgresql.org/message-id/CAKd4e_EXeMp2+DLqeZc=fFCtZ74vL4wVUvavYEM2_-HJu63PsQ@mail.gmail.com http://www.postgresql.org/message- id/CAKd4e_G6xA22C+Sc0QnrLLs03kM1fOPgUNLjymtyRxK64e=VuA@mail.gmail.com > > Thanks in advance, > -LJK -- Adrian Klaver adrian.klaver@aklaver.com
Lesley Kimmel wrote: > I'm working to secure a PosgreSQL database according to a DoD security guide. It has many very generic > requirements that get more toward the internal architecture of the system that wouldn't be apparent to > the average admin. I was hoping someone might have some insight to the following requirements: > > > a) The DBMS must maintain the authenticity of communications sessions by guarding against man-in-the- > middle attacks that guess at Session ID values. You can have that if you use SSL encryption which is available in PostgreSQL: http://www.postgresql.org/docs/current/static/ssl-tcp.html It uses that widely-used OpenSSL software, so an encrypted database connection is as safe from man-in-the-middle attacks as OpenSSL is. > b) Check DBMS settings and vendor documentation to verify the DBMS properly handles transactions in > the event of a system failure. The consistent state must include a security configuration that is at > least as restrictive as before the system failure. This must be guaranteed. I don't understand what is meant by "security configuration" here. Is that defined somewhere? PostgreSQL handles system failures well, it uses a "Write Ahead Log" (WAL) to record transactions as they get committed. In the case of a system failure, the recovery process starts at the latest checkpoint (known consistent state) before the failure and exactly replays all WAL logged committed transactions up to the point of the crash: http://www.postgresql.org/docs/current/static/wal-intro.html After crash recovery, the database is in the same state as it was after the last successful transaction. The (unrecovered) database files of a crashed database are no less secure than any file system backup is. Yours, Laurenz Albe
Thanks for the reply Laurenz. Of course the first thing that I thought of to prevent man-in-the-middle was SSL. However, I also like to try to address the issue in a way that seems to get at what they are intending. It seemed to me that they wanted to do some configuration within the database related to session IDs.
Regarding what is meant by "security configuration", I couldn't say for sure. These guides are very much open to interpretation. In any case your answers are helpful. Thanks again!On Fri, Feb 12, 2016 at 3:02 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Lesley Kimmel wrote:
> I'm working to secure a PosgreSQL database according to a DoD security guide. It has many very generic
> requirements that get more toward the internal architecture of the system that wouldn't be apparent to
> the average admin. I was hoping someone might have some insight to the following requirements:
>
>
> a) The DBMS must maintain the authenticity of communications sessions by guarding against man-in-the-
> middle attacks that guess at Session ID values.
You can have that if you use SSL encryption which is available in PostgreSQL:
http://www.postgresql.org/docs/current/static/ssl-tcp.html
It uses that widely-used OpenSSL software, so an encrypted database connection
is as safe from man-in-the-middle attacks as OpenSSL is.
> b) Check DBMS settings and vendor documentation to verify the DBMS properly handles transactions in
> the event of a system failure. The consistent state must include a security configuration that is at
> least as restrictive as before the system failure. This must be guaranteed.
I don't understand what is meant by "security configuration" here.
Is that defined somewhere?
PostgreSQL handles system failures well, it uses a "Write Ahead Log" (WAL) to record
transactions as they get committed. In the case of a system failure, the
recovery process starts at the latest checkpoint (known consistent state) before the
failure and exactly replays all WAL logged committed transactions up to the point of
the crash:
http://www.postgresql.org/docs/current/static/wal-intro.html
After crash recovery, the database is in the same state as it was after the last
successful transaction.
The (unrecovered) database files of a crashed database are no less secure than
any file system backup is.
Yours,
Laurenz Albe
On 2/12/2016 5:20 AM, Lesley Kimmel wrote: > Thanks for the reply Laurenz. Of course the first thing that I thought > of to prevent man-in-the-middle was SSL. However, I also like to try > to address the issue in a way that seems to get at what they are > intending. It seemed to me that they wanted to do some configuration > within the database related to session IDs. when the connection is broken, the process exits and the session ceases to exist. there are no 'session IDs' to speak of (they are process IDs instead, but a new process mandates new authentication, there's no residual authorizations associated with a PID). -- john r pierce, recycling bits in santa cruz
John R Pierce wrote: > On 2/12/2016 5:20 AM, Lesley Kimmel wrote: >> Thanks for the reply Laurenz. Of course the first thing that I thought >> of to prevent man-in-the-middle was SSL. However, I also like to try >> to address the issue in a way that seems to get at what they are >> intending. It seemed to me that they wanted to do some configuration >> within the database related to session IDs. > > when the connection is broken, the process exits and the session ceases > to exist. there are no 'session IDs' to speak of (they are process > IDs instead, but a new process mandates new authentication, there's no > residual authorizations associated with a PID). I might be misunderstanding, but is there any connection to a man-in-the-middle attack? Without SSL, anybody who can tap into the TCP communication can inject SQL statements. No session ID is required. Yours, Laurenz Albe
LJK, Firstly thanks for bring this topic up. As many companies have to handle attacks on their network. Adrian thanks for the links specially the one covering connection pooling in the first link. Answered by Pavel Stehule . Definitely something to avoid. I have done a fair bit of work on security issues from Ecommerce perspective. Hope these comment might be of some value. Please avoid giving guarantees on security issues. Rather try to give a level of confidence based on current understandingof known attacks, their purpose, potential solutions, their costs and mitigating issues and very importantlyperformance issues. These days specially on ecommerce. Search engines penalise site's ranking based on their performance.So a site could be very secure but it could end up losing business. Can I break this question up a little bit. * Repeated security reports indicate that by far the most serious security breaches are insider jobs or lack of adherenceto proper internal procedures. * DDOS attacks tend to have two aims. Simply stopping the website or causing stack over flow. Hoping to catch the serveron the hop. These are not DB engines job and are best handled elsewhere before the transaction reaches the DB engine. * Man in the middle is a big problem. Too often under estimated. As an example. I live in UK. Tracing a Tcp/Ip transaction going from a UK location to a UK site can end up going to anEastern block country then US before returning back to UK. Last I heard. AOL's (or now talk talk) traffic still goes tooutside UK before returning back to UK. Mitigating potential actions of man in the middle is a collective job of the O/S, network layer, firewall and the DB. *Several years ago there was a research done where during authentication process the tcp/ip traffic was intercepted by afake man in the middle and fed back to the server with the result of creating a new session. (sorry don't recall the O/Sor SSL they used) The potential solution lies at the network layer not the DB engine. *OpenSSL. For a small outlay of few hundred dollars it is safer to have an higher over security of the system by securingthe communication layer. I normally use a mid-cost company like Comodo's SSL service. They tie the server, ip addressto SSL certificate. *Personally I use a customised encryption techniques on the database. In the event of a failure or security breach at saya cloud computing partner. These will be a lot more difficult to crack but also easier to recover. My past experienceis that often DB engines (not postgresql) find it difficult to recover encrypted fields that were encrypted by*DB engine itself*. Please bear in mind this has an impact on searching and indexing and performance so be careful butcan be resolved. *Logs files needs to be just as secure as the main db files. *Lastly but certainly not least. Adrian raised a very important point in his post about use of applications for connectionpooling. It goes without saying that all application installed on the server needs to be checked for potentialsecurity issues but Good luck. I hope these might be of some use to you and others. Farjad Farid -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Albe Laurenz Sent: 15 February 2016 10:48 To: 'John R Pierce *EXTERN*'; pgsql-general@postgresql.org Subject: Re: [GENERAL] PosgreSQL Security Architecture John R Pierce wrote: > On 2/12/2016 5:20 AM, Lesley Kimmel wrote: >> Thanks for the reply Laurenz. Of course the first thing that I >> thought of to prevent man-in-the-middle was SSL. However, I also like >> to try to address the issue in a way that seems to get at what they >> are intending. It seemed to me that they wanted to do some >> configuration within the database related to session IDs. > > when the connection is broken, the process exits and the session ceases > to exist. there are no 'session IDs' to speak of (they are process > IDs instead, but a new process mandates new authentication, there's no > residual authorizations associated with a PID). I might be misunderstanding, but is there any connection to a man-in-the-middle attack? Without SSL, anybody who can tap into the TCP communication can inject SQL statements. No session ID is required. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Fri, Feb 12, 2016 at 5:20 AM, Lesley Kimmel <lesley.j.kimmel@gmail.com> wrote: > Thanks for the reply Laurenz. Of course the first thing that I thought of to > prevent man-in-the-middle was SSL. However, I also like to try to address > the issue in a way that seems to get at what they are intending. It seemed > to me that they wanted to do some configuration within the database related > to session IDs. As far as server configuration, you can configure your server to only accept ssl connections in pg_hba.conf. You should also configure your clients to do sslmode=verify-full on the ssl certificate sent to them by the server (which is not the default). Cheers, Jeff