I have two AWS RDS postgres nodes backing a parallel mode pgpool setup on EC2. After using pgbench to populate test tables, I get odd behavior from test queries. Any query that uses a function produces the error mentioned in the subject line, while other queries work as expected. Three examples showing success, expected failure, and unexpected failure:
Success -- Yields the expected record set:
psql -c "SELECT aid FROM pgbench_accounts" "host=localhost port=9999 user=pgpool password=pass dbname=bench_parallel" # Giant record set is returned here.
Since the backing nodes are on RDS, md5 authentication is required. Authentication appears to be working fine in the case of non-function queries, as can be seen by replacing the correct password above with an incorrect one.
Expected authentication failure:
psql -c "SELECT aid FROM pgbench_accounts" "host=localhost port=9999 user=pgpool password=notmypass dbname=bench_parallel" psql: FATAL: password authentication failed for user "pgpool"
Here's the part that has me baffled -- If I put a function like min() or count() into the query, I get authentication problems:
psql -c "SELECT count(aid) FROM pgbench_accounts" "host=localhost port=9999 user=pgpool password=pass dbname=bench_parallel" ERROR: password is required DETAIL: Non-superusers must provide a password in the connection string.
As can be seen from this last query, the password is supplied in the connection string (to the fronted, anyway) and it is the correct password as shown in the first query.
Why would my first query work fine with no auth problems, but the third one fail? Have I overlooked a setting somewhere?
Edit 2014-10-23: Adding more information.
I added superuser privileges to user pgpool on the (frontend) system database and no longer get
Non-superusers must provide a password in the connection string as the error. Now I get:
ERROR: could not establish connection DETAIL: fe_sendauth: no password supplied
Turning on debugging for pgpool and looking in the log, I see the query being rewritten as the following, which, in the call to dblink, does not contain the password specified in the original connection string:
2014-10-23 19:59:10 DEBUG: pid 1643: OneNode_do_command: Query: SELECT sum(pool_g$0) AS count FROM dblink('host=ip-10-1-2-17 dbname=bench_parallel port=9999 user=pgpool', 'SELECT pool_parallel("SELECT count(aid) FROM pgbench_accounts")',false) AS pool_t$0g (pool_g$0 bigint )