Redash can't create user from Google with long profile_image_url

[spacentropy]

Redash can't create user with long profile_image_url

My coworker tried to login to redash and it failed.

[2019-12-20 14:32:12,927] ERROR in app: Exception on /oauth/google_callback [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python2.7/site-packages/flask_restful/__init__.py", line 271, in error_router
    return original_handler(e)
  File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/app/redash/authentication/google_oauth.py", line 101, in authorized
    user = create_and_login_user(org, profile['name'], profile['email'], picture_url)
  File "/app/redash/authentication/__init__.py", line 257, in create_and_login_user
    models.db.session.commit()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 153, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 866, in _emit_insert_statements
    execute(statement, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
DataError: (psycopg2.DataError) value too long for type character varying(320)
 [SQL: 'INSERT INTO users (updated_at, created_at, org_id, name, email, profile_image_url, password_hash, groups, api_key, disabled_at, details) VALUES (now(), now(), %(org_id)s, %(name)s, %(email)s, %(profile_image_url)s, %(password_hash)s, %(groups)s, %(api_key)s, %(disabled_at)s, %(details)s) RETURNING users.id'] [parameters: {'name': u'<redacted>', 'org_id': <redacted>, 'profile_image_url': u'https://lh3.googleusercontent.com/a-/AAuE7mCQC1y8a0Bew0vZ3zVr835IDK1pq8_J75Jy4YNUwe2TdaYqr8vJBF1eQB8k5u6kooonWTfrnVdpOjR3_Epvit-sKbkbjq12GgcW6qv1iva ... (517 characters truncated) ... JfmDWlN_ESNOyJu6JRgNKLqFN5pJQJQ44IcS0OEt5ozElvbEV35vX7sw-OBptVnBUPW4wy9cElsIhnw8ISHgp8zSqJhwQfrn5bII6fN42EMrq1_sv66KBAm-0NIit0QYWkocdT58V4PClb8?sz=40', 'disabled_at': None, 'details': '{"is_invitation_pending": false}', 'groups': [2], 'api_key': '<redacted>', 'email': u'<redacted>', 'password_hash': None}] (Background on this error at: http://sqlalche.me/e/9h9h)

Turns out, he had basic auto-generated one letter avatar (more info) for which Google returns profile_image_url 815 characters long. Maybe they generate it on the fly, who knows. We changed this auto-generated avatar to a normal one and it succeeded.

Steps to Reproduce

1. Have an user in identity provider (G suite in our case) with profile_image_url longer than 320 characters
2. Try to login first time
3. Get internal server error

Technical details:

• Redash Version: 8.0
• Browser/OS: Chrome
• How did you install Redash: Helmchart

[mbassett]

Ran across this issue as well today. User uploaded the same picture again, and was given a smaller URL which worked.

Can we get the schema updated to allow for extra length in this field?

[arikfr]

Let's move this to the details JSON column we have on the User model and then we won't have any such or similar issues.

[arikfr]

Another interim quick solution to allow creating the user is to check the length of the URL before assigning it (and assign null if it's too long).

[briandilley]

This is what I did (as a workaround) to address it (My redash is in docker):

docker exec redash_postgres_1 su postgres -c "psql -d postgres -c 'alter table users add column profile_image_url_new varchar null'"
docker exec redash_postgres_1 su postgres -c "psql -d postgres -c 'update users set profile_image_url_new=profile_image_url'"
docker exec redash_postgres_1 su postgres -c "psql -d postgres -c 'alter table users drop column profile_image_url'"
docker exec redash_postgres_1 su postgres -c "psql -d postgres -c 'alter table users rename column profile_image_url_new to profile_image_url'"

[susodapop]

This is what I did (as a workaround) to address it (My redash is in docker):

docker exec redash_postgres_1 su postgres -c "psql -d postgres -c 'alter table users add column profile_image_url_new varchar null'"
docker exec redash_postgres_1 su postgres -c "psql -d postgres -c 'update users set profile_image_url_new=profile_image_url'"
docker exec redash_postgres_1 su postgres -c "psql -d postgres -c 'alter table users drop column profile_image_url'"
docker exec redash_postgres_1 su postgres -c "psql -d postgres -c 'alter table users rename column profile_image_url_new to profile_image_url'"

If this is necessary through the long-term it would be best to write an alembic migration for it.

[mweibel]

👋 just ran across this issue too. Any idea when a new version will be released containing this fix?

[susodapop]

I'm looking to cut a new release this summer yet.

[odinsy]

how about?

ALTER TABLE users ALTER COLUMN profile_image_url TYPE character varying(1024);

[susodapop]

@odinsy That's a reasonable short-term solution 👍