使用PostgreSQL存储过程生成.dat文件时遇超级用户权限错误

问题根源 #

PostgreSQL的COPY ... TO直接写入服务器文件时，默认要求超级用户权限——这是出于安全限制，防止普通用户随意操作服务器文件系统，和Oracle依赖UTL_FILE目录对象权限的机制完全不同。

解决方案 #

1. 授予专用角色权限（推荐）

无需给用户超级权限，只需授予pg_write_server_files系统角色，该角色专门用于授权服务器文件写入操作：

GRANT pg_write_server_files TO your_procedure_user;

授权后，存储过程中即可正常使用COPY TO写入文件，示例代码：

CREATE OR REPLACE PROCEDURE export_to_dat()
LANGUAGE plpgsql
AS $$
BEGIN
    COPY (SELECT * FROM your_target_table)
    TO '/path/to/your/target/file.dat'
    WITH (FORMAT csv, DELIMITER '|', HEADER false);
END;
$$;

注意：指定的路径必须是PostgreSQL服务器进程能访问的路径，且操作系统用户（通常为postgres）需拥有该路径的写入权限。

2. 使用pg_file_write函数灵活控制

如果需要更精细的文件写入逻辑，可以结合COPY ... TO STDOUT和pg_file_write函数实现，同样需要pg_write_server_files权限：

CREATE OR REPLACE PROCEDURE export_to_dat()
LANGUAGE plpgsql
AS $$
DECLARE
    file_content text;
BEGIN
    -- 将查询结果转换为文本格式
    COPY (SELECT * FROM your_target_table) TO STDOUT WITH (FORMAT csv, DELIMITER '|') INTO file_content;
    -- 写入文件，第三个参数为false表示覆盖现有文件，true则追加内容
    PERFORM pg_file_write('/path/to/your/target/file.dat', file_content, false);
END;
$$;

3. 客户端侧导出（非存储过程场景）

如果无需在存储过程中执行导出，而是从客户端操作，推荐使用psql的\copy命令——该命令无需服务器端超级权限，文件直接生成在客户端本地：

psql -U your_user -d your_database -c "\copy (SELECT * FROM your_target_table) TO '/local/client/path/file.dat' WITH (FORMAT csv, DELIMITER '|')"

安全提示 #

• 仅将pg_write_server_files权限授予可信用户，避免滥用文件写入权限。
• 尽量限制服务器文件的写入路径，禁止写入系统关键目录。

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