排查PostgreSQL环境间权限不一致问题的系统方法

问题背景 #

数据库开发版本中功能正常，但相同架构的托管版本执行api.register函数时出现权限拒绝错误，需系统排查并解决该问题。

托管环境权限错误详情 #

执行以下SQL语句：

select api.register('google', '333344555');

返回错误信息：

select api.register('google', '333344555');
INFO:  api.register: api
INFO:  api.login: api
ERROR:  permission denied for table auth_ids_link_user
CONTEXT:  SQL function "login" statement 1
SQL statement "select auth.login(auth_agent::core.auth_agent, auth_id)"
PL/pgSQL function register(text,text,text) line 35 at SQL statement

开发环境（PostgreSQL v13.1）权限验证 #

查询auth_ids_link_user表的权限配置：

select grantor, grantee, table_schema, table_name, privilege_type
from information_schema.role_table_grants
where table_name in (select 'auth_ids_link_user');

查询结果：

grantor  | grantee | table_schema |     table_name     | privilege_type
----------+---------+--------------+--------------------+----------------
 postgres | api     | core         | auth_ids_link_user | INSERT
(1 row)

托管环境（PostgreSQL v13.7）权限排查 #

1. api角色对目标表的权限 #

执行查询：

select grantor, grantee, table_schema, table_name, privilege_type
from information_schema.role_table_grants
where grantee = 'api' and table_name in (select 'auth_ids_link_user');

结果显示api角色拥有目标表的INSERT权限，与开发环境一致：

grantor | grantee | table_schema |     table_name     | privilege_type
---------+---------+--------------+--------------------+----------------
 doadmin | api     | core         | auth_ids_link_user | INSERT
(1 row)

2. auth.login函数执行权限 #

查询api角色对auth.login函数的执行权限：

grantor |  grantee  | specific_schema | routine_schema | routine_name | privilege_type
---------+-----------+-----------------+----------------+--------------+----------------
 auth    | api       | auth            | auth           | login        | EXECUTE

确认api角色拥有该函数的EXECUTE权限。

3. auth角色对目标表的权限（函数为定义者权限运行） #

由于auth.login函数以定义者权限运行，需检查auth角色对auth_ids_link_user表的权限：

SELECT schemaname AS Schema, tablename AS Name, 'table' AS Type,
       array_to_string(acl, E'\n') AS "Access privileges",
       array_to_string(colacl, E'\n') AS "Column privileges",
       array_to_string(policies, E'\n') AS Policies
FROM pg_tables
LEFT JOIN (
    SELECT tablename, array_agg(policyname || ': ' || policy) AS policies
    FROM pg_policies
    GROUP BY tablename
) p ON pg_tables.tablename = p.tablename
WHERE tablename = 'auth_ids_link_user';

查询结果显示：

Schema |        Name        | Type  |    Access privileges    | Column privileges |                                             Policies
--------+--------------------+-------+-------------------------+-------------------+---------------------------------------------------------------------------------------------------
 core   | auth_ids_link_user | table | doadmin=arwdDxt/doadmin+| user_id:         +| Api can do anything when current_user_id:                                                        +
        |                    |       | api=a/doadmin           |   auth=rx/doadmin |   (u): ((util.current_user_id() = user_id) AND (current_setting('role'::text) = 'webuser'::text))+
        |                    |       |                         |                   |   (c): ((util.current_user_id() = user_id) AND (current_setting('role'::text) = 'webuser'::text))+
        |                    |       |                         |                   |   to: api                                                                                        +
        |                    |       |                         |                   | Auth can view all auth_ids to authenticate anonymous users (r):                                  +
        |                    |       |                         |                   |   (u): true                                                                                      +
        |                    |       |                         |                   |   to: auth
(1 row)

需求说明 #

• auth角色仅需判断auth_ids_link_user表中记录是否存在
• api角色通过INSERT权限向该表插入新记录

问题定位与解决 #

问题根源是auth角色对auth_ids_link_user表的SELECT权限仅限制在user_id列，权限过于严格，导致auth.login函数执行时无法读取表中其他必要字段，触发权限拒绝错误。

修正权限的SQL语句如下：

-- 原权限设置（仅允许SELECT user_id列）
grant references(user_id), select(user_id) on table core.auth_ids_link_user to auth;

-- 修正后的权限设置（允许全表SELECT）
grant references(user_id), select on table core.auth_ids_link_user to auth;

注：开发环境中postgres角色默认继承其他角色权限，该差异已在托管环境中处理。

内容的提问来源于stack exchange，提问作者Edmund's Echo