CODEWITHSUNDEEP
postgresqljdbc
Abhishek
Abhishek

Reputation: 2673

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

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.

Upvotes: 3

Views: 4297

Answers (2)

Kevin
Kevin

Reputation: 2736

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.

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658242

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.

Upvotes: 1

Related Questions