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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
