Ssh – How to allow the user “postgres” on one server to rsync to another

rsyncssh

I'm trying to get this command to work as the user postgres (so I can ship wal files):

rsync -a /tmp/test postgres@server2:/tmp/test

But I get the error:

Permission denied (publickey).

I've run ssh-keygen eval `ssh-agent` and ssh-add as postgres user on server1. keygen created /var/lib/postgresql/.ssh/id_rsa and id_rsa.pub and I can see that it's sent by using ssh -vvv postgres@server2.

On server2 I've created /var/lib/postgresql/.ssh/authorized_keys put the contents of id_rsa.pub form server1 in it. It's owned by postgres user and group and chmod 600. The .ssh directory is also owned by postgres and chmod 700.

I can see from verbose sshd logging on server2 that Failed publickey for postgres...

postgres user on both servers: postgres:x:106:114:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

ssh -vvv postgres@server2

...
debug1: Found key in /var/lib/postgresql/.ssh/known_hosts:1
debug1: ssh_ecdsa_verify: signature correct
debug2: kex_derive_keys
debug2: set_newkeys: mode 1
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug2: set_newkeys: mode 0
debug1: SSH2_MSG_NEWKEYS received
debug1: Roaming not allowed by server
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug2: service_accept: ssh-userauth
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug2: key: /var/lib/postgresql/.ssh/id_rsa (0x7f468e434000)
debug2: key: /var/lib/postgresql/.ssh/id_dsa ((nil))
debug2: key: /var/lib/postgresql/.ssh/id_ecdsa ((nil))
debug1: Authentications that can continue: publickey
debug3: start over, passed a different list publickey
debug3: preferred gssapi-keyex,gssapi-with-mic,publickey,keyboard-interactive,password
debug3: authmethod_lookup publickey
debug3: remaining preferred: keyboard-interactive,password
debug3: authmethod_is_enabled publickey
debug1: Next authentication method: publickey
debug1: Offering RSA public key: /var/lib/postgresql/.ssh/id_rsa
debug3: send_pubkey_test
debug2: we sent a publickey packet, wait for reply
debug1: Authentications that can continue: publickey
debug1: Trying private key: /var/lib/postgresql/.ssh/id_dsa
debug3: no such identity: /var/lib/postgresql/.ssh/id_dsa
debug1: Trying private key: /var/lib/postgresql/.ssh/id_ecdsa
debug3: no such identity: /var/lib/postgresql/.ssh/id_ecdsa
debug2: we did not send a packet, disable method
debug1: No more authentication methods to try.
Permission denied (publickey).

server2 sshd_config (commented lines removed)

Port 22
Protocol 2
HostKey /etc/ssh/ssh_host_rsa_key
HostKey /etc/ssh/ssh_host_dsa_key
HostKey /etc/ssh/ssh_host_ecdsa_key
UsePrivilegeSeparation yes
KeyRegenerationInterval 3600
ServerKeyBits 768
SyslogFacility AUTH
LogLevel VERBOSE
LoginGraceTime 120
PermitRootLogin yes
StrictModes yes
RSAAuthentication yes
PubkeyAuthentication yes
IgnoreRhosts yes
RhostsRSAAuthentication no
HostbasedAuthentication no
PermitEmptyPasswords no
ChallengeResponseAuthentication no
PasswordAuthentication no
X11Forwarding yes
X11DisplayOffset 10
PrintMotd no
PrintLastLog yes
TCPKeepAlive yes
AcceptEnv LANG LC_*
Subsystem sftp /usr/lib/openssh/sftp-server
UsePAM yes

server2 auth log

Jan 16 03:54:21 ip-10-28-26-251 sshd[7972]: Set /proc/self/oom_score_adj to 0
Jan 16 03:54:21 ip-10-28-26-251 sshd[7972]: Connection from 10.28.123.97 port 49377
Jan 16 03:54:21 ip-10-28-26-251 sshd[7972]: Failed publickey for postgres from 10.28.123.97 port 49377 ssh2
Jan 16 03:54:21 ip-10-28-26-251 sshd[7972]: Connection closed by 10.28.123.97 [preauth]

What am I missing? I'm guessing that sshd isn't looking at my authorized_keys file on server2

Best Answer

Assuming your slave server allows key authentication, you only need to update /etc/ssh/sshd_config if you've set AllowedUsers, in which case you need to ensure postgres is in that list.

Other than that, just ssh-keygen (leave private key passphrase empty), and then add an ~/.ssh/authorized_keys directory/file to the slave server. The home directory for postgres is /var/lib/postgresql, but if you do these operations while su'd as the postgres user, you can just use ~, not to mention you won't have to chown anything, because postgres will own the generated ssh keys on the master server, and postgres will own the created directory/file on the slave server.

Be sure to set the file permissions securely on both the master and slave server:

# On master
chmod 700 ~/.ssh
chmod 600 ~/.ssh/id_rsa
chmod 600 ~/.ssh/id_rsa.pub
chmod 600 ~/.ssh/known_hosts  # this one won't exist until you SSH once

# On slave
chmod 700 ~/.ssh
chmod 600 ~/.ssh/authorized_keys
Related Topic