permission denied trying to read a csv file using JDBC for postgres database

Question

I am trying to make a program which calculates time elapsed for executing a query in postgres.I am using JDBC for this purpose. I am bulk loading a file data.csv using the

copy

statement. When I try n execute the command

copy data_1 from 'C:\\Users\\Abhishek\\Desktop\\data1.csv' using delimiters ','"

using the cmd prompt,It executes. But when I try n execute the same command using java and JDBC.. it gives the error that

org.postgresql.util.PSQLException: ERROR: could not open file for reading: Permission denied

How can I change the permission on the file so that I can help my jre to get to use that file.

Erwin Brandstetter · Accepted Answer · 2012-01-15T06:35:31.587

Did you execute the statement as superuser in psql and as another user via JDBC?
The manual tells us:

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.

You can circumvent this restriction by wrapping the statement in a function with SECURITY DEFINER owned by a superuser. Be aware of the security risks. You may also want to REVOKE all rights from public and only GRANT to selected users. Could look like this:

CREATE OR REPLACE FUNCTION foo()
  RETURNS void AS
$BODY$
    COPY data_1
    FROM E'C:\\Users\\Abhishek\\Desktop\\data1.csv'
    USING delimiters ',';
$BODY$
  LANGUAGE sql VOLATILE SECURITY DEFINER
  SET search_path = public, pg_temp;  -- or whatever schema the table is in

REVOKE ALL ON FUNCTION foo() FROM public;
GRANT SELECT ON FUNCTION foo() TO my_user;

Also, be aware that the proper syntax for escape strings is:

E'C:\\Users\\Abhishek\\Desktop\\data1.csv'

Note the E'...'.
As of version 9.1, the setting standard_conforming_strings is on by default, enforcing this.

I was originally doing it as a superuser only.. But even that did not help. Finally to complete the assignment.. I did it on my linux platform. Seems it was simple permission issue. I got it done there by making permission of the file global using chmod. — Abhishek, Jan 16 '12 at 08:01
@abhiitd.cs: Only give file permissions to the postgres system user. That's all it takes. Doesn't have to be world-readable. — Erwin Brandstetter, Jan 16 '12 at 08:03

score 0 · Answer 2 · answered Nov 30 '22 at 14:10

For one-time, bulk loads from a file, it can be easier to toggle permissions on the file, rather than mess with the permissions of the Windows and PostgreSQL users.

Right clicking on the folder containing the file(s), then under "security", give "everyone" permission to read the file. When done, remove the "everyone" permissions. This can also be followed to make a folder the "write" destination when dumping data from PostgreSQL on Windows.

A step-by-step guide is given in another answer.

permission denied trying to read a csv file using JDBC for postgres database

2 Answers2

Linked