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?