Problem Managing Central Users with the API

lgorman · September 7, 2021, 5:07pm

Hi all!

I am working with ODK central, and I am hoping to manage users with the API. Here are the central version numbers:

versions:
e16b79530756bbd825f68275b3b6e53951409474
 70ec99f02885a06e37709db6319bfdf96fac84eb client (v1.2.2)
 59556ecea91f0a25678cd6da0084adb7e66ca099 server (v1.2.1)

When trying to create a user through the API, I see that the documentation does not specify that you need to include an Authorization header and bearer token. However, it appears authentication is necessary to create users, as when I make a request without the authorization and bearer token, I get this response:

{
    "message": "Could not authenticate with the provided credentials.",
    "code": 401.2
}

When I include the Authorization header and bearer token I get the response:

{
    "message": "Completely unhandled exception: Invalid login: 535 5.7.139 Authentication unsuccessful, user password has expired. [LNXP265CA0071.GBRP265.PROD.OUTLOOK.COM]",
    "details": {
        "stack": [
            "Error: Invalid login: 535 5.7.139 Authentication unsuccessful, user password has expired. [LNXP265CA0071.GBRP265.PROD.OUTLOOK.COM]",
            "at SMTPConnection._formatError (/usr/odk/node_modules/nodemailer/lib/smtp-connection/index.js:774:19)",
            "at SMTPConnection._actionAUTHComplete (/usr/odk/node_modules/nodemailer/lib/smtp-connection/index.js:1513:34)",
            "at SMTPConnection.<anonymous> (/usr/odk/node_modules/nodemailer/lib/smtp-connection/index.js:1471:18)",
            "at SMTPConnection._processResponse (/usr/odk/node_modules/nodemailer/lib/smtp-connection/index.js:932:20)",
            "at SMTPConnection._onData (/usr/odk/node_modules/nodemailer/lib/smtp-connection/index.js:739:14)",
            "at TLSSocket.SMTPConnection._onSocketData (/usr/odk/node_modules/nodemailer/lib/smtp-connection/index.js:189:44)",
            "at TLSSocket.emit (events.js:203:13)",
            "at TLSSocket.EventEmitter.emit (domain.js:494:23)",
            "at addChunk (_stream_readable.js:294:12)",
            "at readableAddChunk (_stream_readable.js:275:11)",
            "at TLSSocket.Readable.push (_stream_readable.js:210:10)",
            "at TLSWrap.onStreamRead (internal/stream_base_commons.js:166:17)"
        ]
    }
}

Since trying to create users via the API, I am now having troubles just managing old users with the GUI. For example, when I try to reset the password of a user I get the error:

Something went wrong: error code 500.

When I look at the Server audit logs, these are what I see, the crossed out names are colour coded so that they represent the same user:

Any help would be greatly appreciated! Thanks already to everyone who has helped me with ODK queries so far

lgorman · September 10, 2021, 8:12am

Further update on this. When I try to create any user now, using the central GUI, I get the same 500 error "something went wrong". Not sure how to unpick this, so if anyone has any ideas it would be super appreciated!!!

Florian_May · September 10, 2021, 9:48am

The server logs might help. What do they say?

lgorman · September 10, 2021, 1:04pm

Thank you so much for the response. The screenshot I attached in my message is the initial server audit logs.

Which logs should I look for (sorry if that is a stupid question!)? I looked at the docker-compose logs, and I can see that there are some issues with the postgres db.

Here are a few of the issues (--tail=100):

