# Postgresql\_query won't run with 'unable to connect to database'

**URL:** <https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602>\
**Category:** Get Help\
**Created:** [May 12, 2025, 1:34pm UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602 "2025-05-12T13:34:02Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\
**Post date:** [May 12, 2025, 1:34pm UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/1 "2025-05-12T13:34:02Z")

</div>

I have this TASK (reloading a postgres database with SQL) that I am sending to a couple of postgres servers.

```auto
    - name: reload postgres
      community.postgresql.postgresql_query:
        login_unix_socket: /tmp
        db: postgres
        query: "SELECT pg_reload_conf()"
      become: true
      become_user: postgres

```

about 40% of them come back with ` "unable to connect to database: fe_sendauth: no password supplied` error.

the postgres user get’s its password locally from a `~/.pgpass` file and running that SQL locally works.

Any idea, what the rootcause here might be?

---

<div class="post-metadata">

**Author:** ![Lyle\_McKarns](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/lyle_mckarns/32/8142_2.png) [@Lyle\_McKarns](https://forum.ansible.com/u/Lyle_McKarns)\
**Post date:** [May 12, 2025, 2:32pm UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/2 "2025-05-12T14:32:52Z")

</div>

Just to confirm, ansible is logging in with a user (such as `ansible`) and then using `become` to operate as the `postgres` user?

I can’t jump right to a root cause, but some thoughts

- Are the 40% of failing servers always the same ones?
- Have you confirmed the existence/permissions of `~/.pgpass` on a working/not working system?

---

<div class="post-metadata">

**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\
**Post date:** [May 12, 2025, 5:55pm UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/3 "2025-05-12T17:55:51Z")

</div>

> [@Lyle\_McKarns](#):
>
> ansible is logging in with a user (such as `ansible`) and then using `become` to operate as the `postgres` user?

yes, precisely

> [@](#):
>
> _Are the 40% of failing servers always the same ones?_

yes, always the same one.

> [@](#):
>
> _confirmed the **existence/permissions of ~/.pgpass** on a working/not working system?_

I took one as example.

1. `~/.pgpass` works fine locally
2. even if I set localhost connections to `trust` in pg\_hba.conf on that exact server I get the same `"unable to connect to database: fe_sendauth: no password supplied` error from ansible

* * *

my current workaround is to got through `shell` and `pg_ctl -D $PGDATA reload` as the postgres user.

---

<div class="post-metadata">

**Author:** ![Lyle\_McKarns](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/lyle_mckarns/32/8142_2.png) [@Lyle\_McKarns](https://forum.ansible.com/u/Lyle_McKarns)\
**Post date:** [May 12, 2025, 6:19pm UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/4 "2025-05-12T18:19:55Z")

</div>

> [@dulhaver](#):
>
> `community.postgresql`

Two other things I’d check

- run the ansible tasks with with `-v` (anywhere from one to 5 `v`’s)
- check out system logs to see if there’s anything indicating what might be happening.

---

<div class="post-metadata">

**Author:** ![wayt](https://avatars.discourse-cdn.com/v4/letter/w/3be4f8/32.png) [@wayt](https://forum.ansible.com/u/wayt)\
**Post date:** [May 13, 2025, 1:41am UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/5 "2025-05-13T01:41:51Z")

</div>

Any chance these are servers running SELinux and you’re hitting a labeling issue on those hosts?

---

<div class="post-metadata">

**Author:** ![ads](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/ads/32/747_2.png) [@ads](https://forum.ansible.com/u/ads)\
**Post date:** [May 13, 2025, 2:48am UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/6 "2025-05-13T02:48:35Z")

</div>

Is the socket directory (/tmp) correct?

Is the database running before you run this command? Try a simple “SELECT 1” and see if that produces the same error.

---

<div class="post-metadata">

**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\
**Post date:** [May 14, 2025, 11:24am UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/7 "2025-05-14T11:24:01Z")

</div>

1. yes the socket /tmp is correct
2. a `SELECT 1` query fails on the same machines with the same error

> .. SELinux … you’re hitting a labeling issue on those hosts?

I compared a failing and a working hosts. Context (as per `ls -laZ`) is identical.

---

<div class="post-metadata">

**Author:** ![ads](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/ads/32/747_2.png) [@ads](https://forum.ansible.com/u/ads)\
**Post date:** [May 14, 2025, 2:27pm UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/8 "2025-05-14T14:27:01Z")

</div>

> [@dulhaver](#):
>
> a `SELECT 1` query fails on the same machines with the same error

This then is an authentication problem with your database.

Running queries as `postgres` user should always be allowed in `pg_hba.conf`.

---

<div class="post-metadata">

**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\
**Post date:** [July 15, 2025, 8:12am UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/9 "2025-07-15T08:12:21Z")

</div>

> [@](#):
>
> authentication problem with your database.  
> Running queries as `postgres` user should always be allowed in `pg_hba.conf`.

sorry, it took a while until I got the chance to dig into this a little deeper.

**postgres via ansible - ERROR**

```auto
> ansible -i ~/repos/ansible-inventory-eakte/ 'dbserver' -l rd5912 -m postgresql_query -a "login_unix_socket='/tmp' query='SHOW port'" --become --become-user postgres

[WARNING]: Invalid characters were found in group names but not replaced, use -vvvv to see details
[WARNING]: Database name has not been passed, used default database to connect to.
rd5912 | FAILED! => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python3"
    },
    "changed": false,
    "msg": "unable to connect to database: fe_sendauth: no password supplied\n"
}

```

Other targets in the same supposedly state work. Also these tests of connectivity to postgres on the server(s) that show the postgres via ansible issue are successful.

**postgres via ssh - WORKS** \*

```auto
> ssh rd5912 'sudo -u postgres /opt/db/postgres/postgresql/bin/psql -qAtc "SHOW port ;"'
could not change directory to "/home/username": Permission denied
5432

```

**.pgpass - EXISTS**

```auto
> ssh rd5912 'sudo -u postgres cat /home/postgres/.pgpass'
localhost:5432:*:postgres: ********************

```

**psycopg2 - EXISTS**

```auto
> ssh rd5912 'dnf list python3-psycopg2'
...
...
...
Installed Packages
python3-psycopg2.x86_64 2.8.6-6.el9 @rhel-9-for-x86_64-appstream-rpms

```

I am out of ideas. What else can I check/fix locally in order to make this work?

---

<div class="post-metadata">

**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\
**Post date:** [July 15, 2025, 12:00pm UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/10 "2025-07-15T12:00:40Z")

</div>

> [@wayt](#):
>
> Any chance these are servers running SELinux and you’re hitting a labeling issue on those hosts?

nice guess, but I compared the folder with the binaries as well as the content of data\_directory between a failing and a working host. The SELinux context is identical, so it’s apparently not that.

---

<div class="post-metadata">

**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\
**Post date:** [July 15, 2025, 12:37pm UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/11 "2025-07-15T12:37:12Z")

</div>

> [@ads](#):
>
> Running queries as `postgres` user should always be allowed in `pg_hba.conf`.

I did not exaclty realize what that actually meant.  
with such a line in pg\_hba.conf everything is good

`local all all 127.0.0.1/32 trust`

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/ansible/original/1X/1882d9a01da5d8996a862359b0913cc568a39b84.svg) [@system](https://forum.ansible.com/u/system)\
**Post date:** [August 14, 2025, 12:37pm UTC](https://forum.ansible.com/t/postgresql-query-wont-run-with-unable-to-connect-to-database/42602/12 "2025-08-14T12:37:47Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.