英文:
setReadOnly not working on PostgreSQL Connection
问题
我在使用JDBC连接PostgreSQL数据库时遇到了设置只读连接的问题。
我正在创建一个应用程序，从文件加载要执行的查询，并且我只想执行SELECT（只读）查询。在运行应用程序的用户上设置权限不是一个选项，所以通过Connection.setReadOnly(boolean)方法在代码级别设置权限是我能想到的唯一选项。
现在的问题是，我尝试了一下我的应用程序，对于插入和删除查询，它们都可以正常运行，所以我的解决方案不起作用。
有关如何解决这个问题的任何提示吗？
以下是我正在测试的简单代码片段：
public class Test{
public static void main(String[] args) {
connectAndExecute();
}
public static void connectAndExecute() {
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
return;
}
Connection connection;
String connString = "jdbc:postgresql://localhost:5432/testdb";
try {
connection = DriverManager.getConnection(connString, "user", "password");
} catch (SQLException e) {
e.printStackTrace();
return;
}
try {
connection.setReadOnly(true);
connection.createStatement().execute("delete from testtable");
// connection.createStatement().execute("insert into testtable(testfield1) values('test')");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
这是我正在使用的JDBC驱动程序：
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.10</version>
PostgreSQL版本：11.6
英文:
I have a problem setting a read-only connection with a PostgreSQL database using JDBC.
I'm creating an application which loads queries to execute from a file, and I want to execute only SELECT (read-only) queries. Setting permissions on users who will run the application is not an option, so setting permissions at code level through the Connection.setReadOnly(boolean) method was the only option I could think of.
Now the problem is that I tried my application for an insert and delete query and they run normally, so my solution does not work.
Any tips on how to solve the problem?
Here below the simple code snippet which I'm testing:
public class Test{
public static void main(String[] args) {
connectAndExecute();
}
public static void connectAndExecute() {
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
return;
}
Connection connection;
String connString = "jdbc:postgresql://localhost:5432/testdb";
try {
connection = DriverManager.getConnection(connString, "user", "password");
} catch (SQLException e) {
e.printStackTrace();
return;
}
try {
connection.setReadOnly(true);
connection.createStatement().execute("delete from testtable");
// connection.createStatement().execute("insert intotesttable(testfield1)values('test')");
} catch (SQLException e) {
e.printStackTrace();
}
}
and this is the JDBC driver I'm using
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.10</version>
PostgreSQL version: 11.6
答案1
得分: 2
问题似乎与驱动程序的错误有关。
这两种解决方法对我都有效：
- kayaman 提出的方法：
首先执行 SQL 命令
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY
- a_horse_with_no_name 提出的方法：
禁用自动提交使 setReadOnly 调用生效
Connection.setAutoCommit(false);
Connection.setReadOnly(true);
英文:
Problem seems related to a bug of the driver.
These two workaround both worked for me:
- proposed by kayaman:
executing first the sql command
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY
- proposed by a_horse_with_no_name:
disabling auto-commit makes the setReadOnly call effective
Connection.setAutoCommit(false);
Connection.setReadOnly(true);
答案2
得分: 0
清楚地说：setReadOnly 方法的规范并不旨在阻止对数据库进行更新。
正如 Java 的 API 所述：
https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#setReadOnly-boolean-
"将此连接置于只读模式，作为提示，以启用数据库优化"
因此，依赖该方法来保护与只读事务需求相关的任何内容并不是一个好主意 
英文:
Just to be clear : the specification for setReadOnly method does not aim to prevent from updating the database.
As the Java's API says :
https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#setReadOnly-boolean-
"Puts this connection in read-only mode as a hint to the driver to enable database optimizations"
So it is not a good idea to rely on this method in order to secure anything related to the need of having readonly transactions
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论