pg_hba.conf and postgresql.conf experimentation for new server

[ChrisLovering]

Look at what config would be needed

[jchristgit]

It's time to tackle this issue! I think our pg_hba.conf should start by explicitly listing the user and database of the first service we want to test using the new PostgreSQL instance with, and that with all the outbound IPs of our K8s cluster. Something like

# type           db           user            address       method
host         pixels          pixels           LKE-1            scram-sha-256
host         pixels          pixels           LKE-2            scram-sha-256
host         pixels          pixels           LKE-3            scram-sha-256

[jchristgit]

@jb3 could you provide me the outbound address(es) of the LKE nodes please?

[jchristgit]

Chris has provided us this IP listing for the LKE nodes:

ipv4,ipv6
172.105.65.136,192.168.137.183,2a01:7e01::f03c:94ff:fe7d:6afb/128
139.162.171.26,192.168.171.231,2a01:7e01::f03c:94ff:fe7d:99b4/128
139.162.171.39,192.168.143.75,2a01:7e01::f03c:94ff:fe7d:99fd/128

PR #259 introduces the lke_ipv{4,6}_addresses variables to allow easy access and configuration of firewall rules and database access from the LKE nodes.

[jchristgit]

PR #259 has been updated. It now dynamically fetches any possible outbound IPs of our LKE cluster via https://geoip.linode.com/ and whitelists it in the firewall for target port 5432 (PostgreSQL).

I'm not sure if it's reasonable for us to also explicitly whitelist the hosts in pg_hba.conf as well, since other hosts won't be able to connect to it anyways. Would be happy for other opinions here, but I would simply rely on nftables to block things off instead of adding 100 entries to the pg_hba.conf.

[shtlrs]

@jchristgit How about we populate the pg_hba.conf dynamically ? It'll still have 100 entries at the end, it just won't be cluttered statically in our repos.

I know that the firewall should do the trick here, but I don't think that an extra step of explictly listing theses hosts to pg is of any harm to us.

2 lines to break is better than one IMO.

[jchristgit]

@jchristgit How about we populate the pg_hba.conf dynamically ? It'll still have 100 entries at the end, it just won't be cluttered statically in our repos.

I know that the firewall should do the trick here, but I don't think that an extra step of explictly listing theses hosts to pg is of any harm to us.

@shtlrs yeah, that sounds good to me. I think we should comment and section it appropriately to make sure the file is still readable to humans.