PostgreSQL 表丢失原因及防止 DROP 操作的方法

摘要：本文总结了 PostgreSQL 数据库中表丢失的常见原因，包括误操作、权限问题、脚本错误等，并提供了多种防止用户执行 DROP 操作的方法，如权限控制、事件触发器、只读模式和日志审计。

1. 为什么 Postgres 数据库会丢失表

      PostgreSQL 是一个非常稳定的关系型数据库系统，通常不会无缘无故“丢失表”。但如果遇到这种情况，通常是由于以下几种原因：

1.1 误操作删除了表

• 使用了 `DROP TABLE` 或 `DROP SCHEMA`。
• 使用了 `TRUNCATE` 清空数据但没意识到。
• 没有启用回收站功能（PostgreSQL 默认没有回收站）。

1.2 连接到错误的数据库或模式（Schema）

• 应用程序连接的是测试/开发环境，而非生产环境。
• 数据库中有多个同名表（不同 schema）。

1.3 权限问题导致看不到表

• 用户没有访问该表的权限。
• 表被 `REVOKE` 掉了 SELECT / USAGE 权限。

1.4 自动脚本或定时任务清除了表

• 有定时任务（比如 cron job）运行了 SQL 脚本删除了表或数据。
• 数据库迁移工具（如 Flyway、Liquibase）执行了不恰当的清理动作。

1.5 备份和恢复失败

• 没有定期备份，或者恢复时遗漏了某些表。
• 备份过程中出错，恢复后数据不完整。

1.6 PostgreSQL 实例崩溃或磁盘损坏

• 极端情况（如硬件故障、断电、磁盘满等）可能导致数据损坏。

2. 如何排查表丢失的问题

2.1 检查数据库中是否存在该表

SELECT * FROM pg_tables WHERE tablename = 'your_table';

2.2 查看 PostgreSQL 日志

tail -f /var/log/postgresql/postgresql-{version}-main.log

2.3 查询最近的操作历史

-- 查找是否有类似如下语句：
DROP TABLE your_table;
DELETE FROM your_table;
TRUNCATE TABLE your_table;

3. 预防措施

预防措施	说明
定期备份	使用 `pg_dump` 或 `pg_basebackup`。
开启日志审计	记录所有 SQL 语句（`log_statement = 'all'`）。
设置只读账户	对于非管理员账户限制写入权限。
使用版本控制管理数据库结构	如 Flyway、Liquibase。
监控和告警	检测异常 DROP 或 DELETE 操作。

4. 如何保护数据库结构，防止用户 drop 表

4.1 使用权限控制

      通过限制用户对数据库对象的操作权限来增强安全性。

4.1.1 撤销用户在 schema 上的 CREATE 权限

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

4.1.2 禁止用户执行 DROP TABLE

      确保普通用户没有以下权限：

• 不是超级用户 (`SUPERUSER`)。
• 不是表的所有者。
• 没有被显式授权 `DROP` 表的权限。

4.1.3 修改表所有权

ALTER TABLE your_table OWNER TO admin_user;

4.2 使用 DDL 触发器

      PostgreSQL 提供了 **事件触发器（Event Trigger）** 功能，可以监听 DDL 操作（如 `DROP TABLE`, `DROP DATABASE` 等），并阻止它们。

4.2.1 创建触发函数

CREATE OR REPLACE FUNCTION protect_drop()
RETURNS event_trigger AS $$
BEGIN
    RAISE EXCEPTION 'User % is not allowed to perform operation: %. Contact DBA.', SESSION_USER, TG_TAG;
END;
$$ LANGUAGE plpgsql;

4.2.2 创建事件触发器

CREATE EVENT TRIGGER prevent_drop_table
ON ddl_command_start
WHEN TAG IN ('DROP TABLE')
EXECUTE FUNCTION protect_drop();

4.3 使用只读模式

-- 设置用户连接到数据库时默认处于只读模式
ALTER USER some_user SET default_transaction_read_only = on;

4.4 审计与日志记录

# postgresql.conf
log_statement = 'ddl'   # 记录所有 DDL 操作（包括 CREATE, DROP, ALTER）

4.5 使用中间件/代理层限制操作

      如果你使用了数据库中间件（如 PgBouncer、ProxySQL、MaxScale），可以在代理层过滤掉 `DROP`、`TRUNCATE` 等危险语句。

5. 推荐组合策略

措施	说明
使用事件触发器	推荐。
限制用户权限	用户不要赋予 SUPERUSER 权限。
修改表所有权	将敏感表归属 DBA 用户。
定期备份	防止误删后无法恢复。
开启日志审计	方便追踪是谁干的。

6. 总结

      要“不允许用户 DROP 表”，最有效的方式是：

1. 使用事件触发器（推荐）。
2. 限制用户权限。
3. 修改表所有权。
4. 开启审计日志。