Couldn't login with new account on the ckan localhost website

[Team1-epat]

Hi,

I am currently using ckan version 2.10 and successfully setup the ckanext-security (with tag 4.0.0), after that, I used the ckan website to create a new account called tester1.

I tried to use tester1 to login in the ckan website, and couldn't login, so I went to the docker console and attached the log as below.

2024-02-26 19:09:20,134 ERROR [ckanext.security.utils] Unhandled error during login: (psycopg2.errors.UndefinedTable) relation "user_security_totp" does not exist
LINE 2: FROM user_security_totp JOIN "user" ON "user".id=user_secu...
         ^
[SQL: SELECT user_security_totp.id AS user_security_totp_id, user_security_totp.user_id AS user_security_totp_user_id, user_security_totp.secret AS user_security_totp_secret, user_security_totp.last_successful_challenge AS user_security_totp_last_successful_challenge
FROM user_security_totp JOIN "user" ON "user".id = user_security_totp.user_id
WHERE "user".name = %(name_1)s
 LIMIT %(param_1)s]
[parameters: {'name_1': 'tester1', 'param_1': 1}]
(Background on this error at https://sqlalche.me/e/14/f405)
Traceback (most recent call last):
   File "/usr/lib/python3.10/site-package/sqlalchemy/engine/base.py", line 1900, in _execute_context
      self.dialect.do_execute(
   File "/usr/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
      cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "user_security_totp" does not exist
LINE 2: FROM user_security_totp JOIN "user" ON "user".id = user_secu...
         ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last)
   File "srv/app/src_extensions/security/ckanext/security/utils.py", line 172, in login
      totp_challenger = SecurityTOTP.get_for_user(user.name)
   File "srv/app/src_extension/security/ckanext/security/model.py", line 93, in get_for_user
      .filter(User.name == user_name).first()
   File "/usr/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2823, in first
      return self.limit(1)._iter().first()
   File "/usr/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2907, in _iter
      result = self.session.execute(
   File "/usr/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
      result = conn._execute_20(statement, params or {}, execution_options)
   File "/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
      return meth(self, args_10style, kwargs_10style, execution_options)
   File "/usr/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
      return connection._execute_clauseelement(
   File "/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
      ret = self._execute_context(
   File "/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
      self._handle_dbapi_exception(
   File "/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
      util.raise_(
   File "/usr/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
      raise exception
   File "/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
      self.dialect.do_execute(
   File "/usr/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
      cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "user_security_totp" does not exist
LINE 2: FROM user_security_totp JOIN "user" ON "user".id = user_secu...
         ^

[SQL: SELECT user_security_totp.id AS user_security_totp_id, user_security_totp.user_id AS user_security_totp_user_id, user_security_totp.secret AS user_security_totp_secret, user_security_totp.last_successful_challenge AS user_security_totp_last_successful_challenge 
FROM user_security_totp JOIN "user" ON "user".id = user_security_totp.user_id 
WHERE "user".name = %(name_1)s
   LIMIT %(param_1)s]
[parameters: {'name_1': 'tester1', 'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/14/f405)  

will someone please advise how can I fix the issue at my end?

I tried to put the same configuration in the who.ini

[plugin:use_beaker]
use = repoze.who.plugins.use_beaker:make_plugin
key_name = ckan_session
delete_on_logout = True

[plugin:friendlyform]
# <your other settings here>
rememberer_name = use_beaker

[identifiers]
plugins =
    friendlyform;browser
    use_beaker

[authenticators]
plugins =
    ckanext.security.authenticator:CKANLoginThrottle
    ckanext.security.authenticator:BeakerRedisAuth

As for the ckan.ini, I only configured those that I think I need. Not very sure if I did it wrongly or not.

ckanext.security.redis.host = redis
ckanext.security.redis.port = 6379
ckanext.security.redis.db = 1
ckanext.security.redis.password = Password123!

beaker.session.type = ext:redis
beaker.session.url = redis://default:Password123!@redis:6379

ckanext.security.lock_timeout = 900
ckanext.security.login_max_count = 3
ckanext.security.brute_force_key =

ckanext.security.disable_password_reset_override = true

ckanext.security.enable_totp = false

[markstuart]

Hi, looks like you need to run the migration to add the user_security_totp table.

See the section of the readme here: https://github.com/data-govt-nz/ckanext-security?tab=readme-ov-file#how-to-install

Looking at your ckan.ini file above it looks like you may be expecting that you don't have to run that migration?

The config setting ckanext.security.enable_totp = false only exists on a unmerged branch at present, there is no way to disable the Two Factor Auth in the 4.0.0 tag.

[markstuart]

I've just released a new 4.1.0 version with the ckanext.security.enable_totp config option, this should prevent the need to run that ckan security migrate as the login flow shouldn't hit the SecurityTOTP class anymore.

[Team1-epat]

Hi, thank you, it is working fine for my side