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

Solution 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.

Solution 2:

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"))

Solution 3:

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"))