Re: Getting the type Oid in a CREATE TYPE output function

Marko Kreen wrote:
> On 10/12/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Weslee Bilodeau <weslee(dot)bilodeau(at)hypermediasystems(dot)com> writes:
>> > It works perfectly so long as I used the same key for all my custom
>> > types. When I want a different key for each type though (so for
>> example,
>> > encrypt credit cards with one key, addresses with another, etc) I
>> need a
>> > way to tell them apart.
>>
>> [ shrug... ] Seems like you should be putting the key ID into the
>> stored encrypted datums, then.
>
> 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.

But actually for me, the key ID is not a PGP key.

When you create a new "type" you create a key ID, and map that key ID to
the OID attached to that type, it stores a hashed password value in a
little far-off place that it can use to ensure all inserts into that
same type are using the exact same encryption key (the key is actually
only half, the database has its own key. It combines the two keys to
encrypt/decrypt data).

Having the same column encrypted with 20 different keys is a bit of a
mess. So I just needed a way to ensure it was the same key with each
INSERT/UPDATE.

At login, you call -

SELECT enc_key( 'type', 'password' );

Returns "OK" if its the real key for that type, otherwise returns an
error with "Invalid Key" and refuses all read/writes (SELECT, INSERT,
UPDATE, etc) to those types, as it would if you never called enc_key()
in the first place.

If anyone else is curious I'll release the code once I have it actually
working. A few more days basically.

Allows things like -

-- Create the new type, just hides all the "CREATE TYPE"
-- and assigns the key to the type
select enc_type_new( 'enc_cardnumber', 'new_password' );

create table credit_card (
card_number enc_cardnumber not null,
card_name varchar(20) not null
);

insert into credit_card values ( '1234', 'test' ) ;

Login again -

select * from credit_card ;

ERROR: Please provide key

select enc_key( 'enc_cardnumber', 'new_password' );

SELECT * from credit_card ;

1234 | test

And yes, you can back it up. Map a user to be able to read/write raw
encrypted values and it allows backup/restores using pg_(dump|restore).

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.

Weslee