Supabase中PostgREST函数API调用无法更新表的问题求助

问题背景 #

我在使用内置PostgREST服务器的Supabase平台，创建了一个需要校验和验证的更新函数set_value：

CREATE OR REPLACE FUNCTION set_value(_id TEXT, _value INTEGER, _check INTEGER) RETURNS BOOLEAN AS $$
BEGIN
  IF get_checksum(_value) = _check THEN
    UPDATE values_table SET score = _value WHERE device_id = _id;
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- 按文档要求创建函数后重载 schema
NOTIFY pgrst, 'reload schema';

直接通过SQL调用该函数时，能正常返回TRUE，且values_table中的score值成功更新：

SELECT * FROM set_value('SOME_ID', 123, 123456)
-- | Results |
-- | true    |

但通过POST请求调用API时，虽然能返回正确响应（校验匹配返回true，否则false），但values_table中的数据始终没有变更。API请求示例如下：

curl --location --request POST 'https://<myapp>.supabase.co/rest/v1/rpc/set_value' \
--header 'Authorization: Bearer <mytoken>' \
--header 'apiKey: <mykey>' \
--header 'Content-Type: application/json' \
--data-raw '{
    "_id": "_deviceId",
    "_value": 123,
    "_check": 123456
}'

问题原因及解决办法 #

1. 角色权限不足（最常见） #

直接用SQL调用函数时，你大概率使用的是超级管理员角色，但PostgREST通过API调用函数时，默认使用anon或authenticated角色，这两个角色可能没有values_table的更新权限。

• 解决方式：给对应角色赋予更新权限

-- 如果是登录用户调用（用authenticated角色的token）
GRANT UPDATE (score) ON values_table TO authenticated;
-- 如果是匿名访问调用
GRANT UPDATE (score) ON values_table TO anon;

2. 函数安全属性设置问题 #

PostgreSQL函数默认使用SECURITY INVOKER（调用者权限执行），即API调用时会用anon/authenticated角色的权限执行函数，如果该角色没有数据表更新权限，就无法修改数据。

• 若需要以函数创建者的权限执行（需注意安全风险），可以修改函数为SECURITY DEFINER：

CREATE OR REPLACE FUNCTION set_value(_id TEXT, _value INTEGER, _check INTEGER) RETURNS BOOLEAN AS $$
BEGIN
  IF get_checksum(_value) = _check THEN
    UPDATE values_table SET score = _value WHERE device_id = _id;
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- 建议设置搜索路径避免安全隐患
ALTER FUNCTION set_value(_id TEXT, _value INTEGER, _check INTEGER) SET search_path = public;

3. 参数匹配问题 #

虽然函数返回了true，但可能UPDATE语句没有匹配到任何行（比如API传入的_id值和数据库中device_id的实际值不匹配，存在大小写、特殊字符差异）。可以修改函数返回受影响行数来排查：

CREATE OR REPLACE FUNCTION set_value(_id TEXT, _value INTEGER, _check INTEGER) RETURNS JSON AS $$
DECLARE
  affected_rows INTEGER;
BEGIN
  IF get_checksum(_value) = _check THEN
    UPDATE values_table SET score = _value WHERE device_id = _id;
    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    RETURN json_build_object('success', TRUE, 'affected_rows', affected_rows);
  ELSE
    RETURN json_build_object('success', FALSE, 'affected_rows', 0);
  END IF;
END;
$$ LANGUAGE plpgsql;

调用API后查看affected_rows值，如果为0，说明_id参数没有匹配到对应数据行。

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