Exceptions in emq_auth_pgsql plugin

[saschathiergart]

Hi,

while testing our EMQ deployment in production i discovered that emq_auth_pgsql plugin throws errors when connecting to the broker "from time to time". That is, sometimes the sql query cannot be executed correctly i guess.

Sometimes i get
2018-03-27 13:00:24.849 [error] <0.1909.0>@emqttd_protocol:process:234 Client([email protected]:63100): Username '0Tz9s' login failed for {error,error,<<"26000">>,invalid_sql_statement_name,<<"unnamed prepared statement does not exist">>,[{file,<<"postgres.c">>},{line,<<"1487">>},{routine,<<"exec_bind_message">>},{severity,<<"ERROR">>}]}

Other times the client can connect without any problem.

Sometimes i get:
2018-03-27 13:01:19.101 [error] <0.1959.0>@emqttd_protocol:process:287 Client([email protected]:63171): Cannot SUBSCRIBE [{<<"1.1/R/5FxrX">>,[{qos,0}]}] for ACL Deny

other times it works. Probably a problem regarding epgsql? Probably related to epgsql/epgsql#150

[bradleyd]

I am seeing this as well on 2.3.5.

[bradleyd]

it looks like it is using a pool with ecpool. I wonder if the issue is 10 connections being used many processes.

[phanimahesh]

My hunch is that the issue is with bad concurrency handling in ecpool. Looks like an ecpool bug that is apparent only under moderate to high load.

[phanimahesh]

The source of the issue is here: https://github.com/emqtt/ecpool/blob/master/src/ecpool_pool.erl#L62
Also see: https://github.com/uwiger/gproc/blob/master/src/gproc_pool.erl#L41-L53

ecpool picks a random client from the pool, instead of claiming it. This can result in the same db connection concurrently being used for more than one query, resulting in the error.

[andileeb]

Is there any fix?

[gilbertwong96]

Yes, it has been fixed in emqx3.0 rc.3

[daadu]

I am facing this issue with emqx v4.0.0, with postgres server in front of pgbouncer

[daadu]

pgbouncer is configured in Transaction Mode.

[daadu]

 query '"auth_query"' failed:  │
│                                          invalid_sql_statement_name,                                                                                                              │
│                                          <<"prepared statement \"auth_query\" does not exist">>,                                                                                  │
│                                          [{file,<<"prepare.c">>},                                                                                                                 │
│                                           {line,<<"512">>},                                                                                                                       │
│                                           {routine,                                                                                                                               │
│                                            <<"FetchPreparedStatement">>},                                                                                                         │
│                                           {severity,<<"ERROR">>}]}                                                     

[daadu]

It works fine without pgbouncer.

[daadu]

Posted fresh issue #197 for pgBouncer problem.

[mlmarius]

I am still experiencing this with emqx 4.2.2 inside of a docker swarm. Both emqx and postgresql are running on the same node.