Unable to login anymore with LDAP

[gabrielctn]

I messed up something...
I had a fine working docker wikijs server with ldap authentication. Then I upgraded my LDAP server to LDAPS, unfortunately I forgot to change the config inside wikijs before, so I can't login anymore...
Now I'm stuck and I cannot login anymore into my wikijs server... the error is "Invalid email/ username or password" but I'm certain both are correct. Also, I know that the ldap debug option is enabled, but I don't see anything in the docker-compose logs.

I tried to change directly the config into the postgresql database, but I am unable to make it work again... and my commands changed the local auth config too (yes I know...)... how can I reset it to be able to reconfigure correctly the ldap ?

Thanks in advance for your help!

Here is the current authentication configuration:

$ docker exec wikijs-db-1 psql -U wikijs -d wiki -c "select * from authentication"
                 key                  | isEnabled |                                                                                                                                                                                                                                                                                                              config                                                                                                                                                                                                                                                                                                              | selfRegistration | domainWhitelist | autoEnrollGroups | order | strategyKey | displayName
--------------------------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------+------------------+-------+-------------+-------------
 e04afa16-7ee0-48b7-953d-60c542abc4cd | t         | {"url": "ldap:https://10.115.31.3:389", "bindDn": "cn=admin,dc=XX,dc=XX,dc=XX", "mapGroups": false, "mappingUID": "uid", "searchBase": "OU=Users,dc=XX,dc=XX,dc=XX", "tlsEnabled": false, "tlsCertPath": "", "mappingEmail": "mail", "searchFilter": "(&(objectClass=posixAccount)(uid={{username}}))", "groupNameField": "name", "mappingPicture": "jpegPhoto", "bindCredentials": "XXXX", "groupDnProperty": "dn", "groupSearchBase": "OU=groups,dc=example,dc=com", "groupSearchScope": "sub", "groupSearchFilter": "(member={{dn}})", "mappingDisplayName": "cn", "verifyTLSCertificate": false} | t                | {"v":[]}        | {"v":[3]}        |     0 | ldap        | LDAP
 local                                | t         | {"url": "ldap:https://10.115.31.3:389", "tlsEnabled": false, "tlsCertPath": "", "verifyTLSCertificate": false}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | f                | {"v":[]}        | {"v":[]}         |     1 | local       | Local

[asbayevy]

Hello, try these commands to disable LDAP
update authentication set "isEnabled" = 'No' where config->>'bindDn' = 'cn=admin,dc=XX,dc=XX,dc=XX';
then to restore the local parameter, try
update authentication set config = '{"tlsEnabled": false, "tlsCertPath": "", "verifyTLSCertificate": false}', "isEnabled" = 't', "selfRegistration" = 'f', "domainWhitelist" = '{"v":[]}', "autoEnrollGroups" = '{"v":[]}', "order" = 0, "strategyKey" = 'local', "displayName" = 'Local-ASD' where "key" = 'local';

[gabrielctn]

Thanks a lot for your help @asbayevy !
I ran your commands and I ended up without any login option because I had set "hideLocal":true in settings.
I solved that by setting it back to true with the command:

docker exec wikijs-db-1 psql -U wikijs -d wiki -c "update settings set value = '{\"autoLogin\":true,\"enforce2FA\":null,\"hideLocal\":false,\"loginBgUrl\":null,\"audience\":\"urn:wiki.js\",\"tokenExpiration\":\"30m\",\"tokenRenewal\":\"14d\"}' where \"key\" = 'auth';"

The thing is that I am still unable to login with my credentials. I get the same error as originally "Invalid email/ username or password".
How can I verify that I am technically able to login with local provider ? I should maybe change my password manually ?
Here are the details for me as a user in the database. It looks like I exist also in the local provider with the same password as for LDAP.

$ docker exec wikijs-db-1 psql -U wikijs -d wiki -c "select * from users where \"name\"='Gabriel Cretin'"
 id |                  email                  |             name              |  providerId  |                        password                        | tfaIsActive | tfaSecret | jobTitle  | location | pictureUrl |     timezone     | isSystem | isActive | isVerified | mustChangePwd |        createdAt         |        updatedAt         |             providerKey              | localeCode | defaultEditor |       lastLoginAt        | dateFormat | appearance
  1 | [email protected]                | Gabriel Cretin                |              | a23M.xURN0a8HkwrI9gBnI.sS57AxLmjJbYHCFsAwXhpI19Soepkxa | f           |           |           |          |            | Europe/Paris     | f        | t        | t          | f             | 2021-08-24T15:34:31.385Z | 2023-02-15T10:22:49.610Z | local                                | en         | markdown      | 2023-02-15T10:21:23.658Z | DD/MM/YYYY |
  3 | [email protected]                | Gabriel Cretin                | cretin       | a23M.xURN0a8HkwrI9gBnI.sS57AxLmjJbYHCFsAwXhpI19Soepkxa | f           |           |           |          |            | America/New_York | f        | t        | t          | f             | 2023-02-15T10:18:37.605Z | 2024-02-16T13:43:55.633Z | e04afa16-7ee0-48b7-953d-60c542abc4cd | fr         | markdown      | 2024-02-20T16:03:39.556Z |            |

