setReadOnly not working on PostgreSQL Connection

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

Answer

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

Advertisement

Answer