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 7FAECD1DB2C for ; Tue, 9 Mar 2004 10:19:47 +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 79361-03 for ; Tue, 9 Mar 2004 06:19:47 -0400 (AST) Received: from vsmtp4.tin.it (unknown [212.216.176.150]) by svr1.postgresql.org (Postfix) with ESMTP id A70D5D1DAC4 for ; Tue, 9 Mar 2004 06:19:44 -0400 (AST) Received: from host150-26.pool8248.interbusiness.it (82.48.26.150) by vsmtp4.tin.it (7.0.019) (authenticated as silvanadimartino) id 402CD2F0004CCECE; Tue, 9 Mar 2004 11:19:29 +0100 From: Silvana Di Martino To: Stephan Szabo Subject: Re: pgcrypto and database encryption Date: Tue, 9 Mar 2004 11:20:59 +0000 User-Agent: KMail/1.5 Cc: pgsql-admin@postgresql.org References: <200403071613.03397.silvanadimartino@tin.it> <200403082105.48145.silvanadimartino@tin.it> <20040308135032.S79172@megazone.bigpanda.com> In-Reply-To: <20040308135032.S79172@megazone.bigpanda.com> MIME-Version: 1.0 Content-Disposition: inline Message-Id: <200403090856.58260.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/142 X-Sequence-Number: 12711 Alle 22:07, luned=EC 8 marzo 2004, Stephan Szabo ha scritto: > > I'm not sure (nobody can actually read the mind of our lawmakers) but I > > think 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). Yes, right! The DBAdmin would be authenticated and allowed to access _all_ = of=20 the databases living on a server and, as a consequence, the encryption syst= em=20 would decrypt and show him _all_ of the data in clear form. To avoid this, = we=20 would need an access control system able to distingush a DBAdmin from a=20 authorized operator and act consquentely. This could be done with pgcrypto= =20 and PL/PGSQL functions if we had access to the current login username from= =20 within a PL/PGSL function. If not, we could likely do it from within a C=20 function. In any case, we will have to implement the whole system. In any case, there will always be an intersection point among all user grou= ps:=20 the DBA(s). > > The user that connect to a database, must authenticate himself using > > Ident, 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. While pgcrypto can work at a column level, our hypothetical system will wor= k=20 at a database level (because its dependence from the PostgreSQL=20 authentication system). In order to be able to allow the access to a=20 table/column of a db, while forbidding the access to another table/access o= f=20 the same db, we should be able to read the privileges of the current user a= nd=20 act consequently. I'm not sure but I think there is not any way to read the= =20 privileges of the current user from within a PL/PGSQL or C function so this= =20 would be impossible. > > This is almost the same technique used by Oracle. To implement it, > > PostgreSQL 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. I agree. > > Note that the trusted administrator _can_ be replaced by a server proce= ss > > if: - the client process that asks for a "global server password" can > > authenticat 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. I like this approach much more than any other. A "password server" based on= =20 HTTP, SOAP or XML-RPC (and protected by SSL or SSH) would be an interesting= =20 general purpose tool per se. Its use with PostgreSQL would "just" require t= o=20 add to the RDBMS engine the ability to communicate with the password server= =20 via HTTP, SOAP or XML-RPC. That is: another interesting general purpose=20 feature of the RDBMS engine per se. > 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. Yes, right... unfortunatley. We would need a way to tell the system to alwa= ys=20 backup _encrypted_ data and restore them without re-encrypting. A specific= =20 couple of programs like pg_enc_dump and pg_enc_restore would be needed. The= =20 internals of such programs could be managed in this way: - always backup (download) data in their original format (encrypted if they= =20 are encrypted, clear if they are clear) - from within your SQL dump file, when needed, signal to the RDBMS engine t= hat=20 we are restoring an encrypted DB. This should be used by SQL code (pgcrypto= =20 functions) to prevent a second, inopportune encryption of encrypted data. I= =20 think that either a SQL query or a specific, hypothetical "sql file header"= =20 variable could be used for this task. Again, a specific set of encrypted data types, each including the intellige= nce=20 needed to handle this conditional encryption, will be of great help. > > Specific encrytped data types are coming out to be a real need. Without > > them, 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 the PostgreSQL developers with the intent to develop a > > standard set of reliable 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. I agree. This could be a good starting point. Pgcrypto could be used as a= =20 basis for this code, apparently. See you. ----------------------------------------- Alessandro Bottoni and Silvana Di Martino alessandrobottoni@interfree.it silvanadimartino@tin.it