postgresql - 在 PostgreSQL 的表上禁用 DELETE？

Question

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

DELETE 应该只在行上设置一个deleted 标志(然后在 View 上可见，应用层将使用该标志)。

据我了解rule会生成额外的查询 - 因此规则无法抑制原始查询。

作为带有触发器(尚未测试)的玩具示例的说明:

-- 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 的好方法？

Answer 1

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

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

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

(手册同一页上的示例)。

另一种方法是REVOKE 删除相关表的权限，并创建用于删除...以及更新和插入的存储过程。