Thread: postgresql.key secure storage

postgresql.key secure storage

From
"Saleem EDAH-TALLY"
Date:
Hello,

This concerns use of postgresql.key private key file on the client side.

psql can't establish a connection. with an encrypted postgresql.key file. If
I'm wrong here, the following is invalid and please show me the steps I'm
ignoring.

An application using libpq would require that the private unencrypted key be
deployed to the end user, together with the public key and trust cert. This
would mean if the end user is curious enough and computer litterate, he can
bypass the client application and make a direct connection to the server with
psql for example. It's then possible to issue commands like TRUNCATE TABLE...

Can we establish a SSL connection using an encrypted private key in PG 8.4 via
libpq ? I have not found any connection parameters allowing this.

Can the use of password protected wallets or PKCS12 certs become an option for
PG devs ? The end user would have a wallet on disk, which could be opened by
libpq using a hard-coded password(I know hard coding is evil but it yet can
help) and then use the certs and keys.

Thank you for considering.



Re: postgresql.key secure storage

From
Adam Tauno Williams
Date:
> An application using libpq would require that the private unencrypted key be
> deployed to the end user, together with the public key and trust cert. This
> would mean if the end user is curious enough and computer litterate, he can
> bypass the client application and make a direct connection to the server with
> psql for example. It's then possible to issue commands like TRUNCATE TABLE...

Sorry, I don't know anything about postgresql.key;  but regardless of
how you identify and authenticate you need to secure your database from
rogue user actions.

A user must have the TRUNCATE privilege to truncate a table or be the
tables owner.

OGo=> truncate table person;
ERROR:  must be owner of relation person



Re: postgresql.key secure storage

From
"Saleem EDAH-TALLY"
Date:
>A user must have the TRUNCATE privilege to truncate a table or be the
>tables owner.

Well the TRUNCATE example I mentioned is perhaps not explicit of what I meant
to say. A user who can modify data in a client application can also modify
data if he connects directly to the database, bypassing the client
application, with commands like 'UPDATE tbl SET col = value' Even if a few
rows are concerned, data is yet inconsistent. The only way to prevent this is
by preventing a direct access to the sever via a client like psql for example.
With or without use of SSL, it is not possible, unless I'm missing something.



Re: postgresql.key secure storage

From
Adam Tauno Williams
Date:
> An application using libpq would require that the private unencrypted key be
> deployed to the end user, together with the public key and trust cert. This
> would mean if the end user is curious enough and computer litterate, he can
> bypass the client application and make a direct connection to the server with
> psql for example. It's then possible to issue commands like TRUNCATE TABLE...

Sorry, I don't know anything about postgresql.key;  but regardless of
how you identify and authenticate you need to secure your database from
rogue user actions.

A user must have the TRUNCATE privilege to truncate a table or be the
tables owner.

OGo=> truncate table person;
ERROR:  must be owner of relation person



Re: postgresql.key secure storage

From
John R Pierce
Date:
Saleem EDAH-TALLY wrote:
> This concerns use of postgresql.key private key file on the client side.
>
> psql can't establish a connection. with an encrypted postgresql.key file. If
> I'm wrong here, the following is invalid and please show me the steps I'm
> ignoring.
>
> An application using libpq would require that the private unencrypted key be
> deployed to the end user, together with the public key and trust cert. This
> would mean if the end user is curious enough and computer litterate, he can
> bypass the client application and make a direct connection to the server with
> psql for example. It's then possible to issue commands like TRUNCATE TABLE...
>
sounds like you should be using a 3-tier architecture, where the
application running on the untrusted user computer isn't allowed to
directly connect to the database at all, rather, connects to middleware
on a secured computer which executes all business logic and makes any
database connections.

of course, this moves the security issues to your client->server
application protocol

Re: postgresql.key secure storage

From
Marc Munro
Date:
On Sun, 2009-09-13 at 12:04 -0300, pgsql-general-owner@postgresql.org
wrote:

> >A user must have the TRUNCATE privilege to truncate a table or be the
> >tables owner.
>
> Well the TRUNCATE example I mentioned is perhaps not explicit of what
> I meant
> to say. A user who can modify data in a client application can also
> modify
> data if he connects directly to the database, bypassing the client
> application, with commands like 'UPDATE tbl SET col = value' Even if a
> few
> rows are concerned, data is yet inconsistent. The only way to prevent
> this is
> by preventing a direct access to the sever via a client like psql for
> example.
> With or without use of SSL, it is not possible, unless I'm missing
> something.

