Обсуждение: Getting the type Oid in a CREATE TYPE output function ..
I'm trying to create a few new types, and based on the type in/out functions will operate a bit differently. For the input function finding the type Oid is easy - Oid our_type_oid = PG_GETARG_OID(1); For output though I'm having difficulty finding out the type Oid. I've tried using getBaseType, get_rel_type_id, and get_typ_typrelid. Maybe I'm using the options wrong? Or not looking in the right place? I'm only interested in getting it working on 8.1 or greater, so if it changes in older versions I'm not as concerned. Any help is appreciated. Weslee
Weslee Bilodeau <weslee.bilodeau@hypermediasystems.com> writes:
> I'm trying to create a few new types, and based on the type in/out
> functions will operate a bit differently.
> For the input function finding the type Oid is easy -
> Oid our_type_oid = PG_GETARG_OID(1);
> For output though I'm having difficulty finding out the type Oid.
You can't, and if you could, relying on it would be a security hole
in your function (somebody could invoke the function manually and pass
it a false OID value). You have to put everything you need to know
right into the Datum.
regards, tom lane
Tom Lane wrote: > Weslee Bilodeau <weslee.bilodeau@hypermediasystems.com> writes: >> I'm trying to create a few new types, and based on the type in/out >> functions will operate a bit differently. >> For the input function finding the type Oid is easy - >> Oid our_type_oid = PG_GETARG_OID(1); >> For output though I'm having difficulty finding out the type Oid. > > You can't, and if you could, relying on it would be a security hole > in your function (somebody could invoke the function manually and pass > it a false OID value). You have to put everything you need to know > right into the Datum. I'm not as worried about them running it manually, since I want it to operate something like - select output_function( 'test'::text ); It would have the type OID for text. select output_function( 'test'::varchar ); It would have the type OID for varchar. I don't want them to tell me the OID they want, I just want to know what type the function was called with. Was it called as a varchar, text, my own type, bytea, etc ? Is this possible? A bit of what I'm trying to do - I'm creating an encrypted data type wrapped around pgcrypto. create table test ( test enctype ); insert into test values ( 'encrypt_me' ); The value in input is encrypted, then stored using byteain. The key used to encrypt it is based of the type. So I can use the same functions for 10 different CREATE TYPE statements. The output function descrypts the value, then hands it off to byteaout. 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. The long way around is just create a new function for each type, but that seems messy since at least input can tell what type the input Datum is. Was hoping output can figure out the Datum type so I can decrypt it. Basically - create table test ( card enctype_card, addrress enctype_address ); Both types have different encryption keys. I know its best to encrypt in the application, and they can log the SQL on the server, or if your not using SSL it can be read, etc. Can't change the application to encrypt or use pgcrypto directly. Weslee
Weslee Bilodeau <weslee.bilodeau@hypermediasystems.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.
regards, tom lane
On 10/12/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Weslee Bilodeau <weslee.bilodeau@hypermediasystems.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(). -- marko
Marko Kreen wrote:
> On 10/12/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Weslee Bilodeau <weslee.bilodeau@hypermediasystems.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
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.
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.
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.
--
marko
On Tue, Oct 17, 2006 at 04:34:35PM +0300, Marko Kreen wrote:
> >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.
>
> 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.
>
> 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.
Better yet, allow the user to plug in encryption modules. Different
people want different kinds of encryption. For example, I believe credit
card companies require AES192.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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
Jim C. Nasby wrote:
> On Tue, Oct 17, 2006 at 04:34:35PM +0300, Marko Kreen wrote:
>>> 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.
>>
>> 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.
>>
>> 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.
>
> Better yet, allow the user to plug in encryption modules. Different
> people want different kinds of encryption. For example, I believe credit
> card companies require AES192.
As its really just a type wrapper around automatically calling
pgcrypto's encrypt/decrypt functions, this should be very easy to do.
I currently default it to 'bf' (blowfish) I can just make the type
creator have an additional parameter that takes any method recognized by
the crypto library.
Weslee