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 8F3DED1DB53 for ; Sun, 7 Mar 2004 15:28:13 +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 63209-09 for ; Sun, 7 Mar 2004 11:28:15 -0400 (AST) Received: from vsmtp2.tin.it (unknown [212.216.176.142]) by svr1.postgresql.org (Postfix) with ESMTP id 14112D1DB0C for ; Sun, 7 Mar 2004 11:28:11 -0400 (AST) Received: from host235-9.pool80182.interbusiness.it (80.182.9.235) by vsmtp2.tin.it (7.0.019) (authenticated as silvanadimartino) id 4043D1B50010499F for pgsql-admin@postgresql.org; Sun, 7 Mar 2004 16:28:13 +0100 From: Silvana Di Martino To: pgsql-admin@postgresql.org Subject: pgcrypto and database encryption Date: Sun, 7 Mar 2004 16:13:03 +0000 User-Agent: KMail/1.5 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200403071613.03397.silvanadimartino@tin.it> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/103 X-Sequence-Number: 12672 Going on with my evaluation of pgcrypto as a possible solution for our (EU) privacy law problems, I have to report the following facts: 1) pgcrypto (understandably) supplies just a small collections of server-side functions, not a general-purpose database-encryption system. 2) This means that pgcrypto does not supply you with any password management tool. All pgcrypto functions expect to receive the password from the calling SQL SELECT code as a parameter. 3) This means that you have to manage by yourself all the details of the password one-time entry at the RDBMS start-up time, its storing (in RAM, only) and its passage to the SQL SELECTs. IMHO, this is still far to satisfy our need for a standard and reliable solution to our "privacy law and DB encryption" problem. 4) What could actually solve our problem is something like the following scenario. Imagine that postmaster (or pg_ctrl) could accept a new CL parameter called "pw". This parameter would contain a sequence of comma-separated databasename/encryption-password pairs. I mean, something like this: postmaster -i -pw=postnuke:"arriba!",phpnuke:"blade runner" Imagine that postmaster stores these passwords in memory (and only in memory) as database-specific and database-wide string variables. Now, pgcrypto functions could read the required password directly from memory (like an environment variable). In this way, we could have an encrypted database and a simple way to manage its passwords. Any comment? 5) There is also a problem related to what pgcrypto can encrypt and what it cannot. For example: pgcrypto encrypt functions cannot be applied to DATE and TIME data types because it would mess up them and make them unacceptable by the RDBMS engine. We would need specific encrypted data types like ENCDATA and ENCTIME to handle these cases. Any comment? PS: I apologize for bothering americans with these all-EU details but it looks like this mailing list is the only one I can use for communicating with others EU pgsql-admins affected by this problem. ----------------------------------------- Alessandro Bottoni and Silvana Di Martino alessandrobottoni@interfree.it silvanadimartino@tin.it