I like the sequence generator idea too.
I know Unix is bad in this area - but that's no reason for us to be bad
too. This is actually one of the (few) areas where Windows is better
than Unix. Let's go for best practice.
(new todo item "Prevent automatic reuse of sysids" ?)
andrew
Tom Lane wrote:
>Gavin Sherry <swm@linuxworld.com.au> writes:
>
>
>>On Tue, 12 Aug 2003, Andrew Dunstan wrote:
>>
>>
>>>Is this a security hole? Looks like one to me. Would it be better to use
>>>a sequence generator for sysids instead of using max+1 on the user
>>>table? Or else store the last sysid used somewhere?
>>>
>>>
>
>
>
>>This issue has been discussed before and it was agreed that since most
>>UNIX systems will behave in the same way, there's no way to know. Also, it
>>is not possible for a given database to know the max(sysid) of pg_user in
>>another database.
>>
>>
>
>You forget that pg_shadow is a shared (cluster-wide) table.
>
>I believe we could make a shared sequence object, too, if we wanted to
>go the sequence route.
>
>Right at the moment I like both ideas: a shared sequence to generate new
>sysids, and don't ever delete pg_shadow rows. One attraction of the
>sequence generator is that scans over pg_shadow could get rather tedious
>if we follow the latter policy. But with a sequence, CREATE USER
>wouldn't need to do a scan.
>
>Something else that should be factored into any redesign of pg_shadow is
>the notion of combining users and groups, at least to the extent of
>having a common sysid space for both. See discussion started by Peter
>a month or two back (I think thread title mentioned "roles").
>
> regards, tom lane
>
>
>