Re: dropping a user causes pain (#2)

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> Andrew Dunstan wrote:
>> OTOH I'm not sure how much harm this causes, other than aesthetic.
>>
> Dropping a user could merely set a "dropped" flag to disable login, and
> some VACUUM action could cleanup databases.

Not sure I care for the "vacuum" part of that, but how about this
variant: DROP USER sets a flag in pg_shadow to disable login, and
the pg_shadow entry isn't removed, ever. (We could tweak the pg_user
view to hide dropped users, but anything looking directly at pg_shadow
would have to be taught about the flag, analogous to what happened with
attisdropped in the last release.)

The advantage here is that the sysid assigned to the user would remain
present in pg_shadow and couldn't accidentally be assigned to a new
user. This would prevent the problem of new users "inheriting"
permissions and even object ownership from deleted users due to chance
coincidence of sysid.

I suppose one could delete the pg_shadow row once one is darn certain
there is no trace of the user's sysid anywhere, but it's not clear to me
it's worth the trouble.

regards, tom lane