Postgres + Airflow db: permission denied for schema public

Question

I'm trying to setup Postgres as backend database for my Airflow installation.

I followed what the documentation suggests. Particularly, I created a specific user for Airflow in Postgres, a specific new table, and granted all privileges to the user. I executed the commands, similar as suggested:

CREATE DATABASE airflow_db;
CREATE USER airflow_user WITH PASSWORD 'airflow_pass';
GRANT ALL PRIVILEGES ON DATABASE airflow_db TO airflow_user;

Also, I edited the sql_alchemy_conn string in airflow.cfg.

Now, when I run airflow db init, I always get the same error:

DB: postgresql+psycopg2://airflow_user:***@localhost:5434/airflow_db
[2022-11-10 15:19:38,319] {migration.py:204} INFO - Context impl PostgresqlImpl.
[2022-11-10 15:19:38,319] {migration.py:207} INFO - Will assume transactional DDL.
Traceback (most recent call last):
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InsufficientPrivilege: permission denied for schema public
LINE 2: CREATE TABLE dag_code (
                     ^


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

Traceback (most recent call last):
  File "/Users/phil/airflowv/bin/airflow", line 8, in <module>
    sys.exit(main())
  File "/Users/phil/airflowv/lib/python3.10/site-packages/airflow/__main__.py", line 39, in main
    args.func(args)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/airflow/cli/cli_parser.py", line 52, in command
    return func(*args, **kwargs)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/airflow/cli/commands/db_command.py", line 37, in initdb
    db.initdb()
  File "/Users/phil/airflowv/lib/python3.10/site-packages/airflow/utils/session.py", line 75, in wrapper
    return func(*args, session=session, **kwargs)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/airflow/utils/db.py", line 683, in initdb
    _create_db_from_orm(session=session)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/airflow/utils/db.py", line 666, in _create_db_from_orm
    Base.metadata.create_all(settings.engine)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4785, in create_all
    bind._run_ddl_visitor(
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3110, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2113, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 846, in visit_metadata
    self.traverse_single(
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 890, in visit_table
    self.connection.execute(
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
    return connection._execute_ddl(
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_ddl
    ret = self._execute_context(
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
    util.raise_(
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/Users/phil/airflowv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied for schema public
LINE 2: CREATE TABLE dag_code (
                     ^

[SQL: 
CREATE TABLE dag_code (
    fileloc_hash BIGINT NOT NULL, 
    fileloc VARCHAR(2000) NOT NULL, 
    last_updated TIMESTAMP WITH TIME ZONE NOT NULL, 
    source_code TEXT NOT NULL, 
    CONSTRAINT dag_code_pkey PRIMARY KEY (fileloc_hash)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)

The SQLAlchemy doc didn't help. I tried to resolve this, but no success. Again, I granted ALL privileges to the airflow user on the new airflow database.

are you sure the airflow path is correct?(`echo $AIRFLOW_HOME`) — Danila Ganchar, Nov 10 '22 at 14:43
Yes. The issue here should not be the airflow home path itself, but whether the sqlalchemy connection is set correctly — PandaPhi, Nov 10 '22 at 14:46

score 7 · Answer 1 · answered Nov 10 '22 at 14:50

After trying out several different things, I found a simple fix:

ALTER DATABASE airflow_db OWNER TO airflow_user;

sets the owner of the database from the standard/admin user (e.g. postgres) to airflow_user.

Now I run airflow db init again, and the error doesn't come up, instead I get Initialization done.

Postgres + Airflow db: permission denied for schema public

1 Answers1