Postgresql – Choosing A PostgreSQL Authentication Method For A Large Course

authenticationpostgresql

I am teaching a first course in databases for the first time. Students will need to have a database management system to which they can connect to do much of their work for the course. I have chosen to use PostgreSQL (running on a GNU/Linux-based VPS), since I am familiar with it from my own personal projects. But I have never needed to administer a server with more than one user, so I want to make sure that I am making wise decisions before setting things into stone.

I would like students to be able to do the following, and (of course) have their accounts reasonably secure from attack:

  • Use psql on the same machine where the database server runs.
  • Use pgAdmin on their local (probably Windows-based) machine.
  • Write JDBC applications that run from their local machine.
  • Write JDBC webapps that run through apache on the same machine where the database server runs.

There are many authentication methods available (see http://www.postgresql.org/docs/9.2/static/auth-methods.html), but none seem to meet all of my requirements well.

  • Option A): Each student has their own OS-level user account and a password-less database role to match. Connections are allowed only through the peer mechanism.
  • Option B): Each student has their own OS-level user account and a database role to match that has a password. Connections are allowed only through the md5 mechanism.
  • Option C): Students do not have OS-level user accounts, only database roles with passwords. Connections are allowed only through the md5 mechanism.

Option A is what I have always used myself, and it would be my preference. But I believe that it would rule out accessing the database from any client other than psql running on the same machine.

Option B seems to be the most flexible. But it also seems terribly ugly for students to need to set and maintain passwords in two disparate systems.

Option C would only allow connecting from clients on remote machines, which is not really acceptable.

I am fairly unfamiliar with GSSAPI / Kerberos, but it does not really sound like what I want either. 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. Is there some better option for my requirements than B above?

Best Answer

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.

Related Question