Postgresql DB - Create Account JSON issue

[dpannem0804]

I am using below statement in create account json to create a account /role in postgresql db but I am getting below error . I don't see any much information in log, 

Could you please confirm that if we can use create statement in create account json. will it support ?

{

"createaccountqry" : ["create Role '${user.systemUserName}' WITH CREATEDB CREATEROLE LOGIN"]

}

Appreciate your support.

[sahajranajee]

Hello,

We don't see any error attached in your post. The statement should work. For the json key, use 'createAccountQry' instead of 'createaccountqry' .

[dpannem0804]

Thanks for your reply.

I tried with below different combinations still getting same error.

{

"createAccountQry" : ["create Role '${user.systemUserName}' WITH CREATEDB CREATEROLE LOGIN"]

}

or

{
"createAccountQry": " create Role \"test999\" WITH CREATEDB CREATEROLE LOGIN "
}

and also tried with below one.

{
"CreateAccountQry": " create Role test999 "
}

{"log":"2022-05-25 14:19:27,411 [quartzScheduler_Worker-7] ERROR provisoning.DBProvisioningService - Error while creating account -000015 stopping the Job\n","stream":"stdout","time":"2022-05-25T14:19:27.41199865Z"}

[avinashchhetri]

The error message is very generic and isn't helping, could you post more logs from this run to help us with more information ?

Regards,

Avinash Chhetri

[dpannem0804]

it seems there is no option to attached logs here , hence i am posting log below. 

"Service Name","Date Timestamp","Message"
"ecm-worker","2022-05-25T14:21:01.240+0000","{"log":"2022-05-25 14:21:00,911 [quartzScheduler_Worker-10] DEBUG provisoning.DBProvisioningService - looping for 000015\n","stream":"stdout","time":"2022-05-25T14:21:00.911346994Z"}"
"ecm-worker","2022-05-25T14:21:01.240+0000","{"log":"2022-05-25 14:21:01,210 [quartzScheduler_Worker-10] ERROR provisoning.DBProvisioningService - Error while creating account -000015 stopping the Job\n","stream":"stdout","time":"2022-05-25T14:21:01.210657371Z"}"
"ecm-worker","2022-05-25T14:21:00.240+0000","{"log":"2022-05-25 14:21:00,188 [quartzScheduler_Worker-10] DEBUG services.ArsTaskHelperService - new account , accountName = 000015 , taskType = 3\n","stream":"stdout","time":"2022-05-25T14:21:00.188533233Z"}"
"ecm-worker","2022-05-25T14:21:00.240+0000","{"log":"2022-05-25 14:21:00,190 [quartzScheduler_Worker-10] DEBUG services.ArsTaskService - Calling DB createAccount with Sec System - Compass and tasklist - [000015:[com.saviynt.ecm.task.ArsTasks : 175]]\n","stream":"stdout","time":"2022-05-25T14:21:00.190996427Z"}"
"ecm-worker","2022-05-25T14:19:28.240+0000","{"log":"2022-05-25 14:19:27,411 [quartzScheduler_Worker-7] ERROR provisoning.DBProvisioningService - Error while creating account -000015 stopping the Job\n","stream":"stdout","time":"2022-05-25T14:19:27.41199865Z"}"
"ecm-worker","2022-05-25T14:19:27.240+0000","{"log":"2022-05-25 14:19:27,094 [quartzScheduler_Worker-7] DEBUG provisoning.DBProvisioningService - looping for 000015\n","stream":"stdout","time":"2022-05-25T14:19:27.094278277Z"}"
"ecm-worker","2022-05-25T14:19:26.241+0000","{"log":"2022-05-25 14:19:26,078 [quartzScheduler_Worker-7] DEBUG services.ArsTaskHelperService - new account , accountName = 000015 , taskType = 3\n","stream":"stdout","time":"2022-05-25T14:19:26.079053833Z"}"
"ecm-worker","2022-05-25T14:19:26.241+0000","{"log":"2022-05-25 14:19:26,080 [quartzScheduler_Worker-7] DEBUG services.ArsTaskService - Calling DB createAccount with Sec System - Compass and tasklist - [000016:[com.saviynt.ecm.task.ArsTasks : 160], 000015:[com.saviynt.ecm.task.ArsTasks : 175]]\n","stream":"stdout","time":"2022-05-25T14:19:26.080693291Z"}"
"ecm-worker","2022-05-25T14:13:13.241+0000","{"log":"2022-05-25 14:13:12,568 [quartzScheduler_Worker-6] DEBUG provisoning.DBProvisioningService - looping for 000015\n","stream":"stdout","time":"2022-05-25T14:13:12.568380574Z"}"
"ecm-worker","2022-05-25T14:13:13.241+0000","{"log":"2022-05-25 14:13:12,885 [quartzScheduler_Worker-6] ERROR provisoning.DBProvisioningService - Error while creating account -000015 stopping the Job\n","stream":"stdout","time":"2022-05-25T14:13:12.885943407Z"}"
"ecm-worker","2022-05-25T14:13:12.240+0000","{"log":"2022-05-25 14:13:11,549 [quartzScheduler_Worker-6] DEBUG services.ArsTaskHelperService - new account , accountName = 000015 , taskType = 3\n","stream":"stdout","time":"2022-05-25T14:13:11.549939143Z"}"
"ecm-worker","2022-05-25T14:13:12.240+0000","{"log":"2022-05-25 14:13:11,552 [quartzScheduler_Worker-6] DEBUG services.ArsTaskService - Calling DB createAccount with Sec System - Compass and tasklist - [000016:[com.saviynt.ecm.task.ArsTasks : 160], 000015:[com.saviynt.ecm.task.ArsTasks : 175]]\n","stream":"stdout","time":"2022-05-25T14:13:11.55239038Z"}"
"ecm-worker","2022-05-25T14:10:05.240+0000","{"log":"2022-05-25 14:10:04,317 [quartzScheduler_Worker-4] ERROR provisoning.DBProvisioningService - Error while creating account -000015 stopping the Job\n","stream":"stdout","time":"2022-05-25T14:10:04.317570691Z"}"
"ecm-worker","2022-05-25T14:10:04.240+0000","{"log":"2022-05-25 14:10:03,945 [quartzScheduler_Worker-4] DEBUG provisoning.DBProvisioningService - looping for 000015\n","stream":"stdout","time":"2022-05-25T14:10:03.94548235Z"}"
"ecm-worker","2022-05-25T14:10:03.240+0000","{"log":"2022-05-25 14:10:02,818 [quartzScheduler_Worker-4] DEBUG services.ArsTaskHelperService - new account , accountName = 000015 , taskType = 3\n","stream":"stdout","time":"2022-05-25T14:10:02.81860295Z"}"
"ecm-worker","2022-05-25T14:10:03.240+0000","{"log":"2022-05-25 14:10:02,830 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService - Calling DB createAccount with Sec System - Compass and tasklist - [000016:[com.saviynt.ecm.task.ArsTasks : 160], 000015:[com.saviynt.ecm.task.ArsTasks : 175]]\n","stream":"stdout","time":"2022-05-25T14:10:02.830830644Z"}"
 

