On 4 November 2011 10:20, Miroslav Šimulčík <simulcik.m...@gmail.com> wrote:
> Hi. > > I'm working on transactiontime temporal support for postgresql 9.0.4. Each > original table with transactiontime support has associated history table, > where original row is stored before each update or delete operation on it. > Both original and history tables have internal timestamp columns for > storing the period of validity of row versions. History tables are internal > and I want to restrict any DML operation on it, so nobody can change > history of operations made on original table. Problem is, that I don't know > where to implement the mechanism for storing original rows to history > tables. Rewrite rules are not suitable, because they are not working with > inheritance and I can't use triggers, because inserts to history tables are > restricted. Can you point me to place in postgresql backend where can i > implement this and maybe give me some hints about how to do it correctly? > > Thank you. > > Best regards > Miroslav Simulcik > Hi, use triggers with security definer so the owner of the triggers and the history table can insert into it, but normal user cannot - this user only is able to change data in original table, and triggers will copy the data, but will be executed using the first user credentials. http://www.postgresql.org/docs/9.0/interactive/sql-createfunction.html On the other hand: superuser always can delete data from a table, so you cannot stop him from doing that. regards Szymon