PostgreSQL Default Permissions未对其他用户创建对象生效问题咨询

为什么配置的默认权限未对其他用户创建的对象生效 #

PostgreSQL的默认权限规则默认和执行配置命令的用户绑定，你运行ALTER DEFAULT PRIVILEGES时没有指定FOR ROLE子句，这套规则就只会作用于「你个人账号后续创建的对象」，对其他用户创建的对象完全不生效。哪怕你是Schema所有者，也没有权限自动访问其他用户在该Schema下创建的对象，这是PostgreSQL权限体系的原生设计。

确保所有用户创建的对象遵循统一默认权限的方案 #

方案1：针对所有有建表权限的用户单独配置默认权限 #

如果团队内有建表权限的用户数量不多，可以为每个用户单独配置默认权限，语法如下：

-- 为user_1配置默认权限，他创建的表自动应用规则
ALTER DEFAULT PRIVILEGES FOR ROLE "user_1" IN SCHEMA "schema_name"
GRANT ALL ON TABLES TO "admin_me" WITH GRANT OPTION;
ALTER DEFAULT PRIVILEGES FOR ROLE "user_1" IN SCHEMA "schema_name"
GRANT SELECT ON TABLES TO "user_1", "user_2";

-- 为user_2配置同理（如果他也有建表权限）
ALTER DEFAULT PRIVILEGES FOR ROLE "user_2" IN SCHEMA "schema_name"
GRANT ALL ON TABLES TO "admin_me" WITH GRANT OPTION;
ALTER DEFAULT PRIVILEGES FOR ROLE "user_2" IN SCHEMA "schema_name"
GRANT SELECT ON TABLES TO "user_1", "user_2";

该方案无需额外启用特殊功能，缺点是后续新增有建表权限的用户时，需要同步补充对应的默认权限配置。你可以通过\ddp命令（psql环境）或查询pg_default_acl系统表，查看所有已配置的默认权限是否正确。

方案2：使用事件触发器自动赋权（推荐） #

如果希望一劳永逸，不需要每次新增用户都修改配置，可以通过事件触发器实现全局自动赋权，需要超级用户权限执行以下配置：

-- 创建自动赋权的存储函数
CREATE OR REPLACE FUNCTION auto_grant_table_privileges()
RETURNS event_trigger AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'CREATE TABLE'
    LOOP
        -- 可根据需求调整过滤的Schema范围
        IF obj.schema_name = 'schema_name' THEN
            EXECUTE format('GRANT ALL ON TABLE %I.%I TO "admin_me" WITH GRANT OPTION', obj.schema_name, obj.object_name);
            EXECUTE format('GRANT SELECT ON TABLE %I.%I TO "user_1", "user_2"', obj.schema_name, obj.object_name);
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 绑定DDL事件触发器，建表完成后自动执行赋权逻辑
CREATE EVENT TRIGGER trigger_auto_grant_table_priv
ON ddl_command_end WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION auto_grant_table_privileges();

该方案不需要后续维护，所有用户在目标Schema下创建的表都会自动应用你设定的权限规则。

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