windows 尝试从 PGAdmin 导入 CSV 文件时权限被拒绝

Permission denied when trying to import a CSV file from PGAdmin

提问by Tool

I'm using PGAdmin 1.14.3.

我正在使用 PGAdmin 1.14.3。

When I try to execute an import command:

当我尝试执行导入命令时：

COPY grad(country_code, postal_code, place_name, admin_name1, admin_code1, admin_name2, admin_code2, admin_name3, admin_code3, latitude, longitude, accuracy)
FROM 'C:\Users\denis\Desktop\BP2Project\USA\US.txt';

I get a

我得到一个

ERROR: could not open file "C:\Users\denis\Desktop\BP2Project\USA\US.txt" for reading: Permission denied SQL state: 42501

错误：无法打开文件“C:\Users\denis\Desktop\BP2Project\USA\US.txt”进行阅读：权限被拒绝 SQL 状态：42501

I did look up other similarquestions and none of them solved my issue.

我确实查找了其他类似的问题，但没有一个解决了我的问题。

I logged in as user "postgres" who is the superuser. I don't see why I'm missing permissions. I'm on Windows 7.

我以超级用户“postgres”用户身份登录。我不明白为什么我缺少权限。我在 Windows 7 上。

采纳答案by Houari

The user Postgresmust have read access on the file from which you are about to copy.

用户Postgres必须对您要从中复制的文件具有读取权限。

Look at this articleto see how to modify files' security access on Windows.

查看本文以了解如何在 Windows 上修改文件的安全访问权限。

回答by Kevin

The permissions articlementioned in the answerby Houariand Flimzyis a good reference material, but a direct answer (the quick fix I used) is:

该权限这篇文章中所提到的答案被胡阿里·和Flimzy是一个很好的参考材料，而是直接的答案（快速修复我使用）是：

• Right click the folder containing the data file(s) that permission was denied to and then click Properties.
• In the Folder's Properties window, select the Securitytab.
• Click the Editbutton.
• In the "Permissions for the folder" window that opened, click the Add...button.
• Type Everyoneinto the "Enter the object names to select" text area box.
• Click OKand the window will close.
• Verify that the default Read & Executepermissions were set to Allowvia the check checkbox in the previous window.
• Click OKand the window will close.
• Click the Applybutton in the Folder Properties window.

• 右键单击包含权限被拒绝的数据文件的文件夹，然后单击“属性”。
• 在文件夹的属性窗口中，选择安全选项卡。
• 单击编辑按钮。
• 在打开的“文件夹权限”窗口中，单击添加...按钮。
• 键入Everyone到“输入对象名称来选择”文本区域框。
• 单击确定，窗口将关闭。
• 通过前一个窗口中的复选框验证默认读取和执行权限是否设置为允许。
• 单击确定，窗口将关闭。
• 单击文件夹属性窗口中的应用按钮。

Now you can run the SQL COPY statement that needs to access those files.

现在您可以运行需要访问这些文件的 SQL COPY 语句。

• Once done, return to the Folder's Properties window.
• Click the Editbutton.
• Select the Everyoneentry in the "Group or user names:" field.
• Click the Removebutton.
• Click OKon the remaining open windows.

• 完成后，返回文件夹的属性窗口。
• 单击编辑按钮。
• Everyone在“组或用户名：”字段中选择条目。
• 单击删除按钮。
• 在其余打开的窗口上单击“确定”。

The permissions have now been returned to what they were.

权限现在已恢复到原来的状态。

回答by folorunso joseph

Ok, this is how got COPY command working,to export a table to CSV, step by step. Pls note that I am using pgAdmin 111.

好的，这就是如何让 COPY 命令工作，一步一步地将表导出到 CSV。请注意，我正在使用 pgAdmin 111。

1. Create the target folder you want to export a table to. E.g C:\myExports
2. Set a read/write permission on this folder following the steps below :

1. 创建要将表导出到的目标文件夹。例如 C:\myExports
2. 按照以下步骤设置对此文件夹的读/写权限：

Right click the folder containing the data file(s) that permission was denied >to and then click Properties.

In the Folder's Properties window, select the Security tab. Click the Edit button.

In the "Permissions for the folder" window that opened, click the Add... button. Type Everyone into the "Enter the object names to select" text area box.

Click OK and the window will close. Verify that the default Read & Execute permissions were set to Allow via the >check checkbox in the previous window. Click OK and the window will close.

Click the Apply button in the Folder Properties window.

右键单击包含权限被拒绝的数据文件的文件夹，然后单击“属性”。

在文件夹的属性窗口中，选择安全选项卡。单击编辑按钮。

在打开的“文件夹权限”窗口中，单击添加...按钮。在“输入要选择的对象名称”文本区域框中键入“所有人”。

单击确定，窗口将关闭。通过前一个窗口中的 >check 复选框验证默认的读取和执行权限是否设置为允许。单击确定，窗口将关闭。

单击文件夹属性窗口中的应用按钮。

1. This is the tricky part, inside myExports folder create a blank CSV file with your desired name.E.g employee.csv

2. Then run the Copy command like this :

   copy employee to 'C:\myExports\employee.csv' delimiter ',' csv;

3. 这是棘手的部分，在 myExports 文件夹中用您想要的名称创建一个空白的 CSV 文件。例如，employee.csv

