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@pg.newtest:/# psql 'host=pg.oldtest port=5432 user=pg_replication
dbname=oldtest'
oldtest=# \q

root@pg.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

On Wed, Aug 31, 2022 at 4:07 PM hubert depesz lubaczewski
<depesz@depesz.com> wrote:

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.

Hi, depesz,

I use the dockerized version of Postgres (both old and new cases).

The docker-compose.yml file maps the .pgass file as volume (-
./v_pgpass:/root/.pgpass).

Inside the container the home is /root:
sudo docker exec -ti db bash
root@pg.newtest:/# cd ~
root@pg.newtest:~# pwd
/root
root@pg.newtest:~# ls -la .pgpass
-rw------- 1 postgres root 74 Aug 30 11:38 .pgpass

And as said, the psql utility has no problems finding the .pgass where
it is. If I lie to it about the pgpass location i.e by giving
passfile=/root/.pgpassx it will ask for password.

Why create subscription does not pick the .pgpass up like psql does, -
that is what I cannot figure out.

Cheers!
--
Kristjan Mustkivi

Email: kristjan.mustkivi@gmail.com

On Wed, Aug 31, 2022 at 04:26:22PM +0300, Kristjan Mustkivi wrote:

And as said, the psql utility has no problems finding the .pgass where
it is. If I lie to it about the pgpass location i.e by giving
passfile=/root/.pgpassx it will ask for password.

of course it doesn't have problem, because you run it as root.
put subscription is run by pg backend, which runs as postgres user, not
root!

also - no pg tool cares about "passfile" env variable, so it is just
ignored.

Best regards,

depesz

On Wed, Aug 31, 2022 at 4:27 PM hubert depesz lubaczewski
<depesz@depesz.com> wrote:

On Wed, Aug 31, 2022 at 04:26:22PM +0300, Kristjan Mustkivi wrote:

And as said, the psql utility has no problems finding the .pgass where
it is. If I lie to it about the pgpass location i.e by giving
passfile=/root/.pgpassx it will ask for password.

of course it doesn't have problem, because you run it as root.
put subscription is run by pg backend, which runs as postgres user, not
root!

also - no pg tool cares about "passfile" env variable, so it is just
ignored.

Thank you, depesz!

After I mapped the volume as (-
./v_pgpass:/var/lib/postgresql/.pgpass) CREATE SUBSCRIPTION was able to find it.

Somehow my initial POC fooled me then I kept barking under the wrong tree.

Best Regards,
--
Kristjan Mustkivi

Email: kristjan.mustkivi@gmail.com