Postgres connection URI incompatible with SQLAlchemy >= 1.4.0b1 #15349

Apache Airflow version: 2.0.1 (possibly any?)

Kubernetes version (if you are using kubernetes) (use kubectl version): N/A

Environment: Any

• Cloud provider or hardware configuration: Any
• OS (e.g. from /etc/os-release): Any
• Kernel (e.g. uname -a): Any
• Install tools: Any
• Others: Any

What happened:

From SQLAlchemy version 1.4.0b1, SQLAlchemy removed the support for the postgres prefix in URIs (used for example in create_engine), and now it supports only postgresql.

This affects all Airflow jobs that:
(A) Use SQLAlchemy version >= 1.4.0
(B) Obtain/use a Postgres URI from the get_uri method of a PostgresHook (method defined in the DbApiHook - airflow/hooks/dbapi.py).

This is because the PostgresHook (airflow/providers/postgres/hooks/postgres.py) class, defines a class attribute conn_type = 'postgres' instead of postgresql.

Right now my airflow jobs with the updated SQLAlchemy that are using SQLAlchemy create_engine with a postgres db are broken.

What you expected to happen:

That Airflow jobs using SQLAlchemy >= 1.4.0 and SQLAlchemy's create_engine with Postgres databases work correctly, and that get_uri generates a postgres URI starting with postgresql://.

Of course I could use an older SQLAlchemy version or manipulated the returned URI, but I guess it'd be nice if Airflow adapts to the updated prefix.

How to reproduce it:

Use the get_uri method of any PostgresHook.

Anything else we need to know:

N/A