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?