在 PostgreSQL 中的表上禁用 DELETE? [英] Disable DELETE on table in PostgreSQL?

问题描述

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

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

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 的好方法是什么?

What would be a good way to suppress a 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.

这篇关于在 PostgreSQL 中的表上禁用 DELETE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！