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 20DCED1BB5F for ; Mon, 8 Mar 2004 22:07:38 +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 30685-09 for ; Mon, 8 Mar 2004 18:07:37 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 64E9FD1DAC4 for ; Mon, 8 Mar 2004 18:07:33 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 245C535184; Mon, 8 Mar 2004 14:07:17 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 226453505A; Mon, 8 Mar 2004 14:07:17 -0800 (PST) Date: Mon, 8 Mar 2004 14:07:17 -0800 (PST) From: Stephan Szabo To: Silvana Di Martino Cc: pgsql-admin@postgresql.org Subject: Re: pgcrypto and database encryption In-Reply-To: <200403082105.48145.silvanadimartino@tin.it> Message-ID: <20040308135032.S79172@megazone.bigpanda.com> References: <200403071613.03397.silvanadimartino@tin.it> <200403080810.44226.silvanadimartino@tin.it> <20040308063356.F70555@megazone.bigpanda.com> <200403082105.48145.silvanadimartino@tin.it> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/138 X-Sequence-Number: 12707 On Mon, 8 Mar 2004, Silvana Di Martino wrote: > 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 que= ry > > to initialize the passwords. > > I'm not sure (nobody can actually read the mind of our lawmakers) but I t= hink > that using the same "password's password" (aka "global server password") = for > all of the databases managed by a single instance of the PostgreSQL server > would be fine if we are able to guarantee that each user has his own > password. Except that you then need someone who is authorized to view all the databases correct since that user would potentially be able to get all the keys? That still seems to miss the case where the sets of authorized users have no intersections (which might come up in shared hosting). > The user that connect to a database, must authenticate himself using Iden= t, > SSH or Kerberos. Once accepted the user's connection, a set of server-side > function (pgcrypto) encrypt/decryp data using the database-specific key. I think there might still be issues with people who have access to some (presumably non-encrypted) data in the database while not having access to the encrypted data unless the SQL permissions system were sufficient. > This is almost the same technique used by Oracle. To implement it, Postgr= eSQL > just lacks a way to supply the "global server password" at start-up. A SQL > Query cannot be used for this both because the password must be visible to > all the database managed by a server and because this password must not be > stored inside the db. I think C functions with access to a shared memory block wouldn't fail those two considerations. > Note that the trusted administrator _can_ be replaced by a server process= if: > - the client process that asks for a "global server password" can authent= icat > itself in a reliable way (SSH, Kerberos, SSH any other PKI) as a trusted > PostgreSQL server. > - the communication channel is encrypted (SSH/SSL) This sounds alot better all in all. This also seems like it might be possible to prototype outside the database proper so as to get an idea of what it would look like. > > 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 t= he > > decrypted form, the admin cannot backup the database using something li= ke > > 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 ey= e. As > long as I can see, there is no need to access or decrypt data for perform= ing > backup, restore and any other database-level operation. From the admin po= int > of view, the database can and should be a black box. But, given the fact that the form of the backup is a set of SQL statements, if the encryption is automatic on input, either the SQL statements contain encrypted data and you need some way to on restore tell the system to not encrypted them again or they contain decrypted data. If accessing the encrypted data without being properly authorized throws an error rather than giving you the encrypted form, how does the admin do the selects necessary on the table to even get the encrypted form. > > 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 t= hem, > I cannot see how we could perform data sorting, comparison and any other > basic operation. IMHO, this topic should be brought to the attention of t= he > PostgreSQL developers with the intent to develop a standard set of reliab= le > encrypted data types. I'm sure that performance would be hugely affected = by Given that it's possible that the types might be an input function, an output function and one or more type conversion functions, I'd think it'd be possible to prototype them first.