Postgres Pro
Downloads
Home   >   mailing lists

Thread: Storing original rows before update or delete

Storing original rows before update or delete

From
Miroslav Šimulčík
Date:
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

Re: Storing original rows before update or delete

From
Szymon Guz
Date:


On 4 November 2011 10:20, Miroslav Šimulčík <simulcik.miro@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.

On the other hand: superuser always can delete data from a table, so you cannot stop him from doing that.

regards
Szymon

Re: Storing original rows before update or delete

From
Miroslav Šimulčík
Date:
If I restrict any DML operation to internal tables (history tables) in phase of parse analyze, nobody can do such operation regardless of user rights. However, triggers can't be used in this case.

2011/11/4 Szymon Guz <mabewlun@gmail.com>


On 4 November 2011 10:20, Miroslav Šimulčík <simulcik.miro@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.

On the other hand: superuser always can delete data from a table, so you cannot stop him from doing that.

regards
Szymon


Re: Storing original rows before update or delete

From
Robert Haas
Date:
2011/11/4 Miroslav Šimulčík <simulcik.miro@gmail.com>:
> If I restrict any DML operation to internal tables (history tables) in phase
> of parse analyze, nobody can do such operation regardless of user rights.

There is absolutely zero point in trying to prevent the superuser from
doing whatever they want.  The superuser has many, many ways of
bypassing whatever controls you put in place - e.g. loading custom C
code into the backend, whacking around the system catalogs, etc.

> However, triggers can't be used in this case.

Which is another reason not to do it that way.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company