PostgresSQLfile permissions error using COPY

问题:

I am using python to dump csv data into a database using Psycopg2. I need to give Postgres permission to a specific filepath in order to use the COPY command (documentation: https://www.postgresql.org/docs/10/static/sql-copy.html). I need to give permission to a specific directory path route and file to avoid the following error:

COPY database.table_name FROM '/home/development_user/Documents/App/CSV/filename.csv' delimiter ',' csv header

ERROR:  could not open file "/home/development_user/Documents/App/CSV/filename.csv" for reading: Permission denied

To simplify things, want to add postgres to the development user's group. That way, postgres should have the group read permissions the development user can easily define on a path by path basis. I added the postgres user to the development_user group using the following command and validated that it was successful:

$ sudo usermod -a -G development_user postgres
$ groups postgres
postgres : postgres development_user

Here is the output of a permissions path trace using the namei -l [path] commmand

$ namei -l /home/development_user/Documents/App/CSV/filename.csv
drwxr-xr-x root        root        /
drwxr-xr-x root        root        home
drwxr-x--- development_user development_user development_user
drwxr-xr-x development_user development_user Documents
drwxr-xr-x development_user development_user App
drwxrwxr-x development_user development_user CSV
-rw-rw-r-- development_user development_user filename.csv

As you can see, anyone in the group development_user should now have read (r) and execute (x) permissions on all directories in the path, and also read and write permissions on the final file. If postgres tried to access the same file as an other user, postgres would be limited by the development_user directory in ability to access.

However, when I try to access the file I get a permissions error as noted above. When I open the development_user directory with other read and execute permissions such as the command below, I am able to read the the file is Postgres:

$ chmod o+rx /home/development

However, I do not want to grant other read and execute permissions for the development_user home directory, and I can't see why postgres user is not able to use the group permissions outlined above to access the same file since I added postgres to the development_user account.

Any ideas if my method to give postgres permissions to read a file by adding it to the user's group is a viable strategy? I do not want to use another solution such as mentioned here: (PostgreSQL - inconsistent COPY permissions errors) or here (Postgres ERROR: could not open file for reading: Permission denied) which advise opening up permissions by setting the file owner to be postgres:postgres. or opening up the directory permissions to widely such as allowing all users to read and execute on the development home directory. I also do not want to create another directory in the system directories and be forced to save files there as suggested here: (psql ERROR: could not open file "address.csv" for reading: No such file or directory).

回答1:

From the PostgreSQL Manual:

COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

So the PostgreSQL user doing the copying must be a database superuser.

You can do this with the ALTER ROLE command:

ALTER ROLE <rolename> WITH SUPERUSER

Also:

COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible by the PostgreSQL user (the user ID the server runs as) and the name must be specified from the viewpoint of the server.

...

Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client.

The default system user that PostgreSQL runs on is postgres. Ensure that that user has access to the files you want to copy. You can test this by using the command sudo -i -u postgres to become the postgres user and then trying to view the files.

回答2:

The way I solved this problem particular to use psychopg2 cursor class function copy_expert (Docs: http://initd.org/psycopg/docs/cursor.html). copy_expert allows you to use STDIN therefore bypassing the need to issue a superuser privilege for the postgres user.

From Postgres COPY Docs (https://www.postgresql.org/docs/current/static/sql-copy.html):

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

You can also leave the permissions set strictly for access to the development_user home folder and the App folder.

            sql = "COPY table_name FROM STDIN DELIMITER '|' CSV HEADER"
            self._cursor.copy_expert(sql, open(csv_file_name, "r"))

回答3:

Slight variation on @jonnyjandles answer, since that shows a mystery self._cursor -- a more typical invocation might be like:

copy_command = f"COPY table_name FROM STDIN CSV HEADER;"
with connection.cursor() as cursor:
    cursor.copy_expert(copy_command, open(some_file_path, "r"))