postgres_1            | STATEMENT:
postgres_1            |         with def as (insert into form_defs ("formId", xml, hash, sha, sha256, version, "keyId", "xlsBlobId", "draftToken", "createdAt", "publishedAt")
postgres_1            |           values (nextval(pg_get_serial_sequence('forms', 'id')), $1, $2, $3, $4, $5, $6, $7, $8, now(), $9)
postgres_1            |           returning *),
postgres_1            |         form as (insert into forms (id, name, "xmlFormId", state, "projectId", "draftDefId", "acteeId", "createdAt")
postgres_1            |           select def."formId", $10, $11, $12, $13, def.id, $14, def."createdAt" from def
postgres_1            |           returning forms.*)
postgres_1            |         select id from form;
postgres_1            | ERROR:  duplicate key value violates unique constraint "forms_projectid_xmlformid_deletedat_unique"
postgres_1            | DETAIL:  Key ("projectId", "xmlFormId")=(17, p1f1) already exists.
postgres_1            | STATEMENT:
postgres_1            |         with def as (insert into form_defs ("formId", xml, hash, sha, sha256, version, "keyId", "xlsBlobId", "draftToken", "createdAt", "publishedAt")
postgres_1            |           values (nextval(pg_get_serial_sequence('forms', 'id')), $1, $2, $3, $4, $5, $6, $7, $8, now(), $9)
postgres_1            |           returning *),
postgres_1            |         form as (insert into forms (id, name, "xmlFormId", state, "projectId", "draftDefId", "acteeId", "createdAt")
postgres_1            |           select def."formId", $10, $11, $12, $13, def.id, $14, def."createdAt" from def
postgres_1            |           returning forms.*)
postgres_1            |         select id from form;
postgres_1            | ERROR:  duplicate key value violates unique constraint "forms_projectid_xmlformid_deletedat_unique"
postgres_1            | DETAIL:  Key ("projectId", "xmlFormId")=(16, p2f1) already exists.
postgres_1            | STATEMENT:
postgres_1            |         with def as (insert into form_defs ("formId", xml, hash, sha, sha256, version, "keyId", "xlsBlobId", "draftToken", "createdAt", "publishedAt")
postgres_1            |           values (nextval(pg_get_serial_sequence('forms', 'id')), $1, $2, $3, $4, $5, $6, $7, $8, now(), $9)
postgres_1            |           returning *),
postgres_1            |         form as (insert into forms (id, name, "xmlFormId", state, "projectId", "draftDefId", "acteeId", "createdAt")
postgres_1            |           select def."formId", $10, $11, $12, $13, def.id, $14, def."createdAt" from def
postgres_1            |           returning forms.*)
postgres_1            |         select id from form;
postgres_1            | ERROR:  duplicate key value violates unique constraint "forms_projectid_xmlformid_deletedat_unique"
postgres_1            | DETAIL:  Key ("projectId", "xmlFormId")=(16, p2f1) already exists.
postgres_1            | STATEMENT:
postgres_1            |         with def as (insert into form_defs ("formId", xml, hash, sha, sha256, version, "keyId", "xlsBlobId", "draftToken", "createdAt", "publishedAt")
postgres_1            |           values (nextval(pg_get_serial_sequence('forms', 'id')), $1, $2, $3, $4, $5, $6, $7, $8, now(), $9)
postgres_1            |           returning *),
postgres_1            |         form as (insert into forms (id, name, "xmlFormId", state, "projectId", "draftDefId", "acteeId", "createdAt")
postgres_1            |           select def."formId", $10, $11, $12, $13, def.id, $14, def."createdAt" from def
postgres_1            |           returning forms.*)
postgres_1            |         select id from form;
postgres_1            | ERROR:  duplicate key value violates unique constraint "forms_projectid_xmlformid_deletedat_unique"
postgres_1            | DETAIL:  Key ("projectId", "xmlFormId")=(17, p1f2) already exists.
postgres_1            | STATEMENT:
postgres_1            |         with def as (insert into form_defs ("formId", xml, hash, sha, sha256, version, "keyId", "xlsBlobId", "draftToken", "createdAt", "publishedAt")
postgres_1            |           values (nextval(pg_get_serial_sequence('forms', 'id')), $1, $2, $3, $4, $5, $6, $7, $8, now(), $9)
postgres_1            |           returning *),
postgres_1            |         form as (insert into forms (id, name, "xmlFormId", state, "projectId", "draftDefId", "acteeId", "createdAt")
postgres_1            |           select def."formId", $10, $11, $12, $13, def.id, $14, def."createdAt" from def
postgres_1            |           returning forms.*)
postgres_1            |         select id from form;
postgres_1            | LOG:  received fast shutdown request
postgres_1            | LOG:  aborting any active transactions
postgres_1            | FATAL:  terminating connection due to administrator command
postgres_1            | LOG:  autovacuum launcher shutting down
postgres_1            | FATAL:  terminating connection due to administrator command
postgres_1            | LOG:  shutting down
postgres_1            | LOG:  database system is shut down
postgres_1            |
postgres_1            | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres_1            |
postgres_1            | LOG:  database system was shut down at 2021-08-15 09:33:25 UTC
postgres_1            | LOG:  incomplete startup packet
postgres_1            | LOG:  MultiXact member wraparound protections are now enabled
postgres_1            | LOG:  database system is ready to accept connections
postgres_1            | LOG:  autovacuum launcher started
postgres_1            | LOG:  received fast shutdown request
postgres_1            | LOG:  aborting any active transactions
postgres_1            | LOG:  autovacuum launcher shutting down
postgres_1            | FATAL:  terminating connection due to administrator command
postgres_1            | LOG:  shutting down
postgres_1            | LOG:  database system is shut down
postgres_1            |
postgres_1            | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres_1            |
postgres_1            | LOG:  database system was shut down at 2021-08-15 10:47:52 UTC
postgres_1            | LOG:  MultiXact member wraparound protections are now enabled
postgres_1            | LOG:  database system is ready to accept connections
postgres_1            | LOG:  autovacuum launcher started
postgres_1            | LOG:  incomplete startup packet
postgres_1            | ERROR:  invalid input syntax for integer: "Default Project"
postgres_1            | STATEMENT:  select * from "projects" where "deletedAt" is null and "id" = $1 order by coalesce(archived, false) asc, "name" asc
postgres_1            | ERROR:  invalid input syntax for integer: "create"
postgres_1            | STATEMENT:  select "actors"."id" as "actor!id", "actors"."type" as "actor!type", "actors"."acteeId" as "actor!acteeId", "actors"."displayName" as "actor!displayName", "actors"."meta" as "actor!meta", "actors"."createdAt" as "actor!createdAt", "actors"."updatedAt" as "actor!updatedAt", "actors"."deletedAt" as "actor!deletedAt", "users"."actorId" as "user!actorId", "users"."password" as "user!password", "users"."mfaSecret" as "user!mfaSecret", "users"."email" as "user!email" from "users" inner join "actors" on "users"."actorId" = "actors"."id" where "actorId" = $1 and "actors"."deletedAt" is null order by "email" asc
postgres_1            | LOG:  received fast shutdown request
postgres_1            | LOG:  aborting any active transactions
postgres_1            | LOG:  autovacuum launcher shutting down
postgres_1            | LOG:  shutting down
postgres_1            | LOG:  database system is shut down
postgres_1            |
postgres_1            | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres_1            |
postgres_1            | LOG:  database system was shut down at 2021-09-07 16:27:25 UTC