If I understand you correctly, you want to allow a to use an application
that has certain database rights without allowing that same user to have
general database access through, say, psql.

The usual way of doing this, is to have the application run on a
separate application server from the one the user has direct access to.
You can then ensure that the database only allows connections from the
application server.

The user cannot gain knowledgde of the account used by the application
server without breaking into that machine, and if the user somehow
"guesses" the authentication details they will still be unable to use
psql as connections from their own machine would be denied.

I suspect that the problem you are trying to deal with is more complex
than you have actually explained.  If the answers you have received so
far don't help, you'll have to provide some more clues.

__
Marc

Re: postgresql.key secure storage

From
"Saleem Edah-Tally"
Date:
>a separate application server

Well this can be a solution in a trustworthy and friendly environment, on
which I can't count.

I would have been more at ease if libpq could manage a PKCS12 cert. or some
secure wallet/keystore that contains both the public and private keys for SSL
traffic. Neither the end user nor any admin would have to provide the password
to access the keys inside the secured storage as I would have prefered to
hard-code the password. Hard coding is not an elegant solution I agree, but
leaving on the table an unencrypted private key is not something to do IMO.

Any way, thank you for the feedback which has been instructive.




Re: postgresql.key secure storage

From
"Saleem Edah-Tally"
Date:
>a separate application server

Well this can be a solution in a trustworthy and friendly environment, on
which I can't count.

I would have been more at ease if libpq could manage a PKCS12 cert. or some
secure wallet/keystore that contains both the public and private keys for SSL
traffic. Neither the end user nor any admin would have to provide the password
to access the keys inside the secured storage as I would have prefered to
hard-code the password. Hard coding is not an elegant solution I agree, but
leaving on the table an unencrypted private key is not something to do IMO.

Any way, thank you for the feedback which has been instructive.



Re: postgresql.key secure storage

From
Tom Lane
Date:
"Saleem Edah-Tally" <nmset@netcourrier.com> writes:
> I would have been more at ease if libpq could manage a PKCS12 cert. or some
> secure wallet/keystore that contains both the public and private keys for SSL
> traffic. Neither the end user nor any admin would have to provide the password
> to access the keys inside the secured storage as I would have prefered to
> hard-code the password. Hard coding is not an elegant solution I agree, but
> leaving on the table an unencrypted private key is not something to do IMO.

You realize, of course, that there is absolutely no way you can stop the
user from extracting whatever data you put on his machine.  "Secure
wallet" is an exercise in self-delusion.  If you want actual security,
there had better be a machine that *you* control in the way.

            regards, tom lane

Re: postgresql.key secure storage

From
Sam Mason
Date:
On Mon, Sep 14, 2009 at 09:40:47AM +0200, Saleem Edah-Tally wrote:
> >a separate application server
>
> Well this can be a solution in a trustworthy and friendly environment, on
> which I can't count.

There must be some mis-communication going on; the above is how things
tend to be done on the Internet (e.g. you have code in the web server
that hands off known "good" queries to the database) and the Internet
certainly isn't a "trustworthy and friendly environment".

> I would have been more at ease if libpq could manage a PKCS12 cert. or some
> secure wallet/keystore that contains both the public and private keys for SSL
> traffic. Neither the end user nor any admin would have to provide the password
> to access the keys inside the secured storage as I would have prefered to
> hard-code the password. Hard coding is not an elegant solution I agree, but
> leaving on the table an unencrypted private key is not something to do IMO.

As Tom said; if this is implemented in software on a conventional OS
then this can *never* work.  Even if you start using a smart card to do
the crypto things don't get any better, how can you ensure that only the
"right" libpq process is talking to the card.

You need something you trust in the middle, and the conventional answer
to this is another server running code that only you control.  The PKI
stuff in libpq is about preventing man-in-the-middle attacks *between*
libpq and the PG server.  Your problem is that you don't trust the code
*calling* libpq and I can't think how PKI would help here.

--
  Sam  http://samason.me.uk/

Re: postgresql.key secure storage

From
"Saleem EDAH-TALLY"
Date:
Le Monday 14 September 2009 16:13:45, vous avez écrit :
> "Secure
> wallet" is an exercise in self-delusion.

Not really. How can a user extract data from a container, by whatever name we
call it, if he does not have the key to open it ?

Could you please instruct how to achieve this ?



Re: postgresql.key secure storage

