Re: Generate user/group sysids from a sequence?

Tom Lane wrote:
> Currently, the default sysid assigned to a user or group is computed as
> "max(sysid)+1". We've seen a couple of complaints now from people who
> deleted their newest user, made another user, and found that permissions
> from the deleted user carried over to the new one.
>
> It seems to me that the easiest solution to this is to generate the
> default sysid from a sequence object, instead. Unless someone
> deliberately resets the sequence, there'd be no conflicts.
>
> A small difficulty is that explicitly-specified sysids could conflict
> with sysids generated later by the sequence. We could perhaps fix this
> by forcing up the sequence setting to be at least as large as an
> explicitly-given ID (compare the handling of explicitly loaded OIDs).

A sequence sounds like a good idea. When we create a user, we can use
MAX() to find the maximum, and if that is less than the sequence value,
bump up the sequence to equal max and add the row, again incrementing
the sequence. Another idea would be to put a trigger on the column so
that any INSERT/UPDATE would automatically bump up the sequence with
setval().

The reason I was being cautious is to handle cases where people are
poking in pg_shadow directly.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073