Thread: Proposed archival read only trigger on rows - prevent history modification

I've got a largish database which once a month churns out some
invoices.  Once those invoices are created, there is zero business logic
reason to ever modify the underlying data.  A few hundred thousand
database rows go into the building of each month's invoices.  New data
is added all the time, and used at the end of each month.

I'm considering building a protective mechanism, and am seeking feedback
on the idea.  The approach would be to add a new column named "ro" to
each table at invoice level and below.  Then have a trigger on
'ro'==true deny the write, and probably raise a huge stink.  As invoice
are mailed each month, all the supporting data would be set to "ro" true.

The idea is to protect years and years of archival data from an
inadvertent write (such from an underspecified where clause, or a
software bug).  Ideally the mechanism would never be triggered.  To
corrupt data would require two acts -- changing the "ro" column, then
issuing an update.

I'm seeking feedback on the need, the approach, performance issues, and
any instances of core database support for such a concept.  I do see an
Oracle feature that seems somewhat on target.  I am using postgres, in a
mostly database independent manner.
         Bryce Nesbitt         http://www.citycarshare.org/

> I'm considering building a protective mechanism, and am seeking
> feedback
> on the idea.  The approach would be to add a new column named "ro" to
> each table at invoice level and below.  Then have a trigger on
> 'ro'==true deny the write, and probably raise a huge stink.  As
> invoice
> are mailed each month, all the supporting data would be set to "ro"
> true.

instead of triggers I use update-able views and permissions.

1. all dml goes through the view
2. use rules on the view to do dml to the table
3. in rules prevent updating all/any columns when whatever
4. grant dml to view to your pgconnect user
5. revoke dml from table to your pgconnect user

imho another instance where rules rule.  for example, you can easily
fit logging into the same view.


     ____________________________________________________________________________________
Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

Yes, the view approach has some advantages.  But it still leaves the
underlying tables naked to modification.
And since the most likely error is... well... me (or another admin) at
the SQL prompt, we want underlying tables protected also.

chester c young wrote:
> instead of triggers I use update-able views and permissions.
>
> 1. all dml goes through the view
> 2. use rules on the view to do dml to the table
> 3. in rules prevent updating all/any columns when whatever
> 4. grant dml to view to your pgconnect user
> 5. revoke dml from table to your pgconnect user
>
> imho another instance where rules rule.  for example, you can easily
> fit logging into the same view.
>   

-- 
----
Visit http://www.obviously.com/

(because our legacy application, which won't change, is using the underlying tables.  We can't do step #5).<br /><br
/>Bryce Nesbitt wrote: <blockquote cite="mid:47B1580A.7050703@obviously.com" type="cite"><pre wrap="">Yes, the view
approachhas some advantages.  But it still leaves the
 
underlying tables naked to modification.
And since the most likely error is... well... me (or another admin) at
the SQL prompt, we want underlying tables protected also.

chester c young wrote: </pre><blockquote type="cite"><pre wrap="">instead of triggers I use update-able views and
permissions.

1. all dml goes through the view
2. use rules on the view to do dml to the table
3. in rules prevent updating all/any columns when whatever
4. grant dml to view to your pgconnect user
5. revoke dml from table to your pgconnect user

imho another instance where rules rule.  for example, you can easily
fit logging into the same view.    </pre></blockquote><pre wrap=""> </pre></blockquote><br /><pre class="moz-signature"
cols="100">--
 
----
Visit <a class="moz-txt-link-freetext" href="http://www.obviously.com/">http://www.obviously.com/</a>
</pre>

On Tuesday 12 February 2008 03:25, Bryce Nesbitt wrote:
> Yes, the view approach has some advantages.  But it still leaves the
> underlying tables naked to modification.
> And since the most likely error is... well... me (or another admin) at
> the SQL prompt, we want underlying tables protected also.
>
> chester c young wrote:
> > instead of triggers I use update-able views and permissions.
> >
> > 1. all dml goes through the view
> > 2. use rules on the view to do dml to the table
> > 3. in rules prevent updating all/any columns when whatever
> > 4. grant dml to view to your pgconnect user
> > 5. revoke dml from table to your pgconnect user
> >
> > imho another instance where rules rule.  for example, you can easily
> > fit logging into the same view.

You can use rules on the underlying tables themselves (on update do nothing) 
which tends to work pretty well.  You can also add triggers into the mix to 
raise errors on update. Also dont forget to revoke update/delete/install 
privileges as appropriate. And look into vacuum freeze. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Tuesday 12 February 2008 03:25, Bryce Nesbitt wrote:
> Yes, the view approach has some advantages.  But it still leaves the 
> underlying tables naked to modification. And since the most likely 
> error is... well... me (or another admin) at the SQL prompt, we want 
> underlying tables protected also.

Couldn't you make a row trigger BEFORE UPDATE?

CREATE OR REPLACE FUNCTION readonly_handler() RETURNS "trigger" AS
$BODY$BEGIN    IF OLD.read_only = TRUE THEN        RETURN OLD;    ELSE        RETURN NEW;    END IF;END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.