airflow initdb with postgresql not able to create tables

[kameshbrao]

Hi.

Iam trying to setup Airflow on my Kubernetes cluster. Iam using the below versions.

Airflow - 1.10.14
Postgresql - 11.10

Below is my custom Docker file

FROM python:3.6-slim

RUN apt-get update -y && apt-get install -y \
    libczmq-dev \
    libssl-dev \
    inetutils-telnet \
    bind9utils \
    gcc \
    && apt-get clean

RUN pip install --upgrade pip

RUN pip install apache-airflow==1.10.14
RUN pip install 'apache-airflow[kubernetes]'
RUN pip install 'apache-airflow[postgres]'


RUN cd /usr/local && mkdir airflow && chmod +x airflow && cd airflow

ARG AIRFLOW_USER_HOME=/usr/local/airflow
ENV AIRFLOW_HOME=${AIRFLOW_USER_HOME}

RUN useradd -ms /bin/bash 1000
RUN usermod -a -G sudo 1000

COPY airflow-test-env-init.sh /tmp/airflow-test-env-init.sh

RUN mkdir -p /usr/local/airflow/dags
RUN mkdir -p /usr/local/airflow/logs

RUN cp -R /usr/local/lib/python3.6/site-packages/airflow/example_dags/* /usr/local/airflow/dags/ && \
    cp -R /usr/local/lib/python3.6/site-packages/airflow/contrib/example_dags/example_kubernetes_*.py /usr/local/airflow/dags/ && \
    cp -a /usr/local/lib/python3.6/site-packages/airflow/contrib/example_dags/libs /usr/local/airflow/dags/


RUN chown -R 1000:1000 /usr/local
RUN chmod -R 777 /usr/local

COPY bootstrap.sh /bootstrap.sh
RUN chmod +x /bootstrap.sh
USER 1000 
ENTRYPOINT ["/bootstrap.sh"]

#Python Package Dependencies for Airflow
RUN pip install SQLAlchemy==1.3.23
RUN pip install Flask-SQLAlchemy==2.4.4`

As you can see I have a airflow-test-env-init.sh file that has the airflow db init. This is triggered during my pod initialisation.

set -x

airflow initdb && \
alembic upgrade heads && \
(airflow create_user --username airflow --lastname airflow --firstname jon --email airflow@apache.org --role Admin --password airflow || true) 

So when the airflow initdb runs I am getting the below erro:

The 'initdb' command is deprecated and removed in Airflow 2.0, please use 'db init' instead
DB: postgresql+psycopg2://root:***@postgres-airflow-postgresql-helm:5432/airflow
[2021-04-06 06:26:21,511] {db.py:378} INFO - Creating tables
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
**psycopg2.errors.InsufficientPrivilege: permission denied to create "pg_catalog.alembic_version"
DETAIL:  System catalog modifications are currently disallowed.**


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

Traceback (most recent call last):
  File "/usr/local/bin/airflow", line 37, in <module>
    args.func(args)
  File "/usr/local/lib/python3.6/site-packages/airflow/utils/cli.py", line 233, in wrapper
    func(args)
  File "/usr/local/lib/python3.6/site-packages/airflow/bin/cli.py", line 1626, in initdb
    db.initdb(settings.RBAC)
  File "/usr/local/lib/python3.6/site-packages/airflow/utils/db.py", line 323, in initdb
    upgradedb()
  File "/usr/local/lib/python3.6/site-packages/airflow/utils/db.py", line 386, in upgradedb
    command.upgrade(config, 'heads')
  File "/usr/local/lib/python3.6/site-packages/alembic/command.py", line 294, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.6/site-packages/alembic/script/base.py", line 490, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.6/site-packages/alembic/util/compat.py", line 182, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/usr/local/lib/python3.6/site-packages/airflow/migrations/env.py", line 97, in <module>
    run_migrations_online()
  File "/usr/local/lib/python3.6/site-packages/airflow/migrations/env.py", line 91, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.6/site-packages/alembic/runtime/environment.py", line 813, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 544, in run_migrations
    self._ensure_version_table()
  File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 485, in _ensure_version_table
    self._version.create(self.connection, checkfirst=True)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 928, in create
    bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1656, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 832, in visit_table
    include_foreign_key_constraints,  # noqa
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1073, in _execute_ddl
    compiled,
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/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/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied to create "pg_catalog.alembic_version"
DETAIL:  System catalog modifications are currently disallowed.

[SQL:
CREATE TABLE alembic_version (
	version_num VARCHAR(32) NOT NULL,
	CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)

]
(Background on this error at: http://sqlalche.me/e/13/f405)

Any help appreciated.

Thanks,
Kamesh

[kameshbrao]

Ok this is solved now as mentioned in the Airflow documentation, I need to set the search_path in postgres to use a default schema as SQLALchemy has no way to know the default schema.

alter role root in database airflow set search_path to public,airflow;