Re: CREATE SUBSCRIPTION not picking up .pgpass while psql does

On Wed, Aug 31, 2022 at 04:03:31PM +0300, Kristjan Mustkivi wrote:
> Hello,
>
> I do not understand why CREATE SUBSCRIPTION does not pick up .pgpass
> (when psql does):
>
> root(at)pg(dot)newtest:/# psql 'host=pg.oldtest port=5432 user=pg_replication
> dbname=oldtest'
> oldtest=# \q
>
> root(at)pg(dot)newtest:/# psql newtest postgres
> newtest=# CREATE SUBSCRIPTION sub_pgupgrade CONNECTION
> 'host=pg.oldtest port=5432 user=pg_replication dbname=oldtest'
> PUBLICATION pub_pgupgrade;
> ERROR: could not connect to the publisher: connection to server at
> "pg.oldtest" (x.x.x.x), port 5432 failed: fe_sendauth: no password
> supplied
> newtest=#
>
> oldtest is 11.15 (Debian 11.15-1.pgdg90+1)
> newtest is 14.5 (Debian 14.5-1.pgdg110+1)
>
> .pgpass is under /root (home inside the docker container) with 0600
> permissions and owned by user "postgres". Even providing
> passfile=/root/.pgpass in the connstring does not work.

How/where you provide it?

Why would you assume that postgres (running from user postgres
presumably) would look for pgpass in /root/.pgpass?

postgres should have it in ~postgres/.pgpass
with proper ownership.

depesz