PostgreSQL表权限授予不生效，与生产环境状态不符求助

操作背景 #

以postgres身份对jira_user执行了以下权限授予命令：

GRANT USAGE ON schema public TO jira_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jira_user;
grant all privileges on database jiradb to jira_user;

测试环境权限查询结果 #

执行查询语句SELECT * FROM information_schema.table_privileges where table_name = 'AO_013613_ACTIVITY_SOURCE';后，得到结果如下：

grantor  |  grantee  | table_catalog | table_schema |        table_name         | privilege_type | is_grantable | with_hierarchy 
----------+-----------+---------------+--------------+---------------------------+----------------+--------------+----------------
 postgres | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | INSERT         | NO           | NO
 postgres | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | SELECT         | NO           | YES
 postgres | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | UPDATE         | NO           | NO
 postgres | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | DELETE         | NO           | NO
 postgres | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | TRUNCATE       | NO           | NO
 postgres | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | REFERENCES     | NO           | NO
 postgres | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | TRIGGER        | NO           | NO

生产环境预期权限结果 #

该表在生产环境的权限结果应为：

grantor  |  grantee  | table_catalog | table_schema |        table_name         | privilege_type | is_grantable | with_hierarchy 
-----------+-----------+---------------+--------------+---------------------------+----------------+--------------+----------------
 jira_user | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | INSERT         | YES          | NO
 jira_user | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | SELECT         | YES          | YES
 jira_user | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | UPDATE         | YES          | NO
 jira_user | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | DELETE         | YES          | NO
 jira_user | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | TRUNCATE       | YES          | NO
 jira_user | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | REFERENCES     | YES          | NO
 jira_user | jira_user | jiradb        | public       | AO_013613_ACTIVITY_SOURCE | TRIGGER        | YES          | NO

疑问与需求 #

对PostgreSQL不熟悉，原以为执行上述授权命令后测试环境权限会与生产环境一致，但实际结果不符，不确定grantor字段差异是否影响权限生效，寻求解决办法。

问题分析与解决步骤 #

1. 理解字段差异的原因 #

• grantor字段：代表授予权限的用户。生产环境中grantor是jira_user，说明该表的所有者就是jira_user，所有者默认拥有表的所有权限，且自带转授权限（is_grantable=YES）；测试环境中grantor是postgres，是因为权限是由postgres用户授予的，而非用户自身作为所有者拥有的权限。
• is_grantable=NO：默认执行GRANT命令时没有添加WITH GRANT OPTION，所以被授予者无法将获得的权限转授给其他用户，因此该字段为NO。

2. 调整测试环境权限至与生产一致 #

要让测试环境权限和生产环境完全匹配，核心是将表的所有权转移给jira_user，步骤如下：

步骤1：确认表的当前所有者

执行以下查询确认目标表的所有者：

SELECT tableowner FROM pg_tables WHERE tablename = 'AO_013613_ACTIVITY_SOURCE';

步骤2：修改表的所有权

如果查询结果显示所有者是postgres，执行以下命令将所有权转移给jira_user：

ALTER TABLE AO_013613_ACTIVITY_SOURCE OWNER TO jira_user;

步骤3：批量处理其他Jira插件表（可选）

如果还有其他类似的AO_开头的Jira插件表需要统一调整，可使用动态SQL批量修改所有权：

DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'AO_%'
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(rec.tablename) || ' OWNER TO jira_user;';
    END LOOP;
END $$;

步骤4：验证权限调整结果

再次执行权限查询语句：

SELECT * FROM information_schema.table_privileges where table_name = 'AO_013613_ACTIVITY_SOURCE';

此时结果应与生产环境一致：grantor为jira_user，所有权限的is_grantable均为YES。

3. 关于权限生效的说明 #

当前测试环境中jira_user已经拥有该表的所有操作权限（INSERT/SELECT/UPDATE等），只是无法将权限转授给其他用户。如果Jira业务不需要转授权限，当前权限已能满足使用；但为了保持与生产环境的一致性，建议调整表所有权。

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