Restrict access to new database by default

Question

I am administering a Postgres server for students on a very basic database course. Students get there own accounts, and are allowed to create databases. The problem: when a student creates a database, the default is that another student can connect to it, and create their own tables in it. (They can't select or insert on each other's tables, however.)

They can prevent this themselves with:

revoke all privileges on database the_database_i_just_created from public;

But what I'd like is for that behavior to be default, so that they don't have to remember to issue the revoke command. There doesn't seem to be anything in alter default privileges that does this. Is there any way to accomplish what I want? It doesn't have to be exactly this, just something that prevents users to mess with each other's databases by default.

I have complete control over the server, so changing configuration files or whatever is fine. It's also fine if I have to issue one command for each user – I can do that in the script that creates the students' accounts.

Answer 1

I think you cannot do this using only the tools PostgreSQL offers.

On one hand, there are no default privileges, as you noticed. The REVOKE command issued on a template DB (template1 in most cases) has no effect on the created DB. Last, the event trigger mechanism does not support shared objects (including databases).

I think the best way would be to create one DB for them each, and confine them to that one from wherever you create the users currently.