I can confirm your problem in current 8.0.X CVS. Your fixes to update pg_group when a user is added (because they might be added to a group at the same time) is correct too. And the visiblity problem is a valid bug too.
--------------------------------------------------------------------------- Dennis Vshivkov wrote: > Package: postgresql-8.0 > Version: 8.0.3-13 > Severity: important > Tags: patch, upstream > > Here's the problem: > > db=# CREATE GROUP g1; > CREATE GROUP > db=# CREATE USER u1 IN GROUP g1; (1) > CREATE USER > > # cat /var/lib/postgresql/8.0/main/global/pg_group > # > > The file gets rewritten, but the group `g1' line does not get > added to the file. Continue: > > db=# CREATE USER u2 IN GROUP g1; (2) > CREATE USER > > # cat /var/lib/postgresql/8.0/main/global/pg_group > "g1" "u1" > # > > Now the line is there, but it lacks the latest member. Consider > this also: > > db=# ALTER USER u2 RENAME TO u3; (3) > ALTER USER > > # cat /var/lib/postgresql/8.0/main/global/pg_group > "g1" "u1" "u2" > # > > The problem is that the code that updates pg_group file resolves > group membership through the system user catalogue cache. The > file update happens shortly before the commit, but the caches > only see updates after the commit. Because of this, new users > or changes in users' names often do not make it to pg_group. > That leads to mysterious authentication failures subsequently. > The problem can also have security implications for certain > pg_hba.conf arrangements. > > The attached `98-6-pg_group-stale-data-fix.patch' makes the code > in question access the system user table directly and thus fixes > the cases (1) and (2), however (3) is doubly ill: the user > renaming code does not even trigger a pg_group file update. > Hence the other patch, `98-5-rename-user-update-pg_group.patch'. > > A byproduct of the main fix is removal of an unlikely system > cache reference leak which happens if a group member name > contains a newline. > > The problems were found and the fixes were done for PostgreSQL > 8.0.3 release. The flaws seem intact in 8.0.4 source code, too. > > Hope this helps. > > -- > /Awesome Walrus <[EMAIL PROTECTED]> [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]