postgres_1            | LOG:  MultiXact member wraparound protections are now enabled
postgres_1            | LOG:  database system is ready to accept connections
postgres_1            | LOG:  autovacuum launcher started
postgres_1            | LOG:  incomplete startup packet
postgres_1            | ERROR:  ODK01:MYEMAIL@outlook.com
postgres_1            | CONTEXT:  PL/pgSQL function check_email() line 9 at RAISE
postgres_1            | STATEMENT:
postgres_1            |         insert into users ("email","password","actorId")
postgres_1            |         values ($1,$2,$3)
postgres_1            |         returning *

I hope this helps.

yanokwa · September 10, 2021, 4:12pm

When you create a web user via API, Central sends a password reset email to that user so they can securely set their password. It looks like your user/password for the mail server Central uses to send that email isn't correct.

That's what your original error message says: Error: Invalid login: 535 5.7.139 Authentication unsuccessful, user password has expired. [LNXP265CA0071.GBRP265.PROD.OUTLOOK.COM.

When the email sending fails, it throws a 500 error in Central.

lgorman · September 10, 2021, 4:14pm

Brilliant thanks so much! Unfortunately I don't have control over this! I have contacted the sysadmin to see if they can resolve it! Have a lovely weekend!

lgorman · September 13, 2021, 12:36pm

Hi all!

Thanks for the help. Turns out that the people configuring the emails forgot to set the passwords to not expire. Nothing to do with central, apologies for the query!