4. 然后像这样运行复制命令：

   将员工复制到 'C:\myExports\employee.csv' 分隔符 ',' csv;

employee is the table name in this example..

在这个例子中，employee 是表名。

Hope this helps.

希望这可以帮助。

回答by Jonas B

If you don't want to give permissions to Everyone, you can add permissions to the account that started the service. In the Control Panel - Administrative Tools - Services, copy the account name in the 'Log On' tab. (On my system the account is called 'Network Service'.) Then share the folder with the CSV-file with this user as shown in the answer above.

如果您不想向所有人授予权限，则可以向启动该服务的帐户添加权限。在控制面板 - 管理工具 - 服务中，复制“登录”选项卡中的帐户名称。（在我的系统上，该帐户称为“网络服务”。）然后与此用户共享带有 CSV 文件的文件夹，如上面的答案所示。

回答by Diwas Poudel

To solve this problem you must give permission to the CSV file because that CSV file present in a COPY command are read directly by the server, but not client application. So to make this file accessible to a server we must give full read-write permission so that Postgresql user can read and write on that file.

要解决此问题，您必须授予 CSV 文件权限，因为 COPY 命令中存在的 CSV 文件由服务器直接读取，而不是客户端应用程序读取。因此，要使服务器可以访问此文件，我们必须授予完全读写权限，以便 Postgresql 用户可以读取和写入该文件。

Reference: articleshowing step by step procedure.

参考：显示分步过程的文章。

回答by Terry

for me and I've just spent some long hours on this. I have a central db residing on a HP box running 14.04 postgresql-9.5 pgAdmin3 postgis-2.2, shares are made through a tweeked Samba share. My clients are using a mixture of windows 10.1, 7, 8.1 and I have one ubuntu 14.04 desktop.

对我来说，我刚刚在这上面花了很长时间。我有一个中央数据库驻留在运行 14.04 postgresql-9.5 pgAdmin3 postgis-2.2 的 HP 机器上，共享是通过 tweeked Samba 共享进行的。我的客户混合使用 Windows 10.1、7、8.1，我有一个 ubuntu 14.04 桌面。

I'm working with large tables updating records and normalising data and have built the routines around SQL copy statements from CSV files which were made from the core COPY public.table_1 TO (the share folder I'd set up in Samba https://www.youtube.com/watch?v=ndAYZ0DJ-U4) '/srv/samba/share/[filename].csv'

我正在使用大型表来更新记录和规范化数据，并围绕来自 CSV 文件的 SQL 复制语句构建了例程，这些 CSV 文件是从核心 COPY public.table_1 TO（我在 Samba 中设置的共享文件夹https:// www.youtube.com/watch?v=ndAYZ0DJ-U4) '/srv/samba/share/[filename].csv'

I can then update the database once the tables have been amended with COPY table_1 from '/srv/samba/share/test.csv' USING DELIMITERS ',' WITH NULL AS '' CSV HEADER; from any of my clients.

然后我可以在通过 '/srv/samba/share/test.csv' USING DELIMITERS ',' WITH NULL AS '' CSV HEADER; 中的 COPY table_1 修改表后更新数据库；来自我的任何客户。

The key as far as I have been able to determine is that the clients doing the updating must be superusers, also everything must tie up in terms of users as there are 4 servers working together here Postgresql, Samba, UNIX and WINS All of my users are registered on each of the servers with the same username and password homogeneity is the main factor.

据我所知，关键是执行更新的客户端必须是超级用户，而且一切都必须以用户为单位，因为这里有 4 个服务器一起工作 Postgresql、Samba、UNIX 和 WINS 我所有的用户在每个服务器上都注册了相同的用户名和密码 同质性是主要因素。

I had tried for a long time moving things about and trying various naming conventions but in the end it was http://www.postgresql.org/message-id/[e-mail] sorted me out it was like a big switch clicking in. chown 777 on your shares and group management was an important learning curve but., the hours I've spent on this will reap rewards down the line... Loving Ubuntu loving life and loving the spirit of open source but that just might be sleep deprivation kicking in... IT WORKS

我已经尝试了很长时间，并尝试了各种命名约定，但最终是http://www.postgresql.org/message-id/[e-mail]对我进行了排序这就像点击一个大开关。在你的共享和组管理上 chown 777 是一个重要的学习曲线，但是，我在这方面花费的时间将获得回报......热爱 Ubuntu，热爱生活，热爱精神开源的，但这可能是睡眠不足开始的......它有效

回答by Alex

Responses to this problem on different threads go something like this 1. "Tell me exactly what command you used" 2. "Make sure you have right permissions" 3. "Just use /copy"

在不同线程上对此问题的响应如下 1.“确切地告诉我您使用了什么命令” 2.“确保您拥有正确的权限” 3.“只需使用 /copy”

I just tried giving permissions to Everyone on the cvs file I am trying to copy from, and it is still giving me the permission denied error. I think this functionality is broken and has been broken for multiple consecutive releases over multiple consecutive versions of Windows.

我只是尝试在我试图从中复制的 cvs 文件上向所有人授予权限，但它仍然给我权限被拒绝错误。我认为此功能已损坏，并且在多个连续版本的 Windows 上的多个连续版本中已损坏。