PostgreSQL 9.2.23定期更新表权限：批量撤销后重授权

嘿，这个需求我刚好碰到过，给你几个适配PostgreSQL 9.2.23的可行方案，帮你搞定批量撤销+重新授权的流程：

1. 生成动态REVOKE语句（手动执行版） #

PostgreSQL没有直接的“撤销所有用户权限”的内置命令，但我们可以通过查询系统视图生成对应的撤销语句，再执行它们。

比如针对单张表my_table（假设在public schema下），运行以下查询：

SELECT 'REVOKE ALL PRIVILEGES ON TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' FROM ' || quote_ident(p.grantee) || ';'
FROM information_schema.table_privileges p
JOIN pg_class c ON c.relname = p.table_name
JOIN pg_namespace n ON n.nspname = p.table_schema
WHERE c.relkind = 'r' -- 仅针对普通表，排除视图、序列等
AND p.table_name = 'my_table'
AND p.table_schema = 'public'
AND p.grantee != (SELECT rolname FROM pg_authid WHERE oid = c.relowner); -- 跳过表所有者，避免报错

这个查询会输出一系列类似REVOKE ALL PRIVILEGES ON TABLE public.my_table FROM user_x;的语句，你可以直接复制这些语句执行，完成批量撤销。

如果要处理某个schema下的所有表，去掉p.table_name = 'my_table'的条件即可：

SELECT 'REVOKE ALL PRIVILEGES ON TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' FROM ' || quote_ident(p.grantee) || ';'
FROM information_schema.table_privileges p
JOIN pg_class c ON c.relname = p.table_name
JOIN pg_namespace n ON n.nspname = p.table_schema
WHERE c.relkind = 'r'
AND n.nspname = 'public'
AND p.grantee != (SELECT rolname FROM pg_authid WHERE oid = c.relowner);

2. 自动化执行（PL/pgSQL函数版） #

如果需要定期自动执行，可以写一个PL/pgSQL函数，自动生成并执行撤销语句：

CREATE OR REPLACE FUNCTION revoke_all_table_privileges(p_schema text, p_table text)
RETURNS void AS $$
DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT 'REVOKE ALL PRIVILEGES ON TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' FROM ' || quote_ident(p.grantee) || ';' AS revoke_stmt
        FROM information_schema.table_privileges p
        JOIN pg_class c ON c.relname = p.table_name
        JOIN pg_namespace n ON n.nspname = p.table_schema
        WHERE c.relkind = 'r'
        AND n.nspname = p_schema
        AND c.relname = p_table
        AND p.grantee != (SELECT rolname FROM pg_authid WHERE oid = c.relowner)
    LOOP
        EXECUTE rec.revoke_stmt;
        RAISE NOTICE '已执行撤销语句: %', rec.revoke_stmt;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

调用函数时，只需传入schema和表名：

SELECT revoke_all_table_privileges('public', 'my_table');

如果要处理整个schema的所有表，修改函数参数和查询条件即可（比如去掉p_table的筛选，改成仅传入schema）。

后续授权步骤 #

完成撤销后，就可以给你的授权用户列表授予SELECT权限了。如果你的用户列表存在某个表中，也可以用类似的动态SQL生成GRANT语句，比如：

SELECT 'GRANT SELECT ON public.my_table TO ' || quote_ident(username) || ';'
FROM your_user_list_table;

复制生成的语句执行，或者用函数自动执行。

注意事项 #

• PostgreSQL 9.2的系统视图和语法完全支持上述方案，但要注意information_schema.table_privileges会包含所有角色（包括用户和组角色）的权限。
• 执行前建议先查看生成的语句，确认没有误操作（特别是生产环境）。
• 表所有者的权限无法被撤销，所以我们在查询中排除了所有者，避免执行时抛出错误。

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