如何限制PostgreSQL用户仅能查询返回至多一行的数据？

可以实现这类需求，但无法通过常规的GRANT SELECT权限直接完成，需要借助安全定义器函数封装查询逻辑，结合权限控制来实现。以下是两种可行的具体方案：

方案一：针对特定查询条件的专用函数 #

如果用户的查询条件是固定类型（比如通过id查询），可以创建专用函数，在函数内部先检查符合条件的行数，超过1行则抛出异常，否则返回结果：

-- 创建返回单行数据的函数
CREATE OR REPLACE FUNCTION get_my_table_by_id(p_id INT)
RETURNS my_table AS $$
DECLARE
    result_row my_table;
    row_count INT;
BEGIN
    -- 统计符合条件的行数
    SELECT COUNT(*) INTO row_count FROM my_table WHERE id = p_id;
    
    IF row_count > 1 THEN
        RAISE EXCEPTION '查询结果超过1行，禁止执行';
    ELSIF row_count = 0 THEN
        RETURN NULL; -- 无结果时返回NULL，也可根据需求抛出异常
    ELSE
        SELECT * INTO result_row FROM my_table WHERE id = p_id;
        RETURN result_row;
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 给用户joe赋予函数执行权限
GRANT EXECUTE ON FUNCTION get_my_table_by_id(INT) TO joe;

-- 收回joe对my_table的直接查询权限（若之前已授予）
REVOKE SELECT ON my_table FROM joe;

用户joe只能通过调用SELECT get_my_table_by_id(17);来获取数据，若传入的id对应多行数据，函数会直接报错阻止执行。

方案二：支持通用查询条件的安全函数（需注意SQL注入） #

如果需要支持灵活的查询条件，可以创建通用函数，但必须严格处理SQL注入风险，建议使用参数化查询而非直接拼接字符串：

-- 安全的通用查询函数（示例以id和name为查询条件）
CREATE OR REPLACE FUNCTION get_my_table_single_row(p_id INT, p_name TEXT)
RETURNS my_table AS $$
DECLARE
    result_row my_table;
    row_count INT;
BEGIN
    -- 统计符合组合条件的行数
    SELECT COUNT(*) INTO row_count 
    FROM my_table 
    WHERE (p_id IS NULL OR id = p_id) 
      AND (p_name IS NULL OR name = p_name);
    
    IF row_count > 1 THEN
        RAISE EXCEPTION '查询结果超过1行，禁止执行';
    END IF;
    
    -- 获取单行结果
    SELECT * INTO result_row 
    FROM my_table 
    WHERE (p_id IS NULL OR id = p_id) 
      AND (p_name IS NULL OR name = p_name)
    LIMIT 1;
    
    RETURN result_row;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 给joe赋予函数执行权限
GRANT EXECUTE ON FUNCTION get_my_table_single_row(INT, TEXT) TO joe;

这种方式通过预设参数来支持灵活查询，避免了直接拼接用户输入的SQL语句，降低了注入风险。

关键注意事项 #

• SECURITY DEFINER属性会让函数以创建者的权限执行，确保函数能访问my_table，而用户joe无需直接拥有表的权限。
• 若要完全禁止用户直接访问表，需确保已收回所有针对my_table的直接权限，包括继承自角色的权限。
• 通用函数的设计需严格控制输入参数范围，避免SQL注入漏洞。

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