SET ROLE via parameterized query

Based on a simple test case I just wrote:

@Test
public void test() throws SQLException {
    PreparedStatement ps = conn.prepareStatement("SET ROLE ?");
    ps.setString(1, "someuser");
    ps.executeUpdate();
}

I think the error you refer to is probably:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 10
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2245)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1974)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:254)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:565)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:366)
    ....

The reason for this is that PostgreSQL's protocol can only bind placement parameters for "plannable" statements, which are SELECT, INSERT, UPDATE, DELETE and a few others. For these statement types it only supports placement parameters for literal values, not for identifiers or other syntax elements.

For other statement types, literals must be substituted in by the client. Some drivers support client-side emulation of prepare for these statements, so they seem to work transparently, but PgJDBC does not currently support this.

PgJDBC actually supports client-side parameter binding if you're using the legacy v2 protocol, but it doesn't expose this functionality for connections on the v3 protocol. Even if you're not using server-side prepared statements it'll still use server-side parameter binding. Forcing the v2 protocol will work, but is a poor workaround with lots of other consequences elsewhere - plus at some point PostgreSQL will drop support for the v2 protocol entirely.

I think this is an issue with the driver and/or PostgreSQL's wire protocol. The user shouldn't have to care about whether the server supports parameter binding for some statements and not others, the driver should be taking care of that - or better, the server should just support it for all statement types.

Unfortunately, PgJDBC also doesn't expose its internal implementations of safe identifier and literal escaping for client application use. If standard_conforming_strings is on, though, the rule is very simple:

• Replace every ' with ''; and
• Wrap the whole thing in single quotes

The same applies to identifiers, with double quotes.

You could create a function that executes SET ROLE with dynamic SQL, using format to safely insert the role identifier (%I inserts an identifier, placing double quotes around it if necessary, and escaping double quotes in the string by doubling them up if necessary). Something along the lines of

CREATE FUNCTION setrole(role text) RETURNS void AS $$ BEGIN EXECUTE format('SET ROLE %I', role); END $$ LANGUAGE plpgsql;

You can then execute that function with something like SELECT setrole(?). That should work fine with parameters.

Here's an example of it in use from psql:

> show role;
   role
-----------
 testuser1
(1 row)

> SELECT setrole('testuser2');
 setrole
---------

(1 row)

> show role;
   role
-----------
 testuser2
(1 row)