People,
Here is the patch for DROP OWNED (finally!). This patch implements two
new commands, DROP OWNED and REASSIGN OWNED BY.
DROP OWNED drops the objects owned by any of a list of roles (in the
current database, of course). It also revokes all privileges that have
been granted to any of them. One must have privileges of all the
mentioned roles in order to be able to do this. (So a superuser can do
it for any role, and in the simple case a role can do it only to
itself.)
REASSIGN OWNED gives the objects away to some other role. It doesn't
touch grants. So if a role has been granted something and you want to
drop it but keep the objects, you must do REASSIGN OWNED and then DROP
OWNED. One must have all privileges of all the mentioned roles in
order to do this, including the receiving role.
The idea of all this is that if you want to drop a role, you first issue
a DROP ROLE, note all the databases on which it says it has dependences,
connect to each and issue REASSIGN OWNED and/or DROP OWNED as
appropiate, and finally issue DROP ROLE again. This eases dropping a
role (or that is the theory anyway).
The patch is missing regression tests. I will include them when I apply
it. I intend to apply it tomorrow or so, unless somebody has (strong?
Is the time when people "strongly objected" to things gone?) objections
to it.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org
"La rebeldía es la virtud original del hombre" (Arthur Schopenhauer)