Re: pgcrypto and database encryption - Mailing list pgsql-admin

Alle 22:07, lunedì 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
the databases living on a server and, as a consequence, the encryption system
would decrypt and show him _all_ of the data in clear form. To avoid this, we
would need an access control system able to distingush a DBAdmin from a
authorized operator and act consquentely. This could be done with pgcrypto
and PL/PGSQL functions if we had access to the current login username from
within a PL/PGSL function. If not, we could likely do it from within a C
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 groups:
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 work
at a database level (because its dependence from the PostgreSQL
authentication system). In order to be able to allow the access to a
table/column of a db, while forbidding the access to another table/access of
the same db, we should be able to read the privileges of the current user and
act consequently. I'm not sure but I think there is not any way to read the
privileges of the current user from within a PL/PGSQL or C function so this
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 process
> > 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
HTTP, SOAP or XML-RPC (and protected by SSL or SSH) would be an interesting
general purpose tool per se. Its use with PostgreSQL would "just" require to
add to the RDBMS engine the ability to communicate with the password server
via HTTP, SOAP or XML-RPC. That is: another interesting general purpose
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 always
backup _encrypted_ data and restore them without re-encrypting. A specific
couple of programs like pg_enc_dump and pg_enc_restore would be needed. The
internals of such programs could be managed in this way:
- always backup (download) data in their original format (encrypted if they
are encrypted, clear if they are clear)
- from within your SQL dump file, when needed, signal to the RDBMS engine that
we are restoring an encrypted DB. This should be used by SQL code (pgcrypto
functions) to prevent a second, inopportune encryption of encrypted data. I
think that either a SQL query or a specific, hypothetical "sql file header"
variable could be used for this task.
Again, a specific set of encrypted data types, each including the intelligence
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
basis for this code, apparently.

See you.
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
alessandrobottoni@interfree.it
silvanadimartino@tin.it