Docker-airflow: Fail to create user on users table on Docker Webserver Container

[jensenity]

cd ~/airflow
python
Python 3.6.5 (default, Jun 6 2018, 02:51:26)
[GCC 6.3.0 20170516] on linux
Type "help", "copyright", "credits" or "license" for more information.
import airflow
from airflow import models, settings
from airflow.contrib.auth.backends.password_auth import PasswordUser
user = PasswordUser(models.User())
user.username = 'jensen'
user.email = '[email protected]'
user._set_password = generate_password_hash("$3", 12).decode('utf-8')
session = settings.Session()
session.add(user)
session.commit()
session.close()
exit()

I ran this to create a web authentification user and password.

But I keep encountering this problem

Traceback (most recent call last):
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 508, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: users

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

Traceback (most recent call last):
File "", line 1, in
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 943, in commit
self.transaction.commit()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 467, in commit
self._prepare_impl()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
self.session.flush()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
self._flush(objects)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
transaction.rollback(_capture_exception=True)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
raise value
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
flush_context.execute()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
rec.execute(self)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
uow
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
mapper, table, insert)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 866, in _emit_insert_statements
execute(statement, params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
context)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 508, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: users [SQL: 'INSERT INTO users (username, email, password) VALUES (?, ?, ?)']

Anyone know how to fix this problem?

I ran the python code on the Docker Webserver container bash

docker-compose exec -u root webserver bash

[jensenity]

Fixed by adding hash password on postgres db directly through query.

[nabinkhadka]

For anyone who wants to know what @jensenity means:

1. pip install 'sqlalchemy>=1.1.15, <1.2.0' should be done to be able to create a user.

2. Create user from UI:
   

3. Set password from backend using:

from flask_bcrypt import generate_password_hash
generate_password_hash('choose_password', 12)

This will generate b'$2b$12$FWd8fyHvADfZqcjfmPE/3enGDCXfOS3pNsaKuUe76pf.HovU6P7YK'
Copy $2b$12$FWd8fyHvADfZqcjfmPE/3enGDCXfOS3pNsaKuUe76pf.HovU6P7YK

1. Then run the following in postgres shell

airflow=# \connect airflow /* Use the database. */
airflow=# update users set password ='$2b$12$FWd8fyHvADfZqcjfmPE/3enGDCXfOS3pNsaKuUe76pf.HovU6P7YK';

[pathakumesh]

Simple user creation from postgres shell should work:
insert into users values (<id>, <user>, <email>, <hased_password_value>);

[ngould]

Should this have been closed? I just experienced the same issue, and the solutions mentioned here are really only workarounds.

[fabiosato]

Just hit the same problem.

From the logs I noticed it is trying to save it to SQLite instead of PostgreSQL.

Was able to fix it to be able to run the python commands to create an user by modifying airflow.cfg section with the following parameter in the core section:

sql_alchemy_conn = postgresql://airflow:airflow@postgres/airflow

[ashb]

If you are execing in to the container you need to run something like /entrypoint.sh bash to get the right environment variables set up, and you might need to export AIRFLOW__HOME="$AIRFLOW_HOME" (untested) - I hope to fix the AIRFLOW__HOME issue in 1.10.3

For example:

docker-compose exec webserver /entrypoint.sh bash
airflow create_user ...

[zerobatu]

If you want this code work

import airflow
from airflow import models, settings
from airflow.contrib.auth.backends.password_auth import PasswordUser
user = PasswordUser(models.User())
user.username = 'jensen'
user.email = '[email protected]'
user._set_password = generate_password_hash("$3", 12).decode('utf-8')
session = settings.Session()
session.add(user)
session.commit()
session.close()
exit()

you must run the python shell setting the environment variables AIRFLOW__CORE__SQL_ALCHEMY_CONN

docker-compose exec --env AIRFLOW__CORE__SQL_ALCHEMY_CONN="postgresql+psycopg2://airflow:airflow@postgres:5432/airflow" webserver python