Re: pl/pgsql RECORD data type, how to access to the values

On Mon, Apr 7, 2008 at 1:56 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> Guillaume Bog wrote:
>
> > I want a table to be "read-only", so I raise exceptions with a before
> > trigger on update, insert and delete. It works well.
> >
> > This read-only table is modified (delete + insert) by a trigger
> > function set on another table, but this second trigger calls the first
> > and I can modify my read-only table. I'd like my "read-only" trigger
> > to be aware that the modification call on the read-only table comes
> > from the second trigger. Is it possible?
>
> Quick question: Why not make the read only table a view of the writeable
> table, instead of using triggers to copy data?

That's how it is now. I have a writable table of events on objects,
and a (complex) view depicting the status of my objects according to
those events. I have pushed a lot of logic on SQL side, mostly with
views, and I'm very happy with this design choice so far (I can
completely change my core logic in few hours!), but the drawback is
that selecting lists of objects according to their states becomes
slower (one second or more). So I tried to materialize one of the
status view, and it seems to work well, but I'd just want to make sure
nobody alters it. It seems that using privileges and SECURITY DEFINER
will be the best approach to protect data integrity.

I have a related issue with a value very often accessed by my object,
and available only in the "grand grand-parents", which makes a triple
join very common in many statements. I hid the triple join behind a
view for convenience but the performance is so-so (even with indexes
on each foreign keys), and I want to try to denormalize this value,
and duplicate it in the object. So here also I need triggers, and will
try the SECURITY DEFINER option (while it is different, because I need
to make only one column "read-only", not a full table)

Thanks for the tip.

> If your data doesn't fit that use or that'd be inefficient, can you use
> access privileges rather than a trigger to limit changes to the read
> only table? I find that limiting a user to SELECT priveleges on a table
> and using a SECURITY DEFINER trigger or other function to perform
> certain restricted priveleged operations on the table to be very useful.
> In your case you might be able to restrict users to SELECT priveleges on
> your read only table, drop the "read only" restriction trigger, and make
> the updating trigger SECURITY DEFINER (after carefully thinking about
> possible risks and issues).
>
> Why the separate read only table, anyway? A materialized view / summary
> table? Something to do with user access control ?
>
> --
> Craig Ringer
>