I have been using FreeBSD running an a virtual machine at DigitalOcean.com.
I use Secure Shell (SSH) from my Mac to remote into a console session on the VM. I used ssh-keygen to create the pair of encryption keys.
I deployed the public one copied to the FreeBSD SSH server. I did so via a back-door web-page supplied by Digital Ocean, pasting the text contents of the public key to a text field in a form.
That works well, logging me in for both root and freebsd users present by default on my new FreeBSD VM. When I connect via either the root or freebsd user accounts via ssh, I am prompted to provide the passphrase protecting my local private key.
ssh [email protected]
…or…
ssh [email protected]
Then I installed the Postgres database system onto this FreeBSD server. As part of the scripts used to build and install Postgres, a third user account is added to the FreeBSD machine. By convention, the new account is named postgres. I gave that user a password using the passwd utility.
When I connect to the FreeBSD server as that user:
ssh [email protected]
…I am prompted for that postgres user’s own password. I am not prompted for the password protecting my local private encryption key.
➥ Why is the postgres user treated differently than root & freebsd with regard to using the public/private SSH keys to log me in?
FYI, I never touched any authorized_keys file on the remote SSH server (my FreeBSD VM).
My Question here may be a duplicate of Public SSH keys not working for all users on same server, I’m not sure.
Add your public key to the postgresql users authorized keys file. This doesn’t happen during the init phase of your instance like with the root and freebsd user (cloud providers use scripts that do things like deploying ssh keys to a set of predefined users). This can be the same as the freebsd public key or you can use a different keypair for that purpose.