can't load the emq_auth_pgsql when the db user is NOT "root"

[seth-yang]

OS: CentOS 6.8 x86_64 EMQTTD: 2.2

my emq_auth_pgsql.conf list below: ## Postgre Server: 5432, 127.0.0.1:5432, localhost:5432 auth.pgsql.server = 127.0.0.1:5432 auth.pgsql.pool = 8 auth.pgsql.username = mqtt auth.pgsql.password = mqtt auth.pgsql.database = mqtt auth.pgsql.encoding = utf8 auth.pgsql.ssl = false

and i try to load the plugin: $ emqttd_ctl plugins load emq_auth_pgsql

load plugin error: {emq_auth_pgsql, {bad_return, {{emq_auth_pgsql_app,start,[normal,[]]}, {'EXIT', {{badmatch, {error, {shutdown, {failed_to_start_child,emq_auth_pgsql, {shutdown, {failed_to_start_child,worker_sup, {shutdown, {failed_to_start_child, {worker,1}, {error,fatal,<<"28000">>, invalid_authorization_specification, <<"role "root" does not exist">>, [{file,<<"miscinit.c">>}, {line,<<"494">>}, {routine, <<"InitializeSessionUserId">>}]}}}}}}}}}, [{emq_auth_pgsql_app,start,2, [{file,"src/emq_auth_pgsql_app.erl"},{line,33}]}, {application_master,start_it_old,4, [{file,"application_master.erl"},{line,273}]}]}}}}}

when i check the crash.log, i found this message:

2017-07-19 15:20:59 =CRASH REPORT==== crasher: initial call: ecpool_worker:init/1 pid: <0.1794.0> registered_name: [] exception exit: {{error,fatal,<<"28000">>,invalid_authorization_specification,<<"role "root" does not exist">>,[{file,<<"miscinit.c">>},{line,<<"494">>},{routine,<<"InitializeSessionUserId">>}]},[{gen_server,init_it,6,[{file,"gen_server.erl"},{line,344}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,247}]}]} ancestors: [<0.1793.0>,<0.1791.0>,emq_auth_pgsql_sup,<0.1789.0>] messages: [{'EXIT',<0.1795.0>,normal}] links: [<0.1793.0>] dictionary: [] trap_exit: true status: running heap_size: 376 stack_size: 27 reductions: 181 neighbours: 2017-07-19 15:20:59 =SUPERVISOR REPORT==== Supervisor: {<0.1793.0>,ecpool_worker_sup} Context: start_error Reason: {error,fatal,<<"28000">>,invalid_authorization_specification,<<"role "root" does not exist">>,[{file,<<"miscinit.c">>},{line,<<"494">>},{routine,<<"InitializeSessionUserId">>}]} Offender: [{pid,undefined},{id,{worker,1}},{mfargs,{ecpool_worker,start_link,[emq_auth_pgsql,1,emq_auth_pgsql_cli,[{pool_size,8},{auto_reconnect,1},{host,"127.0.0.1"},{port,5432},{username,"root"},{password,[]},{database,"mqtt"},{encoding,utf8},{ssl,false},{ssl_opts,[]}]]}},{restart_type,transient},{shutdown,5000},{child_type,worker}]

2017-07-19 15:20:59 =SUPERVISOR REPORT==== Supervisor: {<0.1791.0>,ecpool_pool_sup} Context: start_error Reason: {shutdown,{failed_to_start_child,{worker,1},{error,fatal,<<"28000">>,invalid_authorization_specification,<<"role "root" does not exist">>,[{file,<<"miscinit.c">>},{line,<<"494">>},{routine,<<"InitializeSessionUserId">>}]}}} Offender: [{pid,undefined},{id,worker_sup},{mfargs,{ecpool_worker_sup,start_link,[emq_auth_pgsql,emq_auth_pgsql_cli,[{pool_size,8},{auto_reconnect,1},{host,"127.0.0.1"},{port,5432},{username,"root"},{password,[]},{database,"mqtt"},{encoding,utf8},{ssl,false},{ssl_opts,[]}]]}},{restart_type,transient},{shutdown,infinity},{child_type,supervisor}]

2017-07-19 15:20:59 =SUPERVISOR REPORT==== Supervisor: {local,emq_auth_pgsql_sup} Context: start_error Reason: {shutdown,{failed_to_start_child,worker_sup,{shutdown,{failed_to_start_child,{worker,1},{error,fatal,<<"28000">>,invalid_authorization_specification,<<"role "root" does not exist">>,[{file,<<"miscinit.c">>},{line,<<"494">>},{routine,<<"InitializeSessionUserId">>}]}}}}} Offender: [{pid,undefined},{id,emq_auth_pgsql},{mfargs,{ecpool,start_pool,[emq_auth_pgsql,emq_auth_pgsql_cli,[{pool_size,8},{auto_reconnect,1},{host,"127.0.0.1"},{port,5432},{username,"root"},{password,[]},{database,"mqtt"},{encoding,utf8},{ssl,false},{ssl_opts,[]}]]}},{restart_type,permanent},{shutdown,5000},{child_type,supervisor}]

