Python处理PostgreSQL COPY TO导出CSV的权限问题与邮件发送

• 服务端COPY TO的执行主体是运行PostgreSQL服务的系统用户（通常为postgres），生成的文件默认属于postgres用户，存储路径/var/lib/pgsql/的目录权限仅对postgres用户、root用户开放读写/移动权限，普通用户运行Python脚本时无权限操作该目录下的文件，这就是shutil.move、mv命令、os.system操作均报权限拒绝的本质原因。
• 直接修改COPY TO的输出路径到用户目录时，由于postgres服务进程没有用户目录的写权限，因此会触发“Permission denied”报错，这类权限限制是数据库服务的安全设计，不建议通过放开目录全局777权限绕过。

方案一（推荐）：替换为客户端侧流式导出，跳过目录权限限制 #

完全不需要往/var/lib/pgsql/目录写文件，直接通过psycopg2的内置copy_expert方法，将查询结果通过数据库连接流式传输到Python进程有权限操作的本地路径，同时实现“直接导出到自定义用户目录”和“无权限障碍”两个目标。
修改原代码中的fetch_all_postgres函数为如下逻辑：

def export_query_to_csv(pgsql_conn, target_output_path):
    cur = pgsql_conn.cursor()
    # 核心改动：将TO服务端文件路径改为TO STDOUT，由Python进程直接写文件
    export_sql = """COPY ( select 1 as a ,2 as b ,3 as c) TO STDOUT
                    WITH DELIMITER ',' CSV HEADER;"""
    # 直接写权限完全可控的自定义目录路径，无跨目录权限问题
    with open(target_output_path, 'w', encoding='utf-8') as csv_file:
        cur.copy_expert(export_sql, csv_file)
    pgsql_conn.commit()

调用时直接传入目标路径/home/user/Desktop/somefolder/output.csv即可，写文件的主体是当前运行Python脚本的用户，不存在权限阻碍。如果不需要落盘留存文件，还可以直接将流式内容读到内存中作为邮件附件，完全省去本地文件操作环节。

方案二：必须保留服务端COPY TO逻辑的适配方式 #

如果业务场景必须使用服务端COPY生成文件，可通过两个小改动解决权限问题：

• PostgreSQL 11及以上版本，在COPY命令中增加文件权限参数，让生成的文件对所有用户可读：

  copy ( select 1 as a ,2 as b ,3 as c) to '/var/lib/pgsql/output.csv' 
  with delimiter ',' csv header MODE 0644;
  

  生成的文件权限为644，普通用户可直接读取内容发邮件，不需要移动/删除原文件。
• 需要移动文件时，针对该单一操作配置用户的免密sudo权限，不要全局放开目录权限，在保证安全的前提下完成移动操作。

• 不要使用Python内置关键字copy作为收件人抄送列表的变量名，建议修改为cc_receivers，避免触发语法报错。
• 打开附件文件时增加上下文管理（with语句）自动释放文件句柄，避免长期持有文件锁。

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