Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4FAD72E0071 for ; Fri, 21 Dec 2007 03:06:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 43601-05 for ; Fri, 21 Dec 2007 03:06:15 -0400 (AST) Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.184]) by postgresql.org (Postfix) with ESMTP id A14BA2E0073 for ; Fri, 21 Dec 2007 03:06:14 -0400 (AST) Received: by fk-out-0910.google.com with SMTP id b27so294012fka.3 for ; Thu, 20 Dec 2007 23:06:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=mBidSyg0EGIduSh8h6irS/e7UYPO/Xfk1jVWO9NliNM=; b=IDFn0Tw4PY9nDPtXQ30XrYv0odx4xmWqh6TyMlW01xTBufaYLsMnEnUPjH1XD+bFsBYb+I6PlxSI4a4L7yP+yVa7QHWt5JMLJzL0IDUM/GQaAFOHhrEMYZMiYJlFqrJoyjXRgYBpVtcayzsX2Hl4a3CmDUYbdFOOVzQGZvpb9KY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=iyhPfrfT7RYSTjlC+mtXeFNSF9RtZ5rfq+QTc7z64eWe961pK/XZ6AXxO1sVfrnRDdGeynuu/o1Ohpk0JSATwLwOa07UGOVZjRCQbecd11wuFLFmsEqPjpjaAD4N90NruL9JHNsgXnioQE9Y88Y+5CmSKQXDtgRO6ohae3ikdv8= Received: by 10.78.204.7 with SMTP id b7mr1123021hug.74.1198220772918; Thu, 20 Dec 2007 23:06:12 -0800 (PST) Received: by 10.78.18.2 with HTTP; Thu, 20 Dec 2007 23:06:12 -0800 (PST) Message-ID: Date: Fri, 21 Dec 2007 02:06:12 -0500 From: "Merlin Moncure" To: "Tom Lane" Subject: Re: function body actors (was: viewing source code) Cc: "Alvaro Herrera" , "Joshua D. Drake" , "Trevor Talbot" , "Roberts, Jon" , "Kris Jurka" , "Jonah H. Harris" , "Bill Moran" , "pgsql performance" , "Pgsql Hackers" In-Reply-To: <26811.1198215605@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <26811.1198215605@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/339 X-Sequence-Number: 28155 On Dec 21, 2007 12:40 AM, Tom Lane wrote: > "Merlin Moncure" writes: > > On Dec 20, 2007 6:01 PM, Tom Lane wrote: > >> So if you want something other than endless arguments to happen, > >> come up with a nice key-management design for encrypted function > >> bodies. > > > Maybe a key management solution isn't required. If, instead of > > strictly wrapping a language with an encryption layer, we provide > > hooks (actors) that have the ability to operate on the function body > > when it arrives and leaves pg_proc, we may sidestep the key problem > > (leaving it to the user) and open up the doors to new functionality at > > the same time. > > I think you're focusing on mechanism and ignoring the question of > whether there is a useful policy for it to implement. Andrew Sullivan > argued upthread that we cannot get anywhere with both keys and encrypted > function bodies stored in the same database (I hope that's an adequate > summary of his point). I'm not convinced that he's right, but that has > to be the first issue we think about. The whole thing is a dead end if > there's no way to do meaningful encryption --- punting an insoluble > problem to the user doesn't make it better. Well, there is no 'one size fits all' policy. I'm still holding out that we don't need any specific designs for this...simply offering the example in the docs might get people started (just thinking out loud here): create function encrypt_proc(proname text, prosrc_in text, prosrc_out out text) returns text as $$ declare key bytea; begin -- could be a literal variable, field from a private table, temp table, or 3rd party -- literal is dangerous, since its visible until 'create or replaced' but thats maybe ok, depending key := get_key(); select magic_string || encode(encrypt(prosrc_in, key, 'bf'), 'hex'); -- magic string prevents attempting to unencrypt non-encrypted functions. end; $$ language plpgsql; -- ordering of actors is significant...need to think about that alter language plpgsql add actor 'encrypt_proc' on input; alter language plpgsql add actor 'decrypt_proc' on output; If that's not enough, then you have build something more structured, thinking about who provides the key and how the database asks for it. The user would have to seed the session somehow (maybe, stored in a temp table?) with a secret value which would be translated into the key directly on the database or by a 3rd party over a secure channel. The structured approach doesn't appeal to me much though... The temp table idea might not be so hot, since it's trivial for the database admin to see data from other user's temp tables, and maybe we don't want that in some cases. need to think about this some more... merlin