Re: [GENERAL] PosgreSQL Security Architecture

[Jeff Janes]

On Fri, Feb 12, 2016 at 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.

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

Re: [GENERAL] PosgreSQL Security Architecture

[FarjadFarid(ChkNet)]

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-ow...@postgresql.org 
[mailto:pgsql-general-ow...@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

Re: [GENERAL] PosgreSQL Security Architecture

[Albe Laurenz]

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

Re: [GENERAL] PosgreSQL Security Architecture

[Albe Laurenz]

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

Re: [GENERAL] PosgreSQL Security Architecture

[Lesley Kimmel]

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

Re: [GENERAL] PosgreSQL Security Architecture

[John R Pierce]

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

Re: [GENERAL] PosgreSQL Security Architecture

[Adrian Klaver]

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_-hju63...@mail.gmail.com

http://www.postgresql.org/message-
id/CAKd4e_G6xA22C+Sc0QnrLLs03kM1fOPgUNLjymtyRxK64e=v...@mail.gmail.com




Thanks in advance,
-LJK



--
Adrian Klaver
adrian.kla...@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