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

Take an Open Source Database Survey

LewisC's An Expert's Guide To Oracle Technology

Do you know which open source feature is the most important? Do you know which open source database rocks and which one sucks? Is MySQL better than Postgres? Is Ingres worth considering? How does Firebird compare? Have you used, or have you considered using, an open source database?

Take a survey. It's only 15 questions so it takes just a few minutes.

I'll post a link where you can get the results once they have been compiled and prepared.

BTW, this isn't my survey. I'm just passing on the link.

LewisC

Overall Total in a Query

I was asked a question today by someone who emailed me. I like getting the occasional question as sometimes it either makes me learn new things or sharpens my focus on something I already know. I often don't have time to answer questions but I try as often as I can.

Today's question didn't teach me anything new but it was a fun 15 minute distraction. I like SQL and coming up with solutions.

Here's the question (names removed to protect the innocent):

Subject: Postgres query

Hello,

My name is xxxxxxx and I saw your link on the web and wonder if you have any example that can do the following view.

I have a table called agent_sales, in it I have an agent_link code and a Invoicesales records. I want to create a view that will sum(invoicesales) group by agent_link then create a percentage column of the summed(invoice sales to the overall grand total of the invoiced sales)

e.g.

agent link     invoice sales

   1               100

   2               300

   1               500

   2               400

When I sum by agent link I get 1= 600 and 2 = 700

Then I want to calculate a percentage or 1 and 2 to the total so it would work out to be 1 = 600/1300 & 2 = 700/1600

Can you assist me?

Regards

I have had to do these kinds of queries often so it didn't take long to put it together. The first time I had to do one if these, though, it took me quite a while to get it right.

Oracle provides some analytical queries that make these queries extremely easy to write and very performant. Since this was postgres, I took a different approach.

create table agent_sales (

  agent_link int,

  invoice_sale int );

insert into agent_sales

  (agent_link, invoice_sale)

values

  (1, 100), (2,300), (1, 500), (2, 400);

CREATE VIEW ag_sales AS

select agent_link, inv_sales, inv_sales / tot as overall_precentage

from

  (select agent_link, sum(invoice_sale)::float as inv_sales

     from agent_sales

     group by agent_link) as link_sales,

  (select sum(invoice_sale)::float as tot

     from agent_sales) as total_sales

Output:

SQL> select * from ag_sales;



 AGENT_LINK                INV_SALES       OVERALL_PRECENTAGE

----------- ------------------------ ------------------------

          2                    700.0       0.5384615384615384

          1                    600.0      0.46153846153846156



SQL>

Obviously, there are plenty of ways to answer most any question but this is the way I answered this one. How would you have done it?

postgres query

Postgres DDL Transaction Control Sucks

I come from an Oracle background so I am used to a few rules when it comes to DDL.

• Don't perform DDL in a production database until you've done it in a test instance.

• DDL is permanent unless you have flashback or want to restore.

That might sound like a limitation but I don't see it that way. You *SHOULD* test everything before running it in production. And flashback is much more powerful than simply having commit and rollback for DDL. Today I was trying out some changes to my code (that runs outside of the database) and needed to do a quick test that would delete some rows. I did a quick create table xyz as select * from abc; I ran a quick test on some prep data: select func(); The func() hit an error and rolled back. I fixed the error and reran the function. It worked so I ran my external program. It deleted the data and committed the changes. I then tried to put the records back in the first table from the second table. Oops. Second table no longer exists. The rollback from the stupid func() also rolled back my create table.

In Oracle, I would have several options to recover my data. For one, the create would automatically have committed. But in Oracle, I wouldn't have needed the table, I would just flashback and restore my data.

Fortunately, this was just a test bed database and the data isn't really critical. Had it been critical, I would have taken steps to be sure it was backed up. So, no big deal really.

And, yeah, I know Ingres and SQL Server (and probably Sybase) work like Postgres. Doesn't make it a good thing.

My next whine will be transaction control is stored procedures. But at least that will be coming from EnterpriseDB at some point.

postgres ddl

Postgres East 2008 Conference

I'm in Baltimore this weekend at the Postgres East 2008 conference. I'm giving a presentation tomorrow on SQL/XML for developers. Below is the presentation and below that is the code used in the presentation. Code to follow along: If you aren't using javascript, you can get download the presentation or the script from scribd. Or, to make it easy, just grab this code:

Slide 25

CREATE TABLE EMP
(
  LAST_NAME text,
  EMP_ID integer NOT NULL,
  FIRST_NAME text,
  DEPT_ID integer,
  SALARY integer,
  CONSTRAINT EMP_pkey PRIMARY KEY (EMP_ID)
)
WITH (OIDS=FALSE);
ALTER TABLE EMP OWNER TO xmluser;


Slide 26

INSERT INTO 
    EMP(
       LAST_NAME, EMP_ID, FIRST_NAME, DEPT_ID, SALARY)
    VALUES
      ('Blow', 1, 'Joe', 1, 10000),
      ('Head', 2, 'Helmut', 1, 12000),
      ('Jack', 3, 'Noe', 1, 12000),
      ('Hard', 4, 'Blow', 2, 20000),
      ('First', 5, 'Hugo', 2, 21000),
      ('Spaem',6, 'Kingoof', 2, 20000),
      ('Ventura', 7, 'Ace', 3, 35000),
      ('Nickleby', 8, 'Nick', 3, 400000),
      ('Budd', 9, 'Billy', 4, 99000),
      ('Cleaver', 10, 'Wally', 4, 100000) ;


Slide 29

SELECT xmlelement(name main, last_name) from emp;

Slide 30

SELECT xmlelement(name main, last_name),
       xmlelement(name main, first_name)
