Alvaro Herrera <alvherre@surnet.cl> writes:
> No, that's why I haven't mentioned it :-) I know I can't do anything
> about objects in other databases. But I have wondered if we could
> implement CASCADE behavior for an object whose dependencies are only
> local to the current database (where CASCADE actually mean delete
> references in ACLs, and reject the action altogether if there is an
> ownership reference. And of course, if the object is a tablespace,
> reject as well.)
Well, I think ACL references could be auto-dropped (ie, even in RESTRICT
mode); and group memberships too. It seems to me that those do not
amount to independent objects that need protection. What I'd like
CASCADE to mean is "drop owned objects".
I agree it makes sense to restrict the owned database and owned
tablespace cases even in CASCADE mode, although that's more of a
judgement call than a necessary conclusion.
So what we've got is:
1. Reject if any references to user from within other databases
(implementation restriction).
2. Reject if user owns any databases or tablespaces (safety feature).
3. If CASCADE mode, drop any owned objects within the current database;
if RESTRICT mode, reject if there are owned objects within the current
database.
4. Auto-drop any remaining references (ACLs and group memberships).
5. Drop the user itself.
The main problem I see with this is that if you do have a user you want
to get rid of who owns objects in multiple databases, it's still mighty
hard to do it. It'd be nice to have some kind of command that either
drops or reassigns ownership of everything the user has in the current
database. Then you could use that repeatedly until you'd reached a
point where DROP USER would work.
regards, tom lane