[gabrielctn]

Thanks for your answer !
How can I change the password ? It is hashed I see which hash should I use ?

[asbayevy]

to change your password, you can read it in the official source:

https://docs.requarks.io/en/troubleshooting -> How to manually reset the admin password?

[gabrielctn]

Thanks ! I did it, but still "Invalid email/ username or password" ...

[asbayevy]

Try the following script to create a user and add it to groups,

**-- insert/create new user
WITH new_user AS (
INSERT INTO public.users (email, name, password, "isActive", "isVerified", "mustChangePwd", "createdAt", "updatedAt")
VALUES ('[email protected]', 'useriam', '$2a$12$R1byD/SYMOMFA.3CDkC74eD.N3ANTXeOTj6DfjSL4aqpqQsn6dKd6', true, true, true, now(), now())
RETURNING id
),
-- insert useriam in usergroups
admin_group AS (
INSERT INTO "userGroups" (id, "userId", "groupId")
VALUES ('3', '3', 1)
ON CONFLICT (id) DO NOTHING
)

SELECT 'Users created and assigned to administrator group' AS status;**

where VALUES ('3', '3', 1) is id, userid and groupid, 1 is admin group, where '$2a$12$R1byD/SYMOMFA.3CDkC74eD.N3ANTXeOTj6DfjSL4aqpqQsn6dKd6' is the password 123qweQWE, you can change it to something else.

[gabrielctn]

Great !! I did it, and it worked the login is successful (I get the green window saying Connexion successful), however I directly arrive on this page saying I am not authorized, which is strange if the user is in group Administrator 🤔

I simply try to access the /home page...
Is there a permission I need to add somewhere else ? Maybe I have to add the user to another group or something ? I can give you the output of some commands if you need more information about the settings in place.
But we are getting there !!

[gabrielctn]

OH ! Wait, I figured out something.
I checked at the user I just created to see any difference:

 id |                  email                  |             name              |  providerId  |                           password                           | tfaIsActive | tfaSecret | jobTitle  | location | pictureUrl |     timezone     | isSystem | isActive | isVerified | mustChangePwd |           createdAt           |        updatedAt         |             providerKey              | localeCode | defaultEditor |       lastLoginAt        | dateFormat | appearance
----+-----------------------------------------+-------------------------------+--------------+--------------------------------------------------------------+-------------+-----------+-----------+----------+------------+------------------+----------+----------+------------+---------------+-------------------------------+--------------------------+--------------------------------------+------------+---------------+--------------------------+------------+------------
  2 | [email protected]                       | Guest                         |              |                                                              | f           |           |           |          |            | America/New_York | t        | t        | t          | f             | 2021-08-24T15:34:32.033Z      | 2021-08-24T15:34:32.033Z | local                                | en         | markdown      |                          |            |
  1 | [email protected]                | Gabriel Cretin                |              | a2/sJkJ4SfdrfZNeIRBr3PYDk.rmyymFmZhd.WlM7l8EE7S              | f           |           |           |          |            | Europe/Paris     | f        | t        | t          | f             | 2021-08-24T15:34:31.385Z      | 2023-02-15T10:22:49.610Z | local                                | en         | markdown      | 2023-02-15T10:21:23.658Z | DD/MM/YYYY |
 41 | [email protected]                     | useriam                       |              | $2a$12$xeU.pQPKrdzATW68BdFd/Onp/ODr./wABDoueC4cXez4BirIoy4gS | f           |           |           |          |            | America/New_York | f        | t        | t          | f             | 2024-02-28 10:10:45.130711+00 | 2024-02-28T10:11:29.009Z | local                                | en         | markdown      | 2024-02-28T10:20:45.835Z |            |
  3 | [email protected]                | Gabriel Cretin                | cretin       | a2/sJkJ4SfdrfZNeIRBr3PYDk.rmyymFmZhd.WlM7l8EE7S              | f           |           |           |          |            | America/New_York | f        | t        | t          | f             | 2023-02-15T10:18:37.605Z      | 2024-02-16T13:43:55.633Z | e04afa16-7ee0-48b7-953d-60c542abc4cd | fr         | markdown      | 2024-02-20T16:03:39.556Z |            |

And I saw that my hashed passwords looked of a different type than the one I just set for useriam.
So I re-changed it with a hash generated by https://bcrypt-generator.com/ and it worked, I am able to connect now as me as local user.
I don't really know why the password was not rightly hashed because I checked and I used exactly the same command, the one from the troubleshooting page:

UPDATE users SET password = '$2a$12$6bE71RW8uAZpnOAaoPX4ie2F4TjCAke4Wyf8lCPBV4FOWXcyVlWxi' WHERE email = '[email protected]';

Anyway, thank you for your help I will try to debug my ldap settings now that I can connect to the interface :)