PosgreSQL Security Architecture

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:

/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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 understanding of known attacks, their purpose, potential solutions, their costs and mitigating issues and very importantly performance 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 adherence to proper internal procedures.
* DDOS attacks tend to have two aims. Simply stopping the website or causing stack over flow. Hoping to catch the server on 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 an Eastern block country then US before returning back to UK. Last I heard. AOL's (or now talk talk) traffic still goes to outside 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 a fake man in the middle and fed back to the server with the result of creating a new session. (sorry don't recall the O/S or 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 securing the communication layer. I normally use a mid-cost company like Comodo's SSL service. They tie the server, ip address to SSL certificate.
*Personally I use a customised encryption techniques on the database. In the event of a failure or security breach at say a cloud computing partner. These will be a lot more difficult to crack but also easier to recover. My past experience is 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 but can 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 connection pooling. It goes without saying that all application installed on the server needs to be checked for potential security 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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general