PostgreSQL数据保护：如何有效防止删改操作

某运输公司，由于没有采取数据防删改措施，DBA在维护系统时不小心修改了客车运行时刻表，引发了调度系统混乱，导致客车运行延误和乘客投诉。最终导致了监管部门的罚款和公众信任度的下降。

此外，当客户需要处置业务的基础表、日志或报表数据表、共享数据表、历史数据表以及审计等需要合规要求的数据时，需要采取数据防删改措施，保护数据的完整性和稳定性。

如何如何防止非法删除数据等人为错误呢？具体办法有多种：比如有的考虑引入pg_safeupdate扩展，该方案可行，但部署麻烦，常报错。是否有其它办法呢？

1、设置指定表对任何人只读

PostgreSQL 配置某个特定表对所有用户只读。你通过以下步骤来实现这一目标：

撤销表的写权限：

对特定表撤销所有用户（PUBLIC）的写权限（INSERT、UPDATE、DELETE）。

REVOKE INSERT, UPDATE, DELETE ON TABLE your_table FROM PUBLIC;

确保以后的权限不会更改：

如果你希望将来对该表的权限继续保持只读，修改默认权限，确保即使新用户或角色创建时也会自动没有写权限：

ALTER DEFAULT PRIVILEGES REVOKE INSERT, UPDATE, DELETE ON TABLES FROM PUBLIC;

授予 SELECT 权限：

如果你希望某些特定用户或角色对该表具有只读访问权限，授予SELECT权限：

GRANT SELECT ON TABLE your_table TO PUBLIC;

检查权限：

确保没有其他的用户或角色拥有写权限，使用以下命令检查权限：

\dp your_table

通过这些步骤，可确保某个表对所有用户（或特定角色）是只读的，避免进行任何写操作。

2、防止删除过多的行

如果您只是担心表数据被恶意删除，您中以创建一个语句级触发器，假如有人修改或删除了太多的行，它会禁止操作，并发出提示。

# 定义一个示例表
CREATE TABLE deleteme
AS SELECT i FROM generate_series(1, 1000) AS i;

# 创建一个防删除多行的触发器
CREATE FUNCTION stop_mass_deletes() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF (SELECT count(*) FROM OLD) > TG_ARGV[0]::bigint THEN
      RAISE EXCEPTION 'must not modify more than % rows', TG_ARGV[0];
   END IF;
   RETURN NULL;
END;$$;

#把改触发器应用到示例表
CREATE TRIGGER stop_mass_deletes AFTER DELETE ON deleteme
   REFERENCING OLD TABLE AS old FOR EACH STATEMENT
   EXECUTE FUNCTION stop_mass_deletes(10);
# 注意：stop_mass_deletes(10);中的10为可删除的最大值，该值可自定义为任意整数
   
# 验证删除100行
DELETE FROM deleteme WHERE i < 100;
ERROR:  must not modify more than 10 rows
CONTEXT:  PL/pgSQL function stop_mass_deletes() line 1 at RAISE
#提示正常的报错：修改不可超过10行

# 验证删除小于10行，是否成功
DELETE FROM deleteme WHERE i < 10;
DELETE 9
#提示成功

通过把指定表设为只读，或禁止删除指定数量的数据行，可有效防止误操作或非法操作。当然，管理员要时刻铭记：备份是业务稳定的基本保障。