Search code examples
postgresqlproxywiresharkpacket-sniffersfunction-call

Recognize function calls as proxy between frontend and backend

Based on this question I found out I can't expect to find function calls as proxy using 'F' identifier.

I researched more and now I think postgreSQL uses extended query protocol to send parametric statements such as functions. (correct me if I'm wrong)

I know executing prepared-statements will use extended query protocol too (and I think there should be more statements which use this protocol)

So I think this shouldn't be the way to recognize a function call as a proxy. Is there any other way? Is it possible at all? Or am I completely lost and misunderstood everything?

by the way by recognizing function call I mean I need to recognize a function call and investigate passed parameters and function name as a third party in frontend-backend connection (between client and server)

Solution

  • PostgreSQL uses the extended query protocol for statements with parameters, but these parameters are not necessarily the same as function parameters.

    To use examples using the C API, if you send a function call like this:

    res = PQexec(conn, "SELECT myfun(42)");

    it will be sent in a packet with the 'Q' (Query) identifier.

    If you send it like this:

    const Oid types[1] = { INT4OID };
const char * const vals[1] = { "42" };

res = PQexecParams(conn, "SELECT myfun($1)", 1, types, vals, NULL, NULL, 0);

    the query will be sent in a packet with a 'P' (Parse) identifier and the parameter with be sent in the following 'B' (Bind) packet.

    But that has nothing to do with function calls, the same will happen for a query like this:

    SELECT val FROM mytab WHERE id = $1;

    You say your goal is to listen to the frontend-backend protocol and filter out all function calls and the parameters passed to them.

    That is a very difficult task; essentially it means that you have to parse the SQL statements sent to the server, and that means you have to duplicate at least part of PostgreSQL's parser. You'll have to remember some parsed statements and inject parameters from bind packets.

    In addition to that, two question come to my mind:

    1. Does it matter that this way you won't be able to catch function calls issued inside functions?

    2. How do you determine the passed parameter in cases like this:

      SELECT myfun((SELECT val FROM tab WHERE id = 42));

      or this:

      SELECT myfun(CAST(otherfun(42) || '0' AS integer));

    Maybe there's a better way to achieve what you want, like hacking the PostgreSQL server and extracting your information at the place where the function is actually called.