From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Silvana Di Martino <silvanadimartino@tin.it>
Cc: pgsql-admin@postgresql.org
Subject: Re: pgcrypto and database encryption
Date: Sun, 7 Mar 2004 09:40:58 -0800 (PST)
Message-ID: <20040307091719.P46697@megazone.bigpanda.com> (raw)
In-Reply-To: <200403071613.03397.silvanadimartino@tin.it>
References: <200403071613.03397.silvanadimartino@tin.it>
On Sun, 7 Mar 2004, Silvana Di Martino wrote:
> 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?
Who has access to the unencrypted versions of the data? Are there people
who will have access to the database who might have access to some of the
encrypted data but not all or someone who has access to the database but
none of the encrypted data? If so, then you presumably need a system
where the decryption will only work for the appropriate people and that
needs to be built in.
Also, what manipulation do you want to do with these values in the
database? Are you going to need to subtract two encrypted dates or
determine that one is greater than the other? What about substring or
ordering for encrypted strings? Does this have to be automatic or is
explicit description of the fact that you want to decrypt them and then do
something sufficient?
Finally, there are some questions about reliability of the system as a
whole. In a system like the -pw above, where do those passwords come from,
is it from a human typing at a console? If so, you need to consider
downtime and recovery time issues based on who has access to the
passwords. This doesn't so much affect the law in question but it may
affect contracts that you have if they have downtime requirements.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: sszabo@megazone.bigpanda.com, silvanadimartino@tin.it
Subject: Re: pgcrypto and database encryption
In-Reply-To: <20040307091719.P46697@megazone.bigpanda.com>
References: <200403071613.03397.silvanadimartino@tin.it> <20040307091719.P46697@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox