在PostgreSQL的表上​​禁用DELETE？

本文介绍了在PostgreSQL的表上​​禁用DELETE？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

对于安全敏感的设计，我想在某些表上禁用 Delete 。

For a security sensitive design, I'd like to disable DELETEs on certain tables.

code> Delete 仅应在行上设置 deleted 标志（然后在视图中可见，该标志将由应用层）。

The DELETE should merely set a deleted flag on a row (which would be then visible on a view, which would be used by the application layer).

据我了解规则会生成其他查询-因此规则无法禁止原始查询。

As I understand a rule would generate additional queries - so a rule could not suppress the original query.

如图所示，一个带有触发器的玩具示例（尚未测试）：

As illustration a toy example with a trigger (not yet tested):

-- data in this table should be 'undeletable' CREATE table article ( id serial, content text not null, deleted boolean default false ) -- some view that would only show articles, that are NOT deleted ... -- toy trigger (not tested) CREATE OR REPLACE FUNCTION suppress_article_delete() RETURNS TRIGGER AS $sad$ BEGIN IF (TG_OP = 'DELETE') THEN UPDATE article SELECT id, content, TRUE; -- NEW or NULL?? RETURN NEW; END IF; RETURN NULL; END; $sad$ LANGUAGE plpgsql;

什么是抑制 Delete ？

推荐答案

据我了解，一条规则会生成其他查询-因此一条规则无法取消原始查询。

As I understand a rule would generate additional queries - so a rule could not suppress the original query.

不是真的-它可能是 INSTEAD 规则：

Not really - it could be an INSTEAD rule:

CREATE RULE shoe_del_protect AS ON DELETE TO shoe DO INSTEAD NOTHING;

（该手册同一页上的示例）。

(an example on that same page of the manual).

另一种方法是 REVOKE 删除有关表的特权，并创建存储过程以进行删除...以及更新和插入

Another way is to REVOKE delete privileges on the table in question and to create stored procedure(s) for deleting... and updating and inserting also probably.