从csv文件在PostGreSQL复制中填充表时出现权限错误 [英] permission error when populating a table in PostGreSQL copying from a csv file

问题描述

我知道其他人已经提出了这个问题，但是即使尝试以前的建议，我仍然会收到此错误...

I know this issue has already been raised by others, but even trying previous suggestions I still get this error...

当我尝试从csv文件填充表复制时，出现权限错误.

When I try to populate a table copying from a csv file, I get a permission error.

COPY Eurasia FROM '/Users/Oritteropus/Desktop/eurasia1.csv' CSV HEADER;

ERROR: could not open file "/Users/Oritteropus/Desktop/eurasia1.csv" for reading: Permission denied
SQL state: 42501

如先前在这些情况下建议的那样，我更改了文件的权限(chmod 711 eurasia1.csv或chmod a + r eurasia1.csv)，并且还更改了用户权限:

As previously suggested in these cases, I changed the permission of the file (chmod 711 eurasia1.csv or chmod a+r eurasia1.csv) and I also changed the user rights with:

ALTER USER postgres WITH SUPERUSER; #where postgres is my user

但是，我仍然遇到相同的错误. 我也尝试从pgAdmin手动更改特权，但是似乎已经赋予了平均特权. 我正在Mac OS上工作，并且正在使用PostGreSQL 9.2.4.

However, I still get the same error. I also tried to manually change the privileges from pgAdmin but seems avery privilege is already given. I'm working on a Mac Os and I'm using PostGreSQL 9.2.4.

有什么建议吗?谢谢

推荐答案

最好的选择是更改并使用COPY FROM STDIN，因为这样可以避免很多权限问题.

The best option is to change and use COPY FROM STDIN as that avoids quite a number of permissions issues.

或者，您可以确保postgres用户可以访问该文件.但是，出于几个原因，这很少比COPY FROM STDIN好.

Alternatively you can make sure that the postgres user can access the file. This rarely better than COPY FROM STDIN however for a couple reasons.

1. COPY TO STDOUT可能会破坏您的数据.因为这涉及到PostgreSQL的文件I/O，如果COPY FROM STDIN中存在错误，那也可能是一个问题.

1. COPY TO STDOUT can conceivably corrupt your data. Because this involves file I/O by PostgreSQL if bugs exist in COPY FROM STDIN that could be a problem too.

如果您出于自动化/存储过程的考虑而在服务器端进行此操作，那么这很少是一个胜利，因为您将事务性和非事务性效果结合在一起. COPY TO STDOUT和COPY FROM STDIN没有这些问题. (例如，您不必怀疑inode的时间是否实际上意味着文件已被正确处理).

If you are doing it on the server side because of automation/stored proc concerns, this is rarely a win, as you are combining transactional and non-transactional effects. COPY TO STDOUT and COPY FROM STDIN do not have these issues. (For example, you don't have to wonder whether the atime of the inode actually means the file was properly processed).

这篇关于从csv文件在PostGreSQL复制中填充表时出现权限错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！