Re: Getting the type Oid in a CREATE TYPE output function - Mailing list pgsql-hackers

Marko Kreen wrote:
> On 10/16/06, Weslee Bilodeau <weslee.bilodeau@hypermediasystems.com> wrote:
>> Marko Kreen wrote:
>> > The PGP functions happen to do it already - pgp_key_id().
>>
>> Actually, Tom helped me realize I made a mistake, which I'm following
>> his suggestion. Not tying keys to OIDs which change when backup/restored.
> 
> Yeah, tying to oids is bad, you should link to names,
> preferably schema-qualified.  Anyway, that was just off-hand
> suggestion.
> 
> 
>> [ snip nice description ]
> 
> 
>> I'm not sure if anyone else needs something like it, but it allows us to
>> transparently encrypt data directly in the tables. Minimum application
>> changes ('select enc_key' at connection) - the main requirement when
>> working on legacy code that needs to match todays security polices
>> quickly.
> 
> Some want row-level access control, then your scheme would not be enough.

Right now once I have it working, at least in the column-level keys I'm
looking for, I can worry about this.

My main concern at having multiple keys on the same column is the
ability to index it.

Right now you can index the encrypted values, however you can only do
exact matches to use the index.

IE -

select * from table where credit_card = '1111';

That would work, as it encrypts the '1111' then compares the encrypted
values in the index.

By having multiple keys, I can't do that.

Aside from breaking the index, you can't do any comparisons at all anymore.

Because '1111' is encrypted then compared.
If credit_card was encrypted using key1, and '1111' is encrypted using
key2, there can be no match, even if the unencrytped values match.

You'd have to decrypt everything and then compare, which is a large
performance hit.

Also, I like the ability to do this -

insert into table ( credit_card ) values ( '1111' ) ;

And its automatically encrypted. To support multiple keys, there has to
be a way to switch the keys.

Something like -

select enc_key( 'key1' );
insert into table ( credit_card ) values ( '1111' ) ;

select enc_key( 'key2' );
insert into table ( credit_card ) values ( '2222' ) ;

Its possible, just a bit messy.

The other thing is when doing -

select * from table;

If you only specified key1, you can view the '1111', but if you don't
specify the key2, what is displayed as it doesn't have the decryption
key for '2222'?

Right now it errors out if attempting to view a record you haven't
provided the key for. In this case you need to give all keys before you
can do the select.



> 
> Maybe it would be better to avoid combining the keys, instead have
> hidden key in database and several user keys that grant access to that
> key, thus you can revoke access from only some users.

I like more security. Its a combined two keys for the basic idea that -

The database itself does not have the key. If you hack into the server,
you will not be able to decrypt the values.

The application itself does not have the key.

If you hack the application and have access to enc_raw_read() (this
allows backups to work - selects the raw-encrypted values), you can't
decrypt the values.

Basically you call enc_key( 'application_key' ), the encrypt function is
called with a basic database_key || application_key, ensuring both parts
are always needed.

Thus backups are secure, and the application is a little more secure.

For things like storing credit cards, SSNs, personal information, this
allows us to say if you run off with the server, you can't do anything.

By storing the key in the database as you suggest and just giving access
to that key, any DBA can decrypt the values, and anyone who runs off
with a backup can as well. Your only securing the SQL-access, not the
backups. Which, more often its backups that are stolen, lost, etc.

> 
> But one thing I suggest strongly - use PGP encryption instead
> of old encrypt()/decrypt().  PGP hides the data much better,
> espacially in case of lot of small data with same key.
> 
> 

I may look into PGP once I get this part working.
Smaller steps for me, I'm new to postgres internal API. :)

Weslee