如何在多租户PostgreSQL中安全隔离数据库与用户？需考量哪些进阶事项？

除了创建用户并设置为对应数据库所有者的基础操作外，还需要从安全、资源隔离、生命周期管理等多个维度考虑以下事项：

• 严格控制用户权限范围
  默认创建的用户可能隐含部分不必要权限，需显式限制：

  CREATE USER u2745 WITH NO CREATEDB NO CREATEROLE NO SUPERUSER LOGIN PASSWORD 'strong_pwd';
  

  同时要确保用户仅能访问自己的数据库，可通过撤销其他数据库的连接权限实现：

  REVOKE CONNECT ON DATABASE template1 FROM u2745;
  -- 对其他已存在的用户数据库执行类似撤销操作
  

• 连接与资源限制

  • 限制单个用户的最大连接数，防止资源被耗尽：

    ALTER USER u2745 CONNECTION LIMIT 10;
    

  • 使用PG的资源组功能（需启用pg_resource_group扩展），限制用户的CPU、内存使用率：

    CREATE RESOURCE GROUP rg_user WITH CPU_RATE_LIMIT 20 MEMORY_LIMIT 1024;
    ALTER USER u2745 SET RESOURCE_GROUP = rg_user;
    

  • 针对用户调整会话级参数，比如work_mem，平衡性能与资源占用：

    ALTER USER u2745 SET work_mem = '64MB';
    

• 安全加固措施

  • 强制强密码策略，通过pg_hba.conf设置scram-sha-256认证方式，避免明文密码传输；
  • 设置密码过期时间，定期强制用户更换密码：

    ALTER USER u2745 VALID UNTIL '2025-12-31';
    

  • 限制用户连接来源IP，在pg_hba.conf中添加规则：

    host    d2745     u2745     192.168.1.0/24     scram-sha-256
    

• 数据库初始化规范
  创建数据库时指定统一的编码、时区等配置，避免继承template1的冗余对象：

  CREATE DATABASE d2745 OWNER u2745 
  ENCODING 'UTF8' 
  LC_COLLATE 'zh_CN.UTF-8' 
  LC_CTYPE 'zh_CN.UTF-8' 
  TEMPLATE template0;
  

  可根据业务需求预安装常用扩展（如uuid-ossp、pg_trgm）：

  \c d2745
  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  

• 备份与恢复机制

  • 制定针对单个用户数据库的备份策略，使用pg_dump定期备份：

    pg_dump -U postgres d2745 > d2745_backup.sql
    

  • 测试单个数据库的恢复流程，确保用户数据在故障时能快速恢复；
  • 考虑增量备份方案，降低备份存储成本和恢复时间。

• 生命周期管理

  • 用户注销时，需先断开其所有连接再清理资源：

    -- 断开用户所有连接
    SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'u2745';
    -- 删除数据库和用户
    DROP DATABASE IF EXISTS d2745;
    DROP USER IF EXISTS u2745;
    

  • 建立用户权限变更流程，比如用户升级服务时调整资源限制、添加扩展权限等。

• 监控与审计

  • 启用pg_stat_statements扩展，跟踪用户的查询性能，及时发现慢查询；
  • 配置PG日志记录用户的敏感操作（如DROP TABLE、ALTER USER），便于事后审计；
  • 监控每个用户数据库的磁盘占用、CPU使用率，避免单个用户资源过载影响全局服务。

内容的提问来源于stack exchange，提问作者xyz