PostgreSQL如何限制用户调整work_mem的权限？

限制用户修改work_mem的权限 #

PostgreSQL默认允许普通用户在会话级别调整work_mem，要限制这个权限，有两种直接的方式：

• 收回全局参数修改权限
  普通用户能修改work_mem是因为默认拥有SET权限，执行以下语句收回所有普通用户的该权限：

  REVOKE SET ON PARAMETER work_mem FROM PUBLIC;
  

  若需要给特定用户保留修改权限，可单独授予：

  GRANT SET ON PARAMETER work_mem TO specific_user;
  

• 为用户设置固定work_mem并锁定
  如果你想让某些用户只能使用指定的work_mem值，无法自行修改，可以通过ALTER ROLE强制设置：

  ALTER ROLE target_user SET work_mem = '64MB';
  

  执行后，该用户登录后work_mem会自动应用这个值，无权限的情况下无法通过SET work_mem修改。

替代Oracle PGA_AGGREGATE_LIMIT的方案 #

PostgreSQL 12确实没有直接对应Oracle PGA_AGGREGATE_LIMIT的参数，但可以通过以下方式实现类似的内存聚合限制：

• 通过全局参数估算上限
  先计算系统可分配给work_mem类操作的总内存（比如系统总内存扣除shared_buffers、系统预留内存后的值），再结合max_worker_processes设置全局work_mem的安全值。例如系统有32GB内存，预留8GB给系统和shared_buffers，max_worker_processes设为64，那么全局work_mem可设为375MB（24GB/64），再配合上面的权限限制，避免用户私自调高。

• 使用resource_groups扩展
  PostgreSQL 10及以上版本支持resource_groups扩展，可对角色的资源使用进行精细化限制，包括内存：

  1. 先启用扩展：

     CREATE EXTENSION resource_groups;
     

  2. 创建带内存限制的资源组：

     CREATE RESOURCE GROUP user_restricted_group WITH (
       memory_limit = '20GB', -- 该组所有进程总内存上限
       max_memory_per_process = '512MB' -- 单个进程内存上限
     );
     

  3. 将目标用户分配到该组：

     ALTER ROLE target_user SET resource_group = user_restricted_group;
     

  这样该组内的所有会话总内存消耗不会超过20GB，单个进程最多使用512MB，能有效避免单用户或多用户并发导致的OOM。

其他OOM预防补充措施 #

• 监控work_mem使用
  通过pg_stat_activity实时查看当前会话的work_mem设置及运行中的查询：

  SELECT usename, current_setting('work_mem') AS current_work_mem, query 
  FROM pg_stat_activity 
  WHERE state = 'active';
  

  也可以通过EXPLAIN ANALYZE查看查询计划，定位消耗大量work_mem的操作（如排序、哈希连接）。

• 优化高内存消耗查询
  对需要大量排序或哈希操作的查询，添加合适的索引避免排序、拆分大查询为多个小查询，或调整查询逻辑减少内存占用。

• 合理配置全局内存参数
  确保shared_buffers（建议为系统内存的25%）、maintenance_work_mem（用于VACUUM等维护操作）等参数设置合理，避免抢占work_mem可用的内存资源。

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