在Ubuntu上将CSV导入Postgres时，\copy权限被拒绝 [英] \copy permission denied while importing CSV to Postgres on Ubuntu

问题描述

我在Ubuntu 14.04上使用Postgres，我一直在尝试将一个csv文件导入Postgres中的一个表称为weather。我查看了

为什么

您必须更改一些权限，因为Postgres可以' t读取您的文件。 Postgres是一个不同的用户，因此它不能读取您的文件，如果你不给它的权利。 \copy解决方案只有在您的您而不是postgres是运行 psql 命令的用户的设置时才会工作。 p>

您可以随时创建文件的副本，将文件的权限分配给Postgres的用户Postgres可以执行，然后删除文件，或者您可以这样做：

您必须更改的内容取决于此命令的输出（以user1身份运行）：

  namei -l /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv 
  

（如果尚未安装此命令，则可能需要 sudo apt-get install util-linux 才能运行此命令） p>

此命令将列出文件及其所有父目录的当前权限，以便我们可以找到解决方案。

所有目录都有以x结尾的条目，如下所示：

  drwxr-xr-x用户组文件名
 drwxr-xr-x 
 drwxr-xr-x 
 -rw ------- 
  

那么以下两个解决方案中的任何一个都可以工作。

1. 如果您不想更改权限，并且您拥有sudoer权限，可以执行

     sudo chown / home / user1 / Dropbox / SQL / Codeschool / TrySQL / temp_data.csv postgresql 
     

   但是，这种方法将撤销对文件的访问，你可能不想要的东西。但您可以随时通过

   导入该文件，将文件退回给您。
   
   

     sudo chown / home / user1 / /Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv user1 
     




2. 如果您如果所有用户都读取了您的文件，那么您可以执行（作为user1并且不具有root权限）

     chmod a + r /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv 
     

   I推荐你做这个解决方案。它只会更改该文件的权限，以便计算机上的所有用户都可以读取它。然而，尽管默认情况下linux中的大多数目录可以被任何人打开，但如果不是所有用户都对您的目录具有执行权限，这将有可能不起作用。

   
   
   

   当然，一旦读完文件，您可以随时使用

     chmod ar / home / user1 /Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv 
     

   如果这两个解决方案

I'm using Postgres on Ubuntu 14.04 and I've been trying to import a csv file to a table in Postgres called 'weather'. I've looked at the question Postgres ERROR: could not open file for reading: Permission denied and tried using the \copy command instead of the regular copy, but I still get the same Permission Denied error.

I'm somewhat hesitant to modify ownership permissions for all files for that directory for all the users (as suggested in the first answer). Here's the copy statement:

\copy weather from '/home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv' HEADER DELIMITER ',' CSV

And here's a screenshot of the terminal:

Why is the \copy not working here?

解决方案

You definitely have to change some permissions because Postgres can't read your file. Postgres is a different user from you, so it can't read your files if you don't give it the right to. The \copy solution would work only if you have a setup where you, not postgres, are the user who runs the psql command.

You could always make a copy of the file, assign permissions for the file to user Postgres in a directory Postgres can execute, and delete the file afterwards, or you could do this:

What you have to change depends on the output of this command (run as user1):

namei -l /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv

(you may need to sudo apt-get install util-linux before running this command, if it isn't already installed)

This command will list the current permissions of the file and all its parent directories so we can find solutions.

Assuming all of the directories have entries ending with "x", like this:

drwxr-xr-x user group filename
drwxr-xr-x
drwxr-xr-x
-rw-------

then either of the two solutions below will work.

1. If you don't want to change permissions for all users and you have sudoer permissions, you can do

   sudo chown /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv postgresql
   

   However, this approach will revoke your access to the file, something you probably don't want. But you can always chown the file back to you after you're done importing it with

   sudo chown /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv user1
   

2. If you don't mind if all users read your file, then you can execute (as user1 and without root permissions)

   chmod a+r /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv
   

   I recommend that you do this solution. It will only change the permissions of that one file so that all the users on your computer can read it. However, although by default in linux most directories can be opened by anyone, there's a chance that this won't work if not all users have the execute permission on your directories.

   Of course, once you've read the file, you can always restrict the permissions again with

   chmod a-r /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv
   

   If neither of these solutions are good for you, please comment with the output of the first command above.

这篇关于在Ubuntu上将CSV导入Postgres时，\copy权限被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！