PostgreSQL中如何强制特定用户SELECT查询返回最大行数？

当然可以实现这个需求，而且完全不需要用户手动干预——PostgreSQL提供了两种主要的方案来完成这种强制查询重写，下面我会详细拆解：

方案1：使用查询重写规则（Rewrite Rules）——针对特定表/视图 #

如果你只需要限制用户对某些特定表的查询行数，这种方法简单直接，不需要额外扩展：

1. 首先以超级用户身份登录PostgreSQL（比如postgres用户），因为创建规则需要较高权限。
2. 针对目标用户和目标表创建规则，自动在SELECT后追加LIMIT：

CREATE RULE limit_user_select AS ON SELECT TO restricted_user
TO my_table DO INSTEAD
SELECT * FROM my_table LIMIT 100; -- 替换成你需要的最大行数

• 这里的restricted_user是你要限制的目标用户名，my_table是目标表，100是预设的最大返回行数。
• 这个规则会让该用户对my_table的所有SELECT查询自动变为带LIMIT的版本，用户完全不需要手动添加任何内容。

不过这种方法的局限是：需要给每个要限制的表单独创建规则，如果要覆盖所有表，操作会比较繁琐。

方案2：使用事件触发器（Event Triggers）——全局覆盖所有SELECT查询 #

如果你需要让限制对该用户的所有SELECT查询生效（不管查询哪个表/视图），事件触发器是更理想的选择，它能在查询执行前修改语法树（AST），自动追加LIMIT子句：

步骤1：创建用于重写查询的函数 #

这个函数会捕获用户的SELECT查询，检查执行用户是否为目标用户，然后修改查询AST添加LIMIT：

CREATE OR REPLACE FUNCTION limit_user_queries()
RETURNS event_trigger AS $$
DECLARE
  query_ast json;
BEGIN
  -- 只处理目标用户的SELECT查询
  IF current_user = 'restricted_user' AND tg_tag = 'SELECT' THEN
    -- 获取当前查询的语法树
    query_ast := pg_get_query_rewrite(pg_event_trigger_get_rewrite_oid());
    -- 修改语法树，追加LIMIT子句（这里设置为100行）
    query_ast := jsonb_set(query_ast, '{0,Stmt,SelectStmt,limitClause}', 
                           '{"A_Const": {"val": {"Integer": {"ival": 100}}, "location": -1}}'::jsonb);
    -- 用修改后的语法树替换原查询
    PERFORM pg_event_trigger_rewrite_query(query_ast::text);
  END IF;
END;
$$ LANGUAGE plpgsql;

步骤2：创建事件触发器，绑定上述函数 #

CREATE EVENT TRIGGER limit_user_select_trigger
ON sql_parse
EXECUTE FUNCTION limit_user_queries();

• 这个触发器会在每次SQL解析时触发，检查当前执行用户是否为restricted_user，如果是且为SELECT查询，就自动在查询末尾加上LIMIT 100。
• 注意：事件触发器需要PostgreSQL 9.3及以上版本，且只有超级用户能创建和管理。

注意事项 #

• 权限要求：不管用哪种方法，都需要超级用户权限来创建规则或触发器，普通用户无法操作。
• 自定义逻辑：如果用户的查询已经带有LIMIT子句，上述方案2的代码会直接替换原有LIMIT。如果你想保留用户设置的LIMIT（只要不超过最大值），可以修改函数逻辑，先检查原查询是否有LIMIT，再决定是否替换或限制。
• 验证测试：创建完成后，切换到目标用户执行SELECT * FROM any_table;，应该会自动返回最多100行，无需手动添加LIMIT。

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