That's should be a fairly easy task - I'm trying to bringup a new Airflow cluster (should be supported).
I chose Postgres 15.2 engine in AWS RDS (currently the latest version) and followed the instructions.
While running the airflow db init command I got the following exception:
[2023-03-05 21:43:13,139] {db.py:1448} INFO - Creating tables
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
Traceback (most recent call last):
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
self.dialect.do_execute(
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.InsufficientPrivilege: permission denied for schema public
LINE 2: CREATE TABLE alembic_version (
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/private/tmp/venv/bin/airflow", line 8, in <module>
sys.exit(main())
File "/private/tmp/venv/lib/python3.9/site-packages/airflow/__main__.py", line 38, in main
args.func(args)
File "/private/tmp/venv/lib/python3.9/site-packages/airflow/cli/cli_parser.py", line 51, in command
return func(*args, **kwargs)
File "/private/tmp/venv/lib/python3.9/site-packages/airflow/cli/commands/db_command.py", line 35, in initdb
db.initdb()
File "/private/tmp/venv/lib/python3.9/site-packages/airflow/utils/session.py", line 71, in wrapper
return func(*args, session=session, **kwargs)
File "/private/tmp/venv/lib/python3.9/site-packages/airflow/utils/db.py", line 648, in initdb
upgradedb(session=session)
File "/private/tmp/venv/lib/python3.9/site-packages/airflow/utils/session.py", line 68, in wrapper
return func(*args, **kwargs)
File "/private/tmp/venv/lib/python3.9/site-packages/airflow/utils/db.py", line 1449, in upgradedb
command.upgrade(config, revision=to_revision or 'heads')
File "/private/tmp/venv/lib/python3.9/site-packages/alembic/command.py", line 378, in upgrade
script.run_env()
File "/private/tmp/venv/lib/python3.9/site-packages/alembic/script/base.py", line 569, in run_env
util.load_python_file(self.dir, "env.py")
File "/private/tmp/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
module = load_module_py(module_id, path)
File "/private/tmp/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
spec.loader.exec_module(module) # type: ignore
File "<frozen importlib._bootstrap_external>", line 850, in exec_module
File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
File "/private/tmp/venv/lib/python3.9/site-packages/airflow/migrations/env.py", line 107, in <module>
run_migrations_online()
File "/private/tmp/venv/lib/python3.9/site-packages/airflow/migrations/env.py", line 101, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/private/tmp/venv/lib/python3.9/site-packages/alembic/runtime/environment.py", line 867, in run_migrations
self.get_context().run_migrations(**kw)
File "/private/tmp/venv/lib/python3.9/site-packages/alembic/runtime/migration.py", line 606, in run_migrations
self._ensure_version_table()
File "/private/tmp/venv/lib/python3.9/site-packages/alembic/runtime/migration.py", line 544, in _ensure_version_table
self._version.create(self.connection, checkfirst=True)
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 943, in create
bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2016, in _run_ddl_visitor
visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 483, in traverse_single
return meth(obj, **kw)
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 890, in visit_table
self.connection.execute(
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
return connection._execute_ddl(
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1290, in _execute_ddl
ret = self._execute_context(
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
self._handle_dbapi_exception(
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
util.raise_(
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
self.dialect.do_execute(
File "/private/tmp/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied for schema public
LINE 2: CREATE TABLE alembic_version (
^
[SQL:
CREATE TABLE alembic_version (
version_num VARCHAR(32) NOT NULL,
CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)
]
After digging around I've found that Postgres 15 release notes states that:
Remove PUBLIC creation permission on the public schema
I also reproduced it locally with docker, if I set only POSTGRES_PASSWORD (without POSTGRES_USER & POSTGRES_DB!):
version: '3.7'
services:
postgres:
image: postgres:15.2
environment:
POSTGRES_PASSWORD: 123456
ports:
- 5432:5432
volumes:
- airflow-postgres-vol-data:/var/lib/postgresql/data
volumes:
airflow-postgres-vol-data:
It fails as well. One workaround that worked was:
ALTER USER airflow_user WITH SUPERUSER;
but it seems like a wrong solution (too much power for airflow_user) - as it worked as a non superuser in a previous Postges versions.
What is the right/recommended way of initializing Postgres-15 as Airflow's backend?
So after creating the database and the user as explained in Airflow's documentation:
CREATE DATABASE airflow_db;
CREATE USER airflow_user WITH PASSWORD 'airflow_pass';
GRANT ALL PRIVILEGES ON DATABASE airflow_db TO airflow_user;
I had to switch to the new database and then add privileges:
USE airflow_db;
GRANT ALL ON SCHEMA public TO airflow_user;