pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProposed archival read only trigger on rows - prevent history modification
7+ messages / 5 participants
[nested] [flat]
* Proposed archival read only trigger on rows - prevent history modification
@ 2008-01-29 04:16 Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bryce Nesbitt @ 2008-01-29 04:16 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Proposed archival read only trigger on rows - prevent history modification
@ 2008-01-29 04:40 chester c young <chestercyoung@yahoo.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 7+ messages in thread
From: chester c young @ 2008-01-29 04:40 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Proposed archival read only trigger on rows - prevent history modification
@ 2008-01-30 04:31 Steve Midgley <public@misuse.org>
0 siblings, 0 replies; 7+ messages in thread
From: Steve Midgley @ 2008-01-30 04:31 UTC (permalink / raw)
To: pgsql-sql-owner@postgresql.org; pgsql-sql
At 07:50 PM 1/29/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Mon, 28 Jan 2008 20:16:35 -0800
>From: Bryce Nesbitt <bryce1@obviously.com>
>To: pgsql-sql@postgresql.org
>Subject: Proposed archival read only trigger on rows - prevent history
>modification
>[snip]
>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.
>[snip]
Hi Bryce,
I have a similar situation but a little in reverse. I have many sets of
*incoming* records, which I want to preserve, though at any one time
there is only one "live" version of the incoming records. Sometimes I
have to read and compare versions of the records, live or otherwise.
The logical records I'm talking about occupy a number of tables that
are joined together in the database itself.
My solution, which required a little middleware engineering, was to
create two tables for each table that had multiple "versions" of
records. So, I have a "property" table and a "property_versions" table
that have identical table structures. I have a column common to every
such versioned set of tables called "import_group_id." For live tables,
this just tells me which version from the "*_versions" table is
currently being used for that row. The live tables have primary keys
just like normal ("id" as a serial int field). The "versions" tables'
primary keys are different, compound keyed off "id" and
"import_group_id." This permits normalization but also allows multiple
versions of the same records.
In your case, I'd say you could archive your data table to a
"data_versions" table. You might archive periodically and leave the
records on the live table (but knowing that audit versions are safely
tucked away and easily accessible/comparable), or you might migrate the
records off the live table onto the versions table (insert followed by
a delete in a transaction). If you adopted the latter method, you could
union the two tables to get a complete set of rows. (Via a view even?
Not sure if you can create a view on a union but it seems likely you
can..)
You could also choose (like I did) to store multiple versions of the
records, if your data are slowly changing rather than completely
unchanging. However it sounds like your situation is such that you want
to ensure old records are not modified ever. If you set up triggers
and/or permission on the "data_versions" table, no one can ever delete
or modify anything there. This might work perfectly for your purposes.
Data in the live table can be edited as you like but changes to the
"data_versions" table is simply not permitted. Inserts are allowed to
data_versions but nothing else..
I hope this idea is useful. If I haven't explained it well, drop me a
line and I'll try to clarify. Good luck with the project!
Steve
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Proposed archival read only trigger on rows - prevent history modification
@ 2008-02-12 08:25 Bryce Nesbitt <bryce1@obviously.com>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 2 replies; 7+ messages in thread
From: Bryce Nesbitt @ 2008-02-12 08:25 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Proposed archival read only trigger on rows - prevent history modification
@ 2008-02-12 08:30 Bryce Nesbitt <bryce1@obviously.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 0 replies; 7+ messages in thread
From: Bryce Nesbitt @ 2008-02-12 08:30 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
(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 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:
</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>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Proposed archival read only trigger on rows - prevent history modification
@ 2008-02-22 03:31 Robert Treat <xzilla@users.sourceforge.net>
parent: Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 1 reply; 7+ messages in thread
From: Robert Treat @ 2008-02-22 03:31 UTC (permalink / raw)
To: pgsql-sql; +Cc: Bryce Nesbitt <bryce1@obviously.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Proposed archival read only trigger on rows - prevent history modification
@ 2008-02-22 04:09 Phillip Smith <phillip.smith@weatherbeeta.com.au>
parent: Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 7+ messages in thread
From: Phillip Smith @ 2008-02-22 04:09 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-02-22 04:09 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-29 04:16 Proposed archival read only trigger on rows - prevent history modification Bryce Nesbitt <bryce1@obviously.com>
2008-01-29 04:40 ` chester c young <chestercyoung@yahoo.com>
2008-02-12 08:25 ` Bryce Nesbitt <bryce1@obviously.com>
2008-02-12 08:30 ` Bryce Nesbitt <bryce1@obviously.com>
2008-02-22 03:31 ` Robert Treat <xzilla@users.sourceforge.net>
2008-02-22 04:09 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2008-01-30 04:31 Re: Proposed archival read only trigger on rows - prevent history modification Steve Midgley <public@misuse.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox