postgresql 如何允许在 Ubuntu 中使用 postgres 访问 CSV 文件的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29004860/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to allow permission to access CSV file using postgres in Ubuntu
提问by
I am using the following command :
我正在使用以下命令：
copy (select so.name,
so.date_order,
sol.name,
sol.product_Id,
sol.product_uom_qty ,
ai.number,
ai.date_invoice ,
so.amount_total ,
so.amount_tax
from sale_order so ,
sale_order_line sol ,
account_invoice ai
where so.id = sol.order_id
and so.name = ai.origin
and ai.state='open')
to '/home/ekodev/Documents/test1.csv' delimiter ',' csv header;
However, it gives the following error :
但是，它给出了以下错误：
********** Error **********
ERROR: must be superuser to COPY to or from a file
SQL state: 42501
Hint: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
Even when I changed the permission ekodev@partner:~/Documents$ sudo chmod a+rwX /home/ekodev/ /home/ekodev/Documents/ /home/ekodev/Documents/test1.csv
即使我更改了权限 ekodev@partner:~/Documents$ sudo chmod a+rwX /home/ekodev/ /home/ekodev/Documents/ /home/ekodev/Documents/test1.csv
It still does not work for me.
它仍然对我不起作用。
Does anybody know what the problem is?
有谁知道问题是什么？
回答by frlan
Out of documentation:
出文档：
COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.
COPY 命名文件只允许数据库超级用户使用，因为它允许读取或写入服务器有权访问的任何文件。
That means, your database user needs to have the superuser flag. you can set the flag with
这意味着，您的数据库用户需要具有超级用户标志。你可以设置标志
ALTER ROLE <rolename> WITH SUPERUSER
As this can be quiet dangerous did you consider using \copy from psql instead to copy data from client side.
由于这可能很危险，您是否考虑过使用 psql 中的 \copy 来从客户端复制数据。
回答by Atihska
This solution worked for me using \copy. ALTER did not as that also required admin privileges.
这个解决方案适用于我使用\copy. ALTER 没有，因为这也需要管理员权限。
psql -h <host> -U <user> -d <dbname> -c "\copy <table_name> FROM '<path to csvfile/file.csv>' with (format csv,header true, delimiter ',');"
回答by David Smits
Alternatively you could use pgAdmin to import csv data. Works when the SuperUser role is not available like in for example AWS.
或者，您可以使用 pgAdmin 导入 csv 数据。当 SuperUser 角色不可用时工作，例如在 AWS 中。
回答by DrCabry
The role that is running the query needs to be SUPERUSERto COPY FROMfile.
Otherwise you can copy only from STDIN.
正在运行的查询需求中的作用是SUPERUSER对COPY FROM文件。否则，您只能从STDIN.
