airflow postgresql backend: (psycopg2.OperationalError) FATAL: Ident authentication failed for user "airflow"

Question

Trying to use postgresql as backend for airflow (v1.10.5) on centos7 machine (following this article: https://www.ryanmerlin.com/2019/07/apache-airflow-installation-on-ubuntu-18-04-18-10/) and seeing error

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: Ident authentication failed for user "airflow"

My settings on the machine are...

[airflow@airflowetl airflow]$ psql airflow
psql (9.2.24)
Type "help" for help.

airflow=> \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 airflow   |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

airflow-> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 airflow   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | airflow=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

airflow=> \c airflow
You are now connected to database "airflow" as user "airflow".

airflow=> \dt
No relations found.

airflow=> \conninfo
You are connected to database "airflow" as user "airflow" via socket in "/var/run/postgresql" at port "5432".



[root@airflowetl airflow]# cat /var/lib/pgsql/data/pg_hba.conf
....
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
#host    all             all             127.0.0.1/32            ident
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident



[root@airflowetl airflow]# cat /var/lib/pgsql/data/postgresql.conf
....
# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
# CONNECTIONS AND AUTHENTICATION
# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
# — Connection Settings -
#listen_addresses = ‘localhost’ # what IP address(es) to listen on;
listen_addresses = ‘*’ # for Airflow connection



[airflow@airflowetl airflow]$ cat airflow.cfg
....
[core]
....
# The executor class that airflow should use. Choices include
# SequentialExecutor, LocalExecutor, CeleryExecutor, DaskExecutor, KubernetesExecutor
#executor = SequentialExecutor
executor = LocalExecutor

# The SqlAlchemy connection string to the metadata database.
# SqlAlchemy supports many different database engine, more information
# their website
#sql_alchemy_conn = sqlite:////home/airflow/airflow/airflow.db
sql_alchemy_conn = postgresql+psycopg2://airflow:mypassword@localhost:5432/airflow

and not quite sure what could be going wrong here. Using the password from the sql_alchemy_conn string, I am able to do "psql -U airflow --password" and login successfully, so not sure what the auth faiure is for.

One odd thing I notice is that the pg_hba.conf line has:

# IPv4 local connections:
#host    all             all             127.0.0.1/32            ident
host    all             all             0.0.0.0/0               trust

yet it appears that postgres still trying to use ident authentication (despite my having service postgresql restart multiple times at this point).

Anyone have any further debugging suggestions or can see the error here?

Answer 1

You seem to be matching against

host    all             all             ::1/128                 ident

If you are not using IPv6, it's best to just comment out that line and try again

Answer 2

Im comparing with my local one and one of the differences is the owner of my airflow db is the user "airflow" in your case is "postgres". Please run this command: ALTER DATABASE airflow OWNER TO airflow ;

Regards xavy