Ssh – Virtual users in sshd from a postgres database

nsspampostgresqlssh

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:

libnss-pgsql2 to connect NSS to several database views listing my users in a Unix compatible format
libpam-pgsql to allow PAM authentification using these same views
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.

Best Answer

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

Related Solutions

Mysql – Postgres equivalent to MySQL’s \G

I'm not familiar enough with MySQL to know what the \G option does, but based on the documentation it looks like the psql \x option might do what you want.

It's a toggle, though, so you do it before you submit the query.

\x
select * from sometable;

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Best Answer

Related Solutions

Mysql – Postgres equivalent to MySQL’s \G

PostgreSQL – Default Superuser Username/Password After New Install

Related Topic