Обсуждение: CREATE SUBSCRIPTION not picking up .pgpass while psql does
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. .pgpass contains: *:*:*:pg_replication:password Any thoughts on how to debug much appreciated. Best regards, -- Kristjan Mustkivi Email: kristjan.mustkivi@gmail.com
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