PostgreSQL-不一致的COPY权限错误 [英] PostgreSQL - inconsistent COPY permissions errors

问题描述

我正在Windows 7(32位)计算机上使用EnterpriseDB pgAdmin III(1.12.1版)来与远程Linux服务器上的PostgreSQL数据库一起使用.我以用户postgres身份登录，这使我可以访问$ PGDATA目录(在这种情况下，可以在这里找到:/var/lib/pgsql/data/)

I am using the EnterpriseDB pgAdmin III (v. 1.12.1) on a Windows 7, 32-bit machine to work with PostgreSQL databases on a remote Linux server. I am logged in as the user postgres, which allows me to access the $PGDATA directory (in this instance, it is found here: /var/lib/pgsql/data/)

如果我通过终端登录服务器，运行psql，并使用\copy命令将csv文件中的数据导入到新创建的表中，则没有问题.

If I log into the server via a terminal, run psql, and use the \copy command to import data from csv files into newly created tables, I have no problems.

但是，如果我使用的是pgAdmin，则使用COPY命令将csv文件中的数据导入到新创建的表中.

If I'm in pgAdmin, however, I use the COPY command to import data from csv files into newly created tables.

COPY table_name FROM '/var/lib/pgsql/data/file.csv' 
WITH DELIMITER AS ',' csv header

有时这可以正常工作，有时我会遇到权限错误:

Sometimes this works fine, other times I get a permissions error:

错误:无法打开文件"/var/lib/pgsql/data/file.csv"以供读取:权限被拒绝 SQL状态:42501

ERROR: could not open file '/var/lib/pgsql/data/file.csv" for reading: Permission denied SQL state: 42501

错误的不一致使我感到困惑.当出现错误时，我将文件权限更改为644-777之间的任何值，但没有任何效果.我也尝试将文件移到其他文件夹，例如var/tmp/，也没有任何作用.

It is the inconsistency of the error that is confusing to me. When the error arises, I change the file permission to anywhere from 644 - 777, with no effect. I also try moving the file to other folders, e.g., var/tmp/, also with no effect.

有什么想法吗?

推荐答案

问题是通过目录对文件的访问权限.例如，Postgres用户无权访问您的主文件夹.答案是使用所有用户都可以访问的文件夹(例如/tmp)，或使用正确的权限创建一个文件夹，以便任何用户都可以在那里访问/读取/写入，这是一种用户共享的文件夹.

The problem is the access permissions trough the directories to the file. Postgres user does not have access to your home folder, for example. The answer is to use a folder all users have access like /tmp, or create one with the correct permissions so any user can access/read/write there, a sort of users shared folder.

这篇关于PostgreSQL-不一致的COPY权限错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！