Learn EDB: Basic Encryption

LewisC's An Expert's Guide To Oracle Technology

An ITToolbox user recently asked a question on the EnterpriseDB discussion group, Oracle equilant UTL_I18N.STRING_TO_RAW IN ENTERPRISEDB.

Basically, Sreenivas asked which functions in EnterpriseDB could be used to implement dbms_crypto, hex_to_raw, string_to_raw, etc. I believe he is using EnterpriseDB Postgres Plus Advanced Server which is the product that gives Oracle Compatibility. The short answer to his question is that right now, there are no compatibility functions for those. The long answer is that you can re-implement that functionality using native PG functionality.

If you look at Sreenivas's message you can see how his existing code works. I posted a simple example in response which I am reposting below. The PG docs suggest that you use PGP based encryption rather than what they call raw encryption. I think it depends on exactly what you're doing, personally. Anyway, raw encryption was closer to what Sreenivas was doing so that was what I based my example on.

I've used DBMS_CRYPTO in Oracle quite a bit but this is my first use of encrypt/decrypt in PG. If you have any suggestions for improving it, I'd like to hear them.

Hi Sreenivas,

I saw your post on the edb forum but and planned to write a blog entry on this topic.

The thing is that there isn't a one to one translation in EDB yet. The easiest thing is to rewrite your procedure and use built-ins that are available.

It is recommended that you use the PGP function in postgres as opposed to the raw encryption functions for better security. However, raw encryption more closely matches what you are trying to do. Below is an example of using raw encryption with AES.
You don't need to convert to hex as you'll be using bytea which is easily converted from and to a string. If you really need international support, check out the pg decode function (which is different from Oracle's decode). http://www.postgresql.org/docs/current/static/funct ions-string.html

Here is a very simple example that you can use to build your procedure:

declare

  original_data text := 'I am going to be encrypted';
  data bytea;
  cipher bytea;
  crypto_type text;
  encrypted_data bytea;
  final_string text;

begin

  -- conversion to bytea, could use cast too
  data := original_data;

  --set cipher key
  cipher := 'not a strong key, use pgp instead';

  -- select type: aes or blowfish (bf)
  crypto_type := 'aes';

  -- encrypt the data
  select encrypt(data, cipher, crypto_type) into encrypted_data;

  dbms_output.put_line('Encrypted: ' || encrypted_data );

  -- decrypt the data 
  select decrypt(encrypted_data, cipher, crypto_type) into final_string;
  
  dbms_output.put_line('Decrypted: ' || final_string );

end;

Hope this helps,

LewisC

Technorati : crypto, database, encryption, postgres

Foxpro to Postgres Data Converter

Do you remember Foxpro? xBase? Clipper? I'm an old clipper head from way back. Anyway, I ran across something in my recent internet travels that caught my fancy. It may have the longest name of any utility I have ever seen. FoxPro PostgreSQL Import, Export & Convert Software will transfer tables to and from PostgreSQL and FoxPro. I haven't downloaded it yet so I can say how well it works but it's a neat idea. I'm guessing it can handle DBF files so that would make it compatible with dBase, Foxpro, Clipper and may other tools. It's a shareware program and I don't see a price. It's been a while since I even ran across shareare. That's a flashback for me too. I use to write and sell shareware. Anyway, here's the blurb for it:

This application will allow you to transfer tables to and from PostgreSQL and FoxPro databases. Importing and exporting can be done without SQL knowledge. This program saves time if you need to transfer tables between the two databases quickly. Connect via DBC file or multiple DBF files in a folder.

Take care, LewisC

postgresql foxpro

Native UUIDs in PostgreSQL

Stefan Arentz posted an entry on his blog, about a hack he's done for PostgreSQL. He's added a native UUID to the database. This is pretty slick. Most of your users will never notice but it can help on databases that need it. He lists some advantages on the google code page:

Database Size - Using a native UUID type on a table that also has an index on the uuid column saves 47% space. This is good because there is less data on disk and in memory. Which really matters if you have potentially hundred of millions of records. The native UUID type uses 16 bytes of storage whereas a ascii uuid column uses 38 bytes.

Consistency - The native UUID type only accepts UUIDs that are well formatted. Of course you can also do this with a constraint check, but having a native type with a fixed format is more elegant in my opinion.

Transparent - Nobody is going to notice the difference. Because of the implicit typecasting you can use UUIDs as if they are ascii values.

postgresql uuid data types