Postgresql – postgres and passwordless authentication

Sounds like no fuss to me:

• Create an OS account for each user, preferably with simple ssh key based authentication;

• in pg_hba.conf use peer for local connections;

• Optionally also use ident for host connections from 127.0.0.1 and run identd on the server. Not strictly required.

• Set a password on their role for use with md5 authentication, then use md5 authentication for connections from allowed remote hosts with the hostssl method.

That way they'll be able to connect over SSL directly with a password, and locally (after ssh'ing in) with no password prompt.

What you describe:

My ideal connection method would have PostgreSQL ask the OS on which it is running to ask for a username and password, no matter where the client software is running.

sounds a bit like remote ident. This exists and works, but it's completely and utterly insecure. It was written for the days when "my personal computer" would elicit gales of laughter and nobody but nobody had root. These days anyone can set up a laptop (or phone) to claim to be any user they want over ident, so it's completely useless except on the local loopback where you control it.

It does sound like Kerberos is closer to what you want, but honestly it's going to be way more messing around than seems worthwhile. I'd just use peer for local connections and md5 for remote ones.

This line:

 host    all             all             192.168.0.0/24          md5

will let through connections from IPs matching 192.168.0.X where X is any byte.

The IP address of your error message being 192.168.108.161, it does not match this pattern because 108 is not 0.

To enable addresses like 192.168.X.Y, you'd need a /16 instead of /24 meaning that the first 16 bits only are fixed.

Like this:

 host    all             all             192.168.0.0/16          md5

Don't forget to reload Postgresql. From the official docs, use pg_ctl reload. If that doesn't work, then there are other ways to do it listed in this question.