FROM emp;


Slide 31

SELECT XMLForest(last_name, first_name)
FROM emp;

Slide 32

SELECT xmlelement(name main, 
       xmlforest(last_name, first_name) )
FROM emp;

Slide 33

SELECT XMLAgg(
  XMLForest(last_name, first_name) )
FROM emp;


Slide 34

SELECT XMLElement(name main, 
       XMLAgg(XMLForest(last_name, first_name) ))
FROM emp;


Slide 35

SELECT XMLElement(name main, XMLAttributes(nextval('t_seq') AS rownum) )
FROM emp;


Slide 36

CREATE TEMP SEQUENCE t_seq;

SELECT xmlelement(name main, xmlattributes(nextval('t_seq') AS rownum),
       xmlforest(last_name, first_name) )
FROM emp;

DROP SEQUENCE t_seq;

Slide 38

SELECT xmlelement(name main, 
       xmlforest(last_name || first_name AS fullname, salary) )
FROM emp;


Slide 39

SELECT xmlelement(name main,
       xmlelement(name fullname, xmlattributes(dept_id), last_name || first_name ),
       xmlforest(salary) )
FROM emp;



Slide 40

SELECT xmlelement(name main, xmlcomment('Each Name Goes Here'),
       xmlforest(last_name, first_name))
FROM emp;


Slide 41

SELECT xmlelement(name lastname, last_name),
       xmlelement(name firstname, first_name)
FROM emp;

Slide 42

SELECT xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) )
FROM emp;

Slide 43

SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp;

Slide 44

CREATE TABLE xmltab (col1 XML);

INSERT INTO xmltab ( 
  SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp);

Slide 45

SELECT * FROM xmltab;

Slide 47

SELECT xpath('/main/firstname/text()', col1) FROM xmltab;

Slide 48

SELECT textcol[1] FROM (
  SELECT xpath('/main/firstname/text()', col1) AS textcol
    FROM xmltab ) AS xmlsource;

Slide 49

select query_to_xml('select * from emp', TRUE, TRUE, 'scott');

Slide 50

select table_to_xml('emp', TRUE, TRUE, 'scott');

postgresql conference sql/xml presentation

32 Bit ODBC Drivers in Vista 64

This post isn't really PostgreSQL specific. It also happens with MySQL and Ingres (and any other 32 bit driver).

I am running Vista 64 on one of my machines. I installed Postgres 8.3 and the ODBC drivers. When I tried to setup an ODBC connection, the windows ODBC administrator program ODBCAD32.exe did not list Postgres. I noticed that it also did not list MySQL or Ingres even though I knew I had installed drivers for those databases also.

After a bit of research, I tracked down the issue. My ODBC drivers did in fact install. Microsoft, in all their wisdom, has 2 versions of odbcad32. A 32 bit and a 64 bit. They are both named odbcad32.exe.

It gets better. They put the 32 bit odbcad32.exe in the syswow64 directory. They put the 64 bit odbcad32.exe in the system32 directory. 32 bit apps will pick up the 32 bit registry setting and 64 bit will pick up the 64 bit registry setting. system32 comes before syswow64 in the system path so the 64bit software runs before the 32 bit software.

So, when I manually ran odbcad32.exe in the syswow64 directory, I was able to configure my connections and everything worked hunky dory. I know have a shortcut to syswow64\odbcad32.exe on my desktop for working with 32 bit databases.

I wonder how stable the system would be if syswow64 was in the path before system32? I don't plan to find out.

LewisC

ODBC postgresql

Oracle #1 but Postgres Gaining Ground

I just read this in InfoWeek: Database Survey Gives Oracle The Lead In All 13 Categories The survey compared Oracle with DB2, MySQL, Informix Dynamic Server, PostgreSQL, Microsoft SQL Server, and Sybase Advanced Server Enterprise. The significant parts of that are that Postgres was included (we don't see enough of that) and that Postgres performed exceptionally well. Some important quotes:

In a few categories, the open source system, MySQL, trailed most of the five commercial systems with which it was compared. But high user satisfaction in several categories indicates thatSun Microsystems (NSDQ: JAVA) may have gotten its money's worth when it paid $1 billion for the database's parent company, MySQL AB, last month. MySQL was second only to Oracle in multiplatform support, an important factor in hosting Web applications. When it came to the all important "performance" category, it ranked higher than Microsoft SQL Server, Informix, and Sybase.

Is MySQL really supported on more platforms than Postgres?

In scalability, Oracle lead, followed by DB2, then PostgreSQL took over the commanding position of the open source systems, followed by SQL Server, Informix, Sybase, and MySQL.

Oracle lead the security category, with its ability to automatically store data in encrypted form and to maintain an Audit Vault of information drawn from the operating system, database, and any other auditing source. DB2 was number two followed in the third position by PostgreSQL, with its "robust security layer," said Andrews. Number four was SQL Server, followed by Sybase, MySQL and Informix.

Oracle was tops in atomicity, followed by DB2, PostgreSQL, SQL Server, Sybase, MySQL and Informix.

An increasingly important category for databases that are intended to serve Web applications is XML data handling. Again, Oracle and SQL Server lead the category, followed by DB2. But the open source systems, MySQL and PostgreSQL made up the sixth and seventh ranks, respectively.

When it comes to management tools that come with the database, Oracle lead, followed by SQL Server, DB2, MySQL, Sybase, and Informix. Bringing up the rear was PostgreSQL. "PostgreSQL, which has a large community of open source developers to create tools for it, does not provide them with the database," the report noted.

All in all, I think those are good numbers for Postgres. The really important thing is getting Postgres out there and making it visible. Articles like this go a long way towards that goal. LewisC

postgresql oracle