From
Sam Mason
Date:
On Mon, Sep 14, 2009 at 05:45:14PM +0200, Saleem EDAH-TALLY wrote:
> Le Monday 14 September 2009 16:13:45, vous avez écrit :
> > "Secure
> > wallet" is an exercise in self-delusion.
>
> Not really. How can a user extract data from a container, by whatever
> name we call it, if he does not have the key to open it ?
>
> Could you please instruct how to achieve this ?

Exactly the same way that libpq does--debuggers are powerful tools!

--
  Sam  http://samason.me.uk/

Re: postgresql.key secure storage

From
Tom Lane
Date:
Sam Mason <sam@samason.me.uk> writes:
> On Mon, Sep 14, 2009 at 05:45:14PM +0200, Saleem EDAH-TALLY wrote:
>> Le Monday 14 September 2009 16:13:45, vous avez �crit :
>>> "Secure wallet" is an exercise in self-delusion.
>>
>> Not really. How can a user extract data from a container, by whatever
>> name we call it, if he does not have the key to open it ?

> Exactly the same way that libpq does--debuggers are powerful tools!

Or even easier, modify the source code of libpq to print out the data
after it's extracted it.  Security in an open-source world requires
a different set of tools than security in a closed-source world.

            regards, tom lane

Re: postgresql.key secure storage

From
Sam Mason
Date:
On Mon, Sep 14, 2009 at 12:17:55PM -0400, Tom Lane wrote:
> Sam Mason <sam@samason.me.uk> writes:
> > On Mon, Sep 14, 2009 at 05:45:14PM +0200, Saleem EDAH-TALLY wrote:
> >> How can a user extract data from a container, by whatever
> >> name we call it, if he does not have the key to open it ?
>
> > Exactly the same way that libpq does--debuggers are powerful tools!
>
> Or even easier, modify the source code of libpq to print out the data
> after it's extracted it.

Yup, I suppose you could even modify libpq to rewrite the "good" SQL
into whatever the attackers wants--bypassing any secret based scheme
completely.

> Security in an open-source world requires
> a different set of tools than security in a closed-source world.

Strictly speaking, a debugger is the universal mallet :)

Also, it shouldn't change much.  Security through obscurity is never
good, it is employed far too often though thankfully (a bit) less in
open-source programs.

--
  Sam  http://samason.me.uk/

Re: postgresql.key secure storage

From
"Saleem EDAH-TALLY"
Date:
OK guys, I would never have thought about modifying libpq to steal confidential
data, and I have never used debuggers in this respect at all.

So super gurus can yet do the bad thing.

Nevertheless 99% of users are not super gurus who could do such nasty things
but a few of them could use an unencrypted private key. These few at least
would have been frustrated if libpq could manage an encrypted private key. The
server can manage such a key and the admin starting the server is prompted for
the password. Ironically, it is generally accepted that it's better that the
server private key be unencrypted so that any admin can start the server
anytime.

Thank you.


Re: postgresql.key secure storage

From
Christopher Browne
Date:
nmset@netcourrier.com ("Saleem EDAH-TALLY") writes:
> OK guys, I would never have thought about modifying libpq to steal confidential
> data, and I have never used debuggers in this respect at all.
>
> So super gurus can yet do the bad thing.

Since this thread was published, anyone capable of writing a bit of C
code into libpq (which wasn't written to be opaque to development) can
readily search for this message thread and discover the approach.

> Nevertheless 99% of users are not super gurus who could do such nasty things
> but a few of them could use an unencrypted private key. These few at least
> would have been frustrated if libpq could manage an encrypted private key. The
> server can manage such a key and the admin starting the server is prompted for
> the password. Ironically, it is generally accepted that it's better that the
> server private key be unencrypted so that any admin can start the server
> anytime.

If we added such an interface into libpq, then it would become a
valuable thing for someone to add a libpq "extension" to capture the
data in unencrypted form, and publish it publicly enough to make it
accessible to rather more than the "1% of supergurus."

If you're trying to design something, intending it to be
tamper-resistant, then you *have* to consider the "supergurus,"
particularly because they might blaze a trail for others to follow...
--
(reverse (concatenate 'string "ofni.sailifa.ac" "@" "enworbbc"))
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"

Re: postgresql.key secure storage

From
David Fetter
Date:
On Mon, Sep 14, 2009 at 07:39:47PM +0200, Saleem EDAH-TALLY wrote:
> OK guys, I would never have thought about modifying libpq to steal
> confidential data, and I have never used debuggers in this respect
> at all.
>
> So super gurus can yet do the bad thing.

It doesn't take any kind of guru to use something someone else has
written, as rootkits and other malware amply demonstrate.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate