column level encryption & select rules - Mailing list pgsql-general

Hi,

I’ve implemented a scheme for column level encryption that uses table triggers (isrt/update) to encrypt the input data, and a view

To perform the decrypt.   It’s working ok, but I’m having trouble altering my objects because of the dependents.

To implement the scheme, I have to generate  the view, table trigger (isrt/updt), and a trigger function.

Currently the decrypt functions are embedded in the views which I want to get rid of.   

Can I implement them as a select rule?

If the select rule directs the queries to the same table, does recursion occur?

View is below.

What are the challenges, etc.

Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide

500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

   orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com 

-- View: ods_views.customer_payment_profile

-- DROP VIEW ods_views.customer_payment_profile;

CREATE OR REPLACE VIEW ods_views.customer_payment_profile AS

 SELECT customer_payment_profile.customer_payment_profile_id, customer_payment_profile.ref_point_of_sale_id, customer_payment_profile.last_used_date,

        CASE

            WHEN owwpiiview() THEN dba_work.owwdecrypt(customer_payment_profile.pi2_pii_card_number, customer_payment_profile.customer_payment_profile_id::text)::character varying(128)

            ELSE '**************************************************'::character varying::character varying(128)

        END AS pii_card_number,

        CASE

            WHEN owwpiiview() THEN dba_work.owwdecrypt(customer_payment_profile.pi2_pii_cardholder_name, customer_payment_profile.customer_payment_profile_id::text)::character varying(200)

            ELSE '**************************************************'::character varying::character varying(200)

        END AS pii_cardholder_name, customer_payment_profile.default_ind, customer_payment_profile.ref_payment_type_code, customer_payment_profile.expiration_date, customer_payment_profile.active_ind, customer_payment_profile.customer_member_id,

        CASE

            WHEN owwpiiview() THEN dba_work.owwdecrypt(customer_payment_profile.pi2_pii_address1, customer_payment_profile.customer_payment_profile_id::text)::character varying(200)

            ELSE '**************************************************'::character varying::character varying(200)

        END AS pii_address1, customer_payment_profile.address2, customer_payment_profile.address3, customer_payment_profile.address4, customer_payment_profile.city, customer_payment_profile.ref_state_province_code, customer_payment_profile.ref_country_code, customer_payment_profile.ref_postal_code, customer_payment_profile.po_box_ind, customer_payment_profile.intl_phone_dialing_code,

        CASE

            WHEN owwpiiview() THEN dba_work.owwdecrypt(customer_payment_profile.pi2_pii_phone, customer_payment_profile.customer_payment_profile_id::text)::character varying(200)

            ELSE '**************************************************'::character varying::character varying(200)

        END AS pii_phone, customer_payment_profile.phone_extension, customer_payment_profile.create_date, customer_payment_profile.modified_date, customer_payment_profile.ref_phone_country_code, customer_payment_profile.oltp_deleted_timestamp, customer_payment_profile.ods_load_timestamp, customer_payment_profile.ref_cc_type_code, customer_payment_profile.cvn_valid_ind, customer_payment_profile.issue_date, customer_payment_profile.pii_issue_number

   FROM customer.customer_payment_profile;