Thread: postgresql.key secure storage
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.
> 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
>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.
> 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
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
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
>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.
>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.
"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
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/
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 ?
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/
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
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/
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.
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"
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