Re: dropped users appear as numbers in ACL

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> I'm not sure there's any really good answer to this short of changing
>> the way that users and databases work, which so far no one has wanted
>> to do.

> One possibility would be to have an explicit "flush privileges" command
> that you can run over a database to clean up after this. That might also
> help to support grant options on groups, which suffer from a similar
> can't-look-into-other-databases problem.

One thing that would go a long way towards alleviating the problem is to
get rid of user-selectable sysids for users and groups, and instead assign
the sysids from a cluster-wide sequence (or equivalently, revert to
using OIDs as user/group identifiers). Then dead privileges wouldn't
come back to life as belonging to some newly-created user. You could
imagine making vacuum delete ACL entries for no-longer-known users,
or put it into a separate cleanup operation as Peter suggests.

However, that has its own downside. IIRC, the reason we haven't done
this already is that if you unintentionally drop a user who still owns
objects, it's nice to be able to resurrect him with the same sysid.
The cross-database problem makes it hard to prevent such mistakes with
dependencies.

Maybe rather than a flat prohibition of WITH SYSID, just change the
default assignment to be from a sequence rather than the error-prone
MAX()+1 behavior?

regards, tom lane