connection using java jdbc driver - [issue moved to mailing list]

[arunnairmr]

i am try to insert data into citusDB using JDBC connection.This my java code that use JDBC connection. I give full permission for the user user1 in my database. But i am getting an error as 'permission denied for relation'. Can you kindly check this and suggest a solution for this.

JDBCStatementInsertExample .java

import java.sql.DriverManager; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;

public class JDBCStatementInsertExample {

private static final String DB_DRIVER = "org.postgresql.Driver";
private static final String DB_CONNECTION = "jdbc:postgresql://192.168.1.108:5432/postgres?protocolVersion=2";
private static final String DB_USER = "user1";
private static final String DB_PASSWORD = "myPassword";

public static void main(String[] argv) {

    try {

        insertRecordIntoDbUserTable();
        selectRecordIntoDbUserTable();
    } catch (SQLException e) {

        System.out.println(e.getMessage());

    }

}

private static void insertRecordIntoDbUserTable() throws SQLException {

    Connection dbConnection = null;
    Statement statement = null;

    String insertTableSQL = "INSERT INTO customer " + "VALUES"
            + "(2,'mkyong','system')";

    try {
        dbConnection = getDBConnection();
        statement = dbConnection.createStatement();

        System.out.println(insertTableSQL);

        statement
                .executeQuery("SELECT sync_table_metadata_to_citus('customer')");
        System.out.println("sync_table_metadata_to_citus");
        statement
                .executeUpdate("SET pg_shard.use_citusdb_select_logic TO true");
        System.out.println("SET pg_shard.use_citusdb_select_logic");
        // execute insert SQL stetement
        statement.executeUpdate(insertTableSQL);

        System.out.println("Record is inserted into DBUSER table!");

    } catch (SQLException e) {

        System.out.println(e.getMessage());
        e.printStackTrace();

    } finally {

        if (statement != null) {
            statement.close();
        }

        if (dbConnection != null) {
            dbConnection.close();
        }

    }

}

private static void selectRecordIntoDbUserTable() throws SQLException {

    Connection dbConnection = null;
    Statement statement = null;

    String selectTableSQL = "SELECT * from customer";

    try {
        dbConnection = getDBConnection();
        statement = dbConnection.createStatement();

        System.out.println(selectTableSQL);

        statement
                .executeQuery("SELECT sync_table_metadata_to_citus('customer')");
        System.out.println("sync_table_metadata_to_citus");

        statement
                .executeUpdate("SET pg_shard.use_citusdb_select_logic TO true");
        System.out.println("SET pg_shard.use_citusdb_select_logic");
        // execute insert SQL stetement
        ResultSet rs = statement.executeQuery(selectTableSQL);

        System.out.println("Record is selected!");
        while (rs.next()) {

            System.out.println("-->" + rs.getString(1));
        }
    } catch (SQLException e) {

        System.out.println(e.getMessage());
        e.printStackTrace();

    } finally {

        if (statement != null) {
            statement.close();
        }

        if (dbConnection != null) {
            dbConnection.close();
        }

    }

}

private static Connection getDBConnection() {

    Connection dbConnection = null;

    try {

        Class.forName(DB_DRIVER);

    } catch (ClassNotFoundException e) {

        System.out.println(e.getMessage());

    }

    try {

        dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
                DB_PASSWORD);
        return dbConnection;

    } catch (SQLException e) {

        System.out.println(e.getMessage());

    }

    return dbConnection;

}

}

Error:

INSERT INTO customer VALUES(2,'mkyong','system') ERROR: permission denied for relation pg_dist_shard_placement org.postgresql.util.PSQLException: ERROR: permission denied for relation pg_dist_shard_placement at org.postgresql.core.v2.QueryExecutorImpl.receiveErrorMessage(QueryExecutorImpl.java:562) at org.postgresql.core.v2.QueryExecutorImpl.processResults(QueryExecutorImpl.java:485) at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:365) at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:259) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at JDBCStatementInsertExample.insertRecordIntoDbUserTable(JDBCStatementInsertExample.java:43) at JDBCStatementInsertExample.main(JDBCStatementInsertExample.java:18) SELECT * from customer ERROR: permission denied for relation pg_dist_shard_placement org.postgresql.util.PSQLException: ERROR: permission denied for relation pg_dist_shard_placement at org.postgresql.core.v2.QueryExecutorImpl.receiveErrorMessage(QueryExecutorImpl.java:562) at org.postgresql.core.v2.QueryExecutorImpl.processResults(QueryExecutorImpl.java:485) at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:365) at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:259) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at JDBCStatementInsertExample.selectRecordIntoDbUserTable(JDBCStatementInsertExample.java:86) at JDBCStatementInsertExample.main(JDBCStatementInsertExample.java:19)