[avinashchhetri]

I was hopig to see some error codes but the logs you've posted is the same as the original one.

Are you able to run this query and make it work on the db directly, assuming you have the access ?

Regards,

Avinash Chhetri

[dpannem0804]

I dont find any error codes apart from that which shared .

Yes I am able to execute same query in db and able to create user /role.

for ex : 

CREATE ROLE akibit WITH
LOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION;

GRANT readwrite TO akibit;

[avinashchhetri]

Could you hardcode the logic in the connector and trigger it through a task ? Wanted to ensure if the credentials you've used in the connector has the appropriate access.

Regards,

Avinash Chhetri

[dpannem0804]

I tried with below hardcode value still getting same error.

{
"CreateAccountQry": " create Role \"test999\" WITH CREATEDB CREATEROLE LOGIN "
}

and also tried with below one.

{
"CreateAccountQry": " create Role test999 "
}

{"log":"2022-05-25 14:19:27,411 [quartzScheduler_Worker-7] ERROR provisoning.DBProvisioningService - Error while creating account -000015 stopping the Job\n","stream":"stdout","time":"2022-05-25T14:19:27.41199865Z"}

I dont see any issue with permissions because i am able to pull the accounts.

[rushikeshvartak]

• Try creating account with hardcoded value

• Create a user with no password: CREATE USER jonathan;

• Once above works try dynamic ; it might be possible variable not exposed. 
• If variable not exposed you can display required attribute in dynamic attribute on request form as hidden / readonly single select and use in JSON

[dpannem0804]

Thanks for your reply.

I tried with below hardcode value still getting same error.

{
"CreateAccountQry": " create Role \"test999\" WITH CREATEDB CREATEROLE LOGIN "
}

and also tried with below one.

{
"CreateAccountQry": " create Role test999 "
}

{"log":"2022-05-25 14:19:27,411 [quartzScheduler_Worker-7] ERROR provisoning.DBProvisioningService - Error while creating account -000015 stopping the Job\n","stream":"stdout","time":"2022-05-25T14:19:27.41199865Z"}

[rushikeshvartak]

try below hardcoded logic

{"CreateAccountQry":["CREATE ROLE roleforumrv"]}

[avinashchhetri]

Thanks rushikeshvarthak !!

dpannem0804, could you try creating with the syntax below ?

{"createAccountQry": ["Your Logic Here"]}

Regards,

Avinash Chhetri