Most probably, one firewall rule needs to be opened to allow this.
12:44 <+olasd> I think it makes sense to open read only access (so, punch a hole in the firewall, and update pg_hba.conf to allow staging hosts to connect as the guest user)
Plan, something along the line of:
Status, indeed, blocked by fw:
tony@yavin4 $ ssh root@192.168.100.129
Password:
Last login: Tue Jan 5 13:46:47 2021 from 192.168.101.126
----------------------------------------------
| Hello, this is OPNsense 20.7 | @@@@@@@@@@@@@@@
...
tcpdump: listening on pflog0, link-type PFLOG (OpenBSD pflog file), capture size 256 bytes
00:00:24.579959 rule 168/0(match): block out on vtnet1: (tos 0x0, ttl 63, id 14173, offset 0, flags [DF], proto TCP (6), length 60)
192.168.130.200.36606 > 192.168.100.103.5432: Flags [S], cksum 0xc8b9 (correct), seq 1106950483, win 64240, options [mss 1460,sackOK,TS val 4280384814 ecr 0,nop,wscale 7], length 0From trying to connect from clearly-defined node to the replica:
ardumont@clearly-defined:~% cat .pg_service.conf
[mirror-swh] # db for reading
dbname=softwareheritage
host=somerset.internal.softwareheritage.org
port=5432
user=guest
...
ardumont@clearly-defined:~% psql service=mirror-swh
psql: error: could not connect to server: Connection refused
Is the server running on host "somerset.internal.softwareheritage.org" (192.168.100.103) and accepting
TCP/IP connections on port 5432?Firewall rule opened:
Now, expectedly, we have the flux which passes with login error:
ardumont@clearly-defined:~% psql service=mirror-swh psql: error: ERROR: login rejected ERROR: login rejected
Now on to adapt pgbouncer configuration through the
/etc/postgresql/13/replica/pg_hba.conf file (btw, typo fix there [1]).
Need to go and adapt pg_bouncer/postgres now [2]:
root@somerset:~# grep 130 /etc/postgresql/13/replica/pg_hba.conf host all guest 192.168.130.0/24 trust
And now:
ardumont@clearly-defined:~% psql service=mirror-swh psql (13.1 (Debian 13.1-1.pgdg100+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. softwareheritage=> \conninfo You are connected to database "softwareheritage" as user "guest" on host "somerset.internal.softwareheritage.org" (address "192.168.100.103") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
[1] https://forge.softwareheritage.org/rSPSITE89a78c9fdbb3bce41548a389863ae27a95d13f1b
[2] pgbouncer uses the pg_hba.conf file from postgresql.
and voila:
tg1999@clearly-defined:~$ psql service=replica-swh psql (13.1 (Debian 13.1-1.pgdg100+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. softwareheritage=> \conninfo You are connected to database "softwareheritage" as user "guest" on host "somerset.internal.softwareheritage.org" (address "192.168.100.103") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) softwareheritage=> \q