Re: Blobs in Postgresql

Ron Johnson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 08/17/07 23:16, Merlin Moncure wrote:
>> On 8/18/07, Ron Olson <tachoknight(at)gmail(dot)com> wrote:
>>> The language is Java. I've made some tests and they work very well for 25meg
>>> files....works exactly the way it should, first time. MySQL had all kinds of
>>> nasty surprises for me when I first started working with blobs, but I can
>>> say that I took my code, changed the driver, and it all works like a champ
>>> (mind you, this was a quick test app).
>>>
>>> I haven't looked at encryption at the database level....is such a thing
>>> available? I know Oracle has some form of data encryption at the database
>>> level so the nefarious DBA with the wide mustache and black brimmed hat
>>> always going "ah ha ha ha ha" can't make off with the data, but does
>>> Postgres have something similar?
>>>
>>> BTW, to put into context, the database will be designed to hold evidence
>>> (well, photos and videos of). Thus the compelling need for some security, as
>>> well as the variation in file sizes.
>> Well, my assumption was that you would encrypt the data on the client
>> side and store it that way.
>>
>> PostgreSQL has open architecture. If you wanted to do the encryption
>> on the server, one possible approach that jumps out at me is to write
>> a small C function which receives the data, encrypts the image using a
>> key sent by the client all (but not stored), and either stores the
>> encrypted image back in the database via SPI or writes it out to a
>> file.
>>
>> There are many strategies to encrypting data...first thing to think
>> about is where the encryption happens, where the keys are stored, etc.
>
> Client-side encryption is important, because with server-side
> encryption, you are sending the Valuable Data across the wire (or,
> even worse!) wireless in cleartext form.
>
> It's more likely that there's a packet sniffer on the network than
> an Evil DBA snooping around.
>

The two options I see are -

1. the client encrypts the data and sends it to the DB

2. the client uses an SSL connection to the server to prevent snooping
and lets the DB encrypt for storage.

I would suggest looking at pgcrypto in contrib for server side encryption.

The main benefit I would see from the first is it doesn't matter if
another DB admin changes the server security settings or not. The new
guy may setup a new server and not enforce SSL connections. Of course if
the client refused non-SSL connections you can prevent that.

Either way the app provides the key to decrypt the data for viewing, so
the developers current and future must maintain the security level you
choose.

What sort of security measures are taken for viewing the data? Will each
user have a security certificate on their own USB flash drive to allow
them to view the data? which could also prevent developers from
accessing the data. Or is their password enough to allow the program to
decrypt it for them?

It would really come down to which encryption method you find easiest to
implement that provides enough security for your needs.

--

Shane Ambler
pgSQL(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz