权限被拒绝，尝试使用JDBC读取Postgres数据库的csv文件 [英] permission denied trying to read a csv file using JDBC for postgres database

问题描述

我正在尝试制作一个程序来计算在postgres中执行查询所花费的时间.为此，我正在使用JDBC.我正在使用

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 

声明.当我尝试n执行命令

statement. When I try n execute the command

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

使用cmd提示符执行.但是当我尝试使用Java和JDBC执行相同的命令时，它给出了错误消息

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

如何更改文件的权限，以便可以帮助我的jre使用该文件.

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

推荐答案

您是否在psql中以 superuser 的身份执行了该语句，并通过JDBC以另一个用户的身份执行了该语句? 手册告诉我们:

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

COPY命名文件只允许数据库超级用户使用，因为它 允许读取或写入服务器有权访问的任何文件 访问.

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.

您可以通过将语句包装在具有 SECURITY DEFINER 由超级用户拥有.请注意安全风险.您可能还想撤消公开的所有权利，并且仅<给所选用户的href ="http://www.postgresql.org/docs/current/interactive/sql-grant.html" rel ="nofollow"> GRANT .可能看起来像这样:

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;

---

此外，请注意转义字符串的正确语法是:

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

请注意E'...'.
从9.1版开始，默认情况下设置standard_conforming_strings处于启用状态.

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

这篇关于权限被拒绝，尝试使用JDBC读取Postgres数据库的csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！