X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1F3F3D1CA83 for ; Mon, 8 Mar 2004 21:15:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 20447-03 for ; Mon, 8 Mar 2004 17:15:01 -0400 (AST) Received: from vsmtp3.tin.it (vsmtp3alice.tin.it [212.216.176.143]) by svr1.postgresql.org (Postfix) with ESMTP id 8BDECD1C4ED for ; Mon, 8 Mar 2004 17:14:58 -0400 (AST) Received: from host173-9.pool80182.interbusiness.it (80.182.9.173) by vsmtp3.tin.it (7.0.019) (authenticated as silvanadimartino) id 402CD18F00497C28; Mon, 8 Mar 2004 22:14:20 +0100 From: Silvana Di Martino To: Stephan Szabo Subject: Re: pgcrypto and database encryption Date: Mon, 8 Mar 2004 22:12:22 +0000 User-Agent: KMail/1.5 Cc: pgsql-admin@postgresql.org References: <200403071613.03397.silvanadimartino@tin.it> <200403080810.44226.silvanadimartino@tin.it> <20040308063356.F70555@megazone.bigpanda.com> In-Reply-To: <20040308063356.F70555@megazone.bigpanda.com> MIME-Version: 1.0 Content-Disposition: inline Message-Id: <200403082105.48145.silvanadimartino@tin.it> Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/134 X-Sequence-Number: 12703 Alle 15:08, luned=EC 8 marzo 2004, Stephan Szabo ha scritto: > That's what I figured, but given a system where the password is given on > startup to the database, you would need another layer for the security, > have you though about how that would work? A not terrible option would be > to compose the key from something given by the user (perhaps at session > start with a query) with something already stored, but I don't know if the > law would allow that. > Also, there's a question about multiple databases in one instance that > might want separate encryption esp where the authorized users might be > non-overlapping sets. It might be better to allow a human to use a query > to initialize the passwords. I'm not sure (nobody can actually read the mind of our lawmakers) but I thi= nk=20 that using the same "password's password" (aka "global server password") fo= r=20 all of the databases managed by a single instance of the PostgreSQL server= =20 would be fine if we are able to guarantee that each user has his own=20 password.=20 So, we should have this scenario: - a single "global server password", supplied just once at the server start= -up=20 by a trusted admin via SSH/SSL and used to encrypt/decrypt all other=20 passwords. This password should never be stored on disk/db on the same host= =20 as the RDBMS. It must be encrypted and kept in volatile memory (RAM), only.= =20 If the server is shut down, the trusted admin will have to supply it again. - a set of database-specific passwords used to encrypt/decrypt data. These= =20 passwords could be encrypted using the "global server password" and safely= =20 stored into a table of the database. If we use strong encryption and a long= =20 password (Blowfish or Rijandel with a 512-1024 char-long key) to encrypt th= e=20 keys it should be very hard (almost impossible) to crack them. The user that connect to a database, must authenticate himself using Ident,= =20 SSH or Kerberos. Once accepted the user's connection, a set of server-side= =20 function (pgcrypto) encrypt/decryp data using the database-specific key. This is almost the same technique used by Oracle. To implement it, PostgreS= QL=20 just lacks a way to supply the "global server password" at start-up. A SQL= =20 Query cannot be used for this both because the password must be visible to= =20 all the database managed by a server and because this password must not be= =20 stored inside the db. Note that the trusted administrator _can_ be replaced by a server process i= f: - the client process that asks for a "global server password" can authentic= at=20 itself in a reliable way (SSH, Kerberos, SSH any other PKI) as a trusted=20 PostgreSQL server. - the communication channel is encrypted (SSH/SSL) > As above, automatic runs into questions about who can do it and maint > issues, like if encryption happens on insert and the admin cannot see the > decrypted form, the admin cannot backup the database using something like > pg_dump. This is probably best solved by doing whatever is necessary to > make the admin authorized. Well, data encryption is meant _also_ to protect data from the admin's eye.= As=20 long as I can see, there is no need to access or decrypt data for performin= g=20 backup, restore and any other database-level operation. From the admin poin= t=20 of view, the database can and should be a black box. > It should be reasonably easy to make types that took input and encrypted > it and had say appropriate output function that checked and decrypted > along with implicit casts that do the same thing. It'd be CPU consuming, > but workable. Here you run into the question of how it's configured > again. Specific encrytped data types are coming out to be a real need. Without the= m,=20 I cannot see how we could perform data sorting, comparison and any other=20 basic operation. IMHO, this topic should be brought to the attention of the= =20 PostgreSQL developers with the intent to develop a standard set of reliable= =20 encrypted data types. I'm sure that performance would be hugely affected by= =20 encryption but, again, this is out of our reach: law imposes it. ----------------------------------------- Alessandro Bottoni and Silvana Di Martino alessandrobottoni@interfree.it silvanadimartino@tin.it