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: Mon, 8 Mar 2004 07:08:06 -0800 (PST)
Message-ID: <20040308063356.F70555@megazone.bigpanda.com> (raw)
In-Reply-To: <200403080810.44226.silvanadimartino@tin.it>
References: <200403071613.03397.silvanadimartino@tin.it>
<20040307091719.P46697@megazone.bigpanda.com>
<200403080810.44226.silvanadimartino@tin.it>
On Mon, 8 Mar 2004, Silvana Di Martino wrote:
> Alle 17:40, domenica 7 marzo 2004, Stephan Szabo ha scritto:
> > 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.
>
> Yes: each "authorized operator" must be able to access data belonging to him,
> only, even if the database contains data belonging to many different
> operators. Moreover, SysAdmin (that are not "authorized operator") must not
> be able to access any data, even if they are able to access the database (as
> a whole) for maintenance. So, decryption must work for authorized operator
> only.
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 query
to initialize the passwords.
> > 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?
>
> Yes: we will need to perform basic operations on encrypted dates, times and so
> on. Of course, it would be great to be able to delegate the on-the-fly
> decryption/encryption work to the encryption system.
>
> > 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?
>
> Same as above: we would need to perform substring-wise operations and ordering
> on encrypted strings. The encryption/decryption mechanism should be
> automatic.
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 the
decrypted form, the admin cannot backup the database using something like
pg_dump. This is probably best solved by doing whatever is necessary to
make the admin authorized.
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.
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: <20040308063356.F70555@megazone.bigpanda.com>
References: <200403071613.03397.silvanadimartino@tin.it> <20040307091719.P46697@megazone.bigpanda.com> <200403080810.44226.silvanadimartino@tin.it> <20040308063356.F70555@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