In the very helpful response I received to a previous question, I am trying to write some JDBC code that first sets the role to a specific user before executing subsequent queries. On the safe side I would like to prevent SQL injection attacks by parameterizing the SET ROLE statement. My approach in Groovy (which uses JDBC) was:

def sql = Sql.newInstance('jdbc:postgresql:mydb', 'mydbweb', 'mydbwebpass', 'org.postgresql.Driver')
sql.execute 'SET ROLE ?', user

but this generates a syntax error. The documentation says SET ROLE can take a string literal, but I'm just not clear how I can validly pass it one. Any suggestions?


Based on a simple test case I just wrote:

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

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.

| improve this answer | |
  • Thanks, that's a very thorough answer. I think the restriction for plannable statements is fair enough since that's what prepared statements are for, but it would be useful to have a standard injection-safe String type for these situations. – beldaz Oct 5 '14 at 20:04
  • @beldaz Yeah, I think we need to expose PGConnection.escapeLiteral and PGConnection.escapeIdentifier methods. Unfortunately. – Craig Ringer Oct 6 '14 at 1:56

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;
(1 row)

> SELECT setrole('testuser2');

(1 row)

> show role;
(1 row)
| improve this answer | |

As far as I was able to test with , SET ROLE role_name can also be achieved using a set_config function, e.g. SELECT set_config('role', 'role_name', TRUE), and this way parameters can be used: SELECT set_config($1, $2, TRUE).

There is also a note in Postgres documentation that set_config provides the same functionality as SET (but even if ROLE is not mentioned there, it still worked for me.)

PostgreSQL version: PostgreSQL 12.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.