Thread

1. CREATE SUBSCRIPTION not picking up .pgpass while psql does

   Kristjan Mustkivi <sonicmonkey@gmail.com> — 2022-08-31T13:03:31Z

   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
   
   
   
   

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

   hubert depesz lubaczewski <depesz@depesz.com> — 2022-08-31T13:07:20Z

   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
   
   
   
   

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

   Kristjan Mustkivi <sonicmonkey@gmail.com> — 2022-08-31T13:26:22Z

   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
   
   
   
   

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

   hubert depesz lubaczewski <depesz@depesz.com> — 2022-08-31T13:27:58Z

   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
   
   
   
   
   

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

   Kristjan Mustkivi <sonicmonkey@gmail.com> — 2022-08-31T13:39:02Z

   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