Can't add user -- column id is not unique

[MartinPJones]

I can't add more than one user at a time to Crackq without having to restart it. I get the error "Error adding user: undefined". When looking in the logs, I find:

DEBUG    cq_api.py:1868 post 2021-01-26 22:49:53,927 Creating User: test
ERROR    app.py:1892 log_exception 2021-01-26 22:49:54,104 Exception on /api/admin/ [POST]
Traceback (most recent call last):
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 609, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: column id is not unique
...
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) column id is not unique
[SQL: INSERT INTO user (id, active, username, email, last_login_at, last_seen, current_login_at, last_login_ip, current_login_ip, login_count, confirmed_at, is_admin, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: (<memory at 0x7fbbbcff0108>, None, 'test', 'test@test.com', None, None, None, None, None, None, None, 0, '{some hash}')]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

[f0cker]

Are you adding more than one user using the add_admuser.py script?

[MartinPJones]

Initially, yes. When I encountered #26 I created a second admin user with a valid password and deleted the first. Is there any fix, aside from deleting the database and letting it regenerate?

[f0cker]

Easiest thing to do would be to delete it yeah.

[f0cker]

I'll close this one off and add the additional allowed characters in the next push.

[MartinPJones]

Note: Deleting the database and regenerating it did not fix this. I'll try reinstalling crackq and see if that helps.

[MartinPJones]

After completing a fresh install, I can say this issue does not appear to be from using the admin command twice. I used it once on this install, and still have the error. Full error logs below:

DEBUG    cq_api.py:1868 post 2021-02-02 06:47:23,238 Creating User: test
ERROR    app.py:1892 log_exception 2021-02-02 06:47:23,416 Exception on /api/admin/ [POST]
Traceback (most recent call last):
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: column id is not unique

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.6/site-packages/flask/views.py", line 89, in view
    return self.dispatch_request(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/flask/views.py", line 163, in dispatch_request
    return meth(*args, **kwargs)
  File "/opt/crackq/build/crackq/cq_api.py", line 364, in wrap
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/flask_login/utils.py", line 272, in decorated_view
    return func(*args, **kwargs)
  File "/opt/crackq/build/crackq/cq_api.py", line 1872, in post
    password=pass_hash, email=email)
  File "/opt/crackq/build/crackq/cq_api.py", line 393, in create_user
    db.session.commit()
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 163, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/session.py", line 1046, in commit
    self.transaction.commit()
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/session.py", line 504, in commit
    self._prepare_impl()
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
    flush_context.execute()
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 1136, in _emit_insert_statements
    statement, params
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1130, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) column id is not unique
[SQL: INSERT INTO user (id, active, username, email, last_login_at, last_seen, current_login_at, last_login_ip, current_login_ip, login_count, confirmed_at, is_admin, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: (<memory at 0x7fe28a55b408>, None, 'test', None, None, None, None, None, None, None, None, 0, '{{Some hash here}}')]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

Same issue. I can create the admin user with the tool and I can create a single user from the UI, and then when trying to create a second I get this error. Does not appear to be related to having used the tool twice the first time I encountered this error. Does this log show the issue, or are there other logs/reports I should try to grab?

[f0cker]

OK I'll reopen it. Did you delete /var/crackq/files/crackq.db? I can't reproduce the issue locally, can you document a reproducible process for me please using dummy values you can paste here? Thanks.

[MartinPJones]

I did, I'll try going through a reproducible attempt at it. May just switch to LDAP if I can't find the issue on my side making this, but hopefully can find how to make it reproducible.

[kernel-sanders]

While debugging I found the default for user.id is set to the memory address of uuid.uuid4().hex function from https://github.com/f0cker/crackq/blob/38634179ef4c1e2040c33551480dadddb05d410b/crackq/models.py#L24

In it's current state you can only add 1 new user per container restart.

I fixed this by adding a separate function for the default and it has fixed the issue:

from sqlalchemy.types import TypeDecorator, CHAR
from sqlalchemy.dialects.postgresql import UUID

class GUID(TypeDecorator):
    """Platform-independent GUID type.
    Uses PostgreSQL's UUID type, otherwise uses
    CHAR(32), storing as stringified hex values.
    """
    impl = CHAR

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(UUID())
        else:
            return dialect.type_descriptor(CHAR(32))

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == 'postgresql':
            return str(value)
        else:
            if not isinstance(value, uuid.UUID):
                return "%.32x" % uuid.UUID(value).int
            else:
                # hexstring
                return "%.32x" % value.int

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            if not isinstance(value, uuid.UUID):
                value = uuid.UUID(value)
            return value

# This is needed because otherwise the default is only generated once and a second user being added will have a unique check failure on user.id until the app is restarted... fun.
def generate_uuid():
    return str(uuid.uuid4())

class User(db.Model):
    """Flask-login User model for session management"""
    __tablename__ = 'user'
    id = Column(UUIDType(binary=True), default=uuid.uuid4().hex, primary_key=True,
                index=True)
    id = db.Column(GUID(), primary_key=True, default=generate_uuid, index=True)

[f0cker]

@kernel-sanders thanks for this

[MartinPJones]

This is the exact issue I was having. That should fix this issue. Can close when pulled.

On Tue, Apr 6, 2021 at 09:37 f0cker @.***> wrote:

@kernel-sanders https://github.com/kernel-sanders thanks for this

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/f0cker/crackq/issues/27#issuecomment-814219505, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALISMBF7KZ36QS64AU5DRGDTHMTC3ANCNFSM4WUIQSLA .

--

Alex Adams

[f0cker]

I've pushed the fix to the dev_tasks branch if you want to test it out. I'll merge it in a few weeks