Problem appears to be that airflow was trying to use IPv6 to connect to postgres instead of IPv4 (have never done much DBA anything until now). The SO post here helped me understand: stackoverflow.com/a/39373767/8236733.
The guide I was using had localhost in connection string, but postgres apparently uses underlying local machine configs with this kind of string (and mine is apparently set to use IPv6 (did not spin up my current server)) and using 127.0.0.1 forces the use of IPv4. So changing the connection string to sql_alchemy_conn = postgresql+psycopg2://airflow:mypassword@127.0.0.1:5432/airflow solved the problem for me. On Wed, Dec 18, 2019 at 10:04 AM Reed Villanueva <rvillanu...@ucera.org> wrote: > Oddly after commenting out the only line that refers to ident auth method, > the IPv6 line, and running "airflow initdb" again I see... > > File > "/home/airflow/.local/lib/python3.6/site-packages/psycopg2/__init__.py", > line 130, in connect > conn = _connect(dsn, connection_factory=connection_factory, **kwasync) > sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: no > pg_hba.conf entry for host "::1", user "airflow", database "airflow", SSL > off > > Based on the articles that I have been seeing to try to set up postgres > with airflow, I am not sure that this should be happening. Anyone know what > could be going on here or which logs files (and where) could give more > useful information? > > On Tue, Dec 17, 2019 at 3:10 PM Reed Villanueva <rvillanu...@ucera.org> > wrote: > >> No, didn't work. >> No experience with psql until now, but seems odd that it is still trying >> to use ident auth method rather than anything else, yet setting >> >> # IPv6 local connections: >> host all all ::1/128 ident >> host all all 127.0.0.1/32 trust >> >> (the only other uncommented part of the config file that references >> indent) did not seem to change anything either. >> >> On Tue, Dec 17, 2019 at 2:49 PM Kaxil Naik <kaxiln...@gmail.com> wrote: >> >>> Add the following line and try restarting it: >>> >>> host all all 127.0.0.1/32 trust >>> >>> >>> >>> On Wed, Dec 18, 2019 at 12:45 AM Reed Villanueva <rvillanu...@ucera.org> >>> wrote: >>> >>>> 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). >>>> >>>> On Tue, Dec 17, 2019 at 12:28 PM Reed Villanueva <rvillanu...@ucera.org> >>>> wrote: >>>> >>>>> 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. >>>>> >>>>> >>>>> >>>>> [root@airflowetl airflow]# cat /var/lib/pgsql/data/pg_hba.conf >>>>> ....# TYPE DATABASE USER ADDRESS METHOD >>>>> # "local" is for Unix domain socket connections onlylocal 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. >>>>> >>>>> Anyone have any further debugging suggestions or can see the error >>>>> here? >>>>> >>>> >>>> This electronic message is intended only for the named >>>> recipient, and may contain information that is confidential or >>>> privileged. If you are not the intended recipient, you are >>>> hereby notified that any disclosure, copying, distribution or >>>> use of the contents of this message is strictly prohibited. If >>>> you have received this message in error or are not the named >>>> recipient, please notify us immediately by contacting the >>>> sender at the electronic mail address noted above, and delete >>>> and destroy all copies of this message. Thank you. >>>> >>> -- This electronic message is intended only for the named recipient, and may contain information that is confidential or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error or are not the named recipient, please notify us immediately by contacting the sender at the electronic mail address noted above, and delete and destroy all copies of this message. Thank you.
