Some time ago I wrote:
> I've been trying to figure out what to do about pg_dumpall's --clean
> option in view of our recent changes.
(for the rest, see
http://archives.postgresql.org/pgsql-hackers/2005-07/msg01143.php
Since we haven't come up with any bright ideas, and it seems far too
late in the cycle to invent new features like "revoke <role> from *",
I propose that we just punt and make "pg_dumpall --clean" do the
straightforward thing of emitting a "DROP ROLE" command for each role
(user or group) it intends to re-create.
This eliminates the previous behavior of attempting to drop every user
and group in the destination installation. I'm of the opinion that
that's a good thing; there was no such "scorched earth" policy for other
object types, and indeed one would wonder what's the point of having a
--clean option to do that. You might as well just initdb before loading
the dump file.
There will not be a need to special-case the superuser, either, since
any attempt to drop the owner of template0 will of course fail.
The principal problem with this approach is that since we lack "DROP
ROLE CASCADE", the drops are entirely likely to fail (they will fail
if the roles hold any privileges or own any objects). This makes the
whole feature rather pointless. However, that's a problem we will not
be able to solve in the 8.1 time frame, so I propose we live with it.
It's not clear to me that pg_dumpall --clean is a sufficiently widely
used feature to be worth agonizing over.
regards, tom lane