When I'm developing against a PostgreSQL database I often drop and re-create it and I often find that the drop fails, because it's "in use by other users". This is really annoying, especially when I know full well there are no other users - it's just me.
I'm aware of the workaround: use pg_terminate_backend to kill existing connections, but there are two problems with that. Firstly, it's just a hassle, for something that should be a very simple operation. (I'm not even writing SQL for it normally, just pressing Delete in pgAdmin.) Secondly, pg_terminate_backend requires superuser rights. If I'm not a superuser, but I am the owner of the database, it doesn't seem right that another user should be able to prevent me from dropping my database.
I'd really like to see PostgreSQL directly support dropping a database, regardless of who is using it - something like "DROP DATABASE ... CASCADE". (Although "CASCADE" wouldn't be the appropriate word here. Maybe "DROP DATABASE ... TO_HELL_WITH_USERS"?)