2017-07-19 15:20:59 =CRASH REPORT==== crasher: initial call: application_master:init/4 pid: <0.1788.0> registered_name: [] exception exit: {{bad_return,{{emq_auth_pgsql_app,start,[normal,[]]},{'EXIT',{{badmatch,{error,{shutdown,{failed_to_start_child,emq_auth_pgsql,{shutdown,{failed_to_start_child,worker_sup,{shutdown,{failed_to_start_child,{worker,1},{error,fatal,<<"28000">>,invalid_authorization_specification,<<"role "root" does not exist">>,[{file,<<"miscinit.c">>},{line,<<"494">>},{routine,<<"InitializeSessionUserId">>}]}}}}}}}}},[{emq_auth_pgsql_app,start,2,[{file,"src/emq_auth_pgsql_app.erl"},{line,33}]},{application_master,start_it_old,4,[{file,"application_master.erl"},{line,273}]}]}}}},[{application_master,init,4,[{file,"application_master.erl"},{line,134}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,247}]}]} ancestors: [<0.1787.0>] messages: [{'EXIT',<0.1789.0>,normal}] links: [<0.1787.0>,<0.935.0>] dictionary: [] trap_exit: true status: running heap_size: 610 stack_size: 27 reductions: 126 neighbours:

i create the user named 'root' in the pg db, and reload the plug, it WORKS!!! createuser -sP root emqttd_ctl plugins load emq_auth_pgsql

Start apps: [emq_auth_pgsql] Plugin emq_auth_pgsql loaded successfully.

it does NOT use the conf entry 'auth.pgsql.username'?

[agenteDserrano]

I am using version 2.3.11 and I cant autenticate with database user emq.

Log here:

webadmin@bagre:~$ sudo emqttd_ctl plugins load emq_auth_pgsql load plugin error: {emq_auth_pgsql, {bad_return, {{emq_auth_pgsql_app,start,[normal,[]]}, {'EXIT', {{badmatch, {error, {shutdown, {failed_to_start_child,emq_auth_pgsql, {shutdown, {failed_to_start_child,worker_sup, {shutdown, {failed_to_start_child, {worker,1}, invalid_password}}}}}}}}, [{emq_auth_pgsql_app,start,2, [{file,"src/emq_auth_pgsql_app.erl"},{line,33}]}, {application_master,start_it_old,4, [{file,"application_master.erl"},{line,273}]}]}}}}}

[eisaev]

Same problem. The plugin always uses the name "root", regardless of the "auth.pgsql.username" setting. 100% reproducible. Why is the issue closed?

[eisaev]

After a simple check, it turned out that all settings except "auth.pgsql.password" are ignored.

[eisaev]

Reproduced on emqx installed from the package for Ubuntu 16.04. On emqx built manually, all parameters are used normally.

[HJianBo]

Hi, @eisaev Thanks a lot for your effort. We will try to reproduce it later

[JACINT1]

Hi, same problem here. Any news on this?

/opt/emqx/bin $ emqx_ctl plugins load emqx_auth_pgsql Load plugin emqx_auth_pgsql error: {emqx_auth_pgsql, {bad_return, {{emqx_auth_pgsql_app,start,[normal,[]]}, {'EXIT', {{badmatch, {error, {shutdown, {failed_to_start_child, emqx_auth_pgsql, {shutdown, {failed_to_start_child, worker_sup, {shutdown, {failed_to_start_child, {worker,1}, {badmatch, {error,econnrefused}}}}}}}}}}, [{emqx_auth_pgsql_app,start,2, [{file, "/emqx_rel/_build/emqx/lib/emqx_auth_pgsql/src/emqx_auth_pgsql_app.erl"}, {line,37}]}, {application_master,start_it_old,4, [{file,"application_master.erl"}, {line,277}]}]}}}}}.

Thank you.

[leqii-com]

同样的问题，怎么办？已经在postgresql中创建了root超级用户，也不行，不论怎么弄，就是不行

emqx_ctl plugins load emqx_auth_pgsql

提示如下： Load plugin emqx_auth_pgsql error: {emqx_auth_pgsql, {bad_return, {{emqx_auth_pgsql_app,start,[normal,[]]}, {'EXIT', {{badmatch, {error, {shutdown, {failed_to_start_child, emqx_auth_pgsql, {shutdown, {failed_to_start_child, worker_sup, {shutdown, {failed_to_start_child, {worker,1}, invalid_authorization_specification}}}}}}}}, [{emqx_auth_pgsql_app,start,2, [{file,"emqx_auth_pgsql_app.erl"}, {line,37}]}, {application_master,start_it_old,4, [{file,"application_master.erl"}, {line,277}]}]}}}}}.

[leqii-com]

@HJianBo @agenteDserrano @seth-yang @eisaev @all , Can you help me？

[eisaev]

@leqii-com I don't use pre-built packages and have switched to using a manually built emqx.

[seguidor777]

Any fix for this please?