[SOLVED] Access to pgsql DB denied after IP change

[bougatoyta]

Hi,

I've set up a PMG proxy on temp IP, then I moved the cluster to the proper IP, for which I needed to delete and re-create the cluster.

I've removed nodes from the master, deleted all cluster.conf files, redo the iptables and now I get this error :

Received request to connect to path /var/run/postgresql/.s.PGSQL.5432, but the request was denied.

Which make the cluster always in sync state.

I don't know what is the problem, I tried to set sshd with allowRoot Yes and prohibit-password, no change.

And I get this error in syslog :

Jun 19 16:30:53 mx-p-01 pmgmirror[1331]: starting cluster synchronization
Jun 19 16:30:53 mx-p-01 pmgmirror[1331]: database sync 'mx-p-02' failed - DBI connect('dbname=Proxmox_ruledb;host=/run/pmgtunnel;port=2;','root',...) failed: la connexion au serveur a été coupée de façon inattendue
    Le serveur s'est peut-être arrêté anormalement avant ou durant le
    traitement de la requête. at /usr/share/perl5/PMG/DBTools.pm line 66.
Jun 19 16:30:53 mx-p-01 pmgmirror[1331]: cluster synchronization finished  (1 errors, 0.01 seconds (files 0.00, database 0.01, config 0.00))

Is there any tweak to do to the postgres config ?

I'm thinking to backup the master and redo everything right now...

 

[Stoiko Ivanov]

How did you recreate the cluster after changing IPs?

My guess is that the IP change resulted in the ssh-connection to not be made (because the new IP has not yet been added to the known_hosts) ...
try (as root) to ssh to all your cluster nodes from all other cluster nodes.

else check the status of the pmgtunnel service

For now it does not look as if this is related to the Postgresql configuration

 

[bougatoyta]

So I've rebuild both server and imported the backup of the master :

mv pmg-backup_2023_06_19_64906A94.tgz /var/lib/pmg/backup/
# I restored from GUI
sudo nano /etc/ssh/sshd_config => Allow root access with password
systemctl restart sshd
# This block was not working because I still had the old cluster.conf file
# pmgcm create
# pmgcm status
# pmgcm join-cmd
# pmgcm update-fingerprints => Fingerprint denied
# pmgconfig apicert --force 1 => This show me the correct fingerprint (same as join-cmd)

rm /etc/pmg/cluster.conf

# This block gave me the proper fingerprint
pmgcm create
pmgcm join-cmd
openssl x509 -in /etc/pmg/pmg-api.pem -fingerprint -noout -sha256
reboot => Had to reboot both master and secondary node to avoid fingerprint error
pmgcm status => Nodes syncing
pmgtunnel status => Running

I still get theses errors :

Jun 20 15:56:35 localhost sshd[743]: Received request to connect to path /var/run/postgresql/.s.PGSQL.5432, but the request was denied.
Jun 20 15:58:35 localhost sshd[743]: Received request to connect to path /var/run/postgresql/.s.PGSQL.5432, but the request was denied.
Jun 20 16:00:35 localhost sshd[743]: Received request to connect to path /var/run/postgresql/.s.PGSQL.5432, but the request was denied.

 

[Stoiko Ivanov]

Do you have any specific modifications in the sshd config maybe?
or in the authorized_keys?

 

[bougatoyta]

Pretty normal sshd_config :

Include /etc/ssh/sshd_config.d/*.conf
LogLevel INFO
LoginGraceTime 60
PermitRootLogin yes
MaxAuthTries 4
HostbasedAuthentication no
IgnoreRhosts yes
PermitEmptyPasswords no
ChallengeResponseAuthentication no
UsePAM yes
AllowTcpForwarding no
X11Forwarding no
PrintMotd no
PermitUserEnvironment no
ClientAliveInterval 300
ClientAliveCountMax 3
AcceptEnv LANG LC_*
Subsystem       sftp    /usr/lib/openssh/sftp-server
Ciphers chacha20-poly1305@openssh.com,aes256-gcm@openssh.com,aes128-gcm@openssh.com,aes256-ctr,aes192-ctr,aes128-ctr
MACs hmac-sha2-512-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512,hmac-sha2-256
KexAlgorithms curve25519-sha256,curve25519-sha256@libssh.org,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512,ecdh-sha2-nistp521,ecdh-sha2-nistp384,ecdh-sha2-nistp256,diffie-hellman-group-exchange-sha256
AllowGroups ssh-allowed root

Authorized key of root user only has local pub key and master pub key :

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCmn2ip5XeCYQqaKSoQxvegkWG2aKT9298FGgTfhmlH4TMY57kacu7poS8egtwTpLQkcGjmbfbidPi+I/WiYxWlcrDNsOgO2cVObe5dBQaJ73v8bFxoqOVcvXcosbfcvwmrT/EphXg8OUg+MWlMbOKkwZ3wDER4cOpuIDxXmqAu20mptQ3Is71aBsY+YPsmvG358FC9PjLcg9GlWi5Zh7NOkAUuuU4IvTbvrx4qs+f8v9+GlEmt7nD5lstJl6Hu9ynd6tYKDE2pZKdvEDsntuqZkBtvc1t9/Anar0zEv+BE2MI4B9qrk5YfubXE6/FAAgr6fxxm+Tvz/a4hzQyTUvrD root@mx-p-02
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQClniyKUKfmsqlm4zaVFXtfFvOKwYnd/ncEfy7OhaeMQmRo89hmWwl3Mn5rrZiNlPy/DeR+Vmhoic8ESi9a/+l57X/68PFiJi1SYIOtWGjoZPvP9KgXIkuAx81wynk+niHOgosXoXgHlJcU7Mw0lbPggC+tFS7l0tZ51k6esHZ4UxlgWY8pbSJOxCDCHTbQNSelbj+uObtK4BXzXi8Ote8TS+QslRj8EPqfIHWHLpKzzYr04AB29VyRQ+T7DzngJfbZ9R9+nJ0mYQKvi4IV2AkPj3l9YP1Llkgq6bs4YwQN3PNEcMWuJD9Qhq7QbRzi9k8yAnvzCyAaro5lQ22CoMzr root@mx-p-01

I can connect from master to node and node to master with the root account without password

 

[Stoiko Ivanov]

AllowTcpForwarding no

I guess this is the issue ...

pmgtunnel forwards connections to the postgresql database over ssh...

 

[bougatoyta]

I guess this is the issue ...

pmgtunnel forwards connections to the postgresql database over ssh...

Nice catch it was that.

Might be worth to add in the documentation, we have it on 'No' by default to comply to some CIS rules there might be other people in the same boat

Anyway, has always thanks for your time and help !

 

[Stoiko Ivanov]

Glad we found the issue!

Might be worth to add in the documentation,

Hm - not quite sure if this is warranted -
a) it is documented (quite briefly and maybe not where you expect):
https://pmg.proxmox.com/pmg-docs/pmg-admin-guide.html#_pmgtunnel_cluster_tunnel_daemon

b) documenting all potentially problematic changes from the default config that is shipped in the packages is not really possible ...
(even if we could summarize the needed config-settings for the services in one version - this will most likely become outdated quite fast, and it could never cover all potential problems with additionally installed packages..)