Virtual users in sshd from a postgres database

Question

I have a Postgresql database full of user accounts, and i would like to allow these user to access a server through ssh, using only public keys authentification.

So far, i have setup these parts on an Ubuntu Server:

1. libnss-pgsql2 to connect NSS to several database views listing my users in a Unix compatible format
2. libpam-pgsql to allow PAM authentification using these same views
3. sshd AuthorizedKeysCommand with a script that authenticates users with their public key (still from the postgresql database).

Is there a simpler way to go around this problem ? I have issues setting up correctly the nss configuration (lack of documentation & logs).

Thanks for your time & help.

Answer 1

I would dump your users from DB into LDAP and load it into local LDAP. You can automate updates easily. This would make your OS to query users from LDAP and it would be much more portable - libnss-pgsql2 is not on every UNIX-like system, if you would ever like to move out from Linux. You never know ;)

Answer 2

you can use ssh tunneling to allow user to connect to your database. such as

ssh -L local_port:IP address/hostname:server_port user@IP address/hostname.

here user will be OS user so it best to provide postgres superuser to connect.

But keep in mind that the postgresql database server must be on that server.

once done u can connect using simple psql command as

psql -h hostname/IP address -p port -U user -d database