----- Original Message ----- From: "Tom Lane" > It's not apparent to me why you have to control the userID in order to > have an auxiliary table defining a user. You could do something like When ever a postgresql user does anything to a record in a table we have a trigger function to check at the beginning of each transaction whether they currently have sufficient privallages to perform what ever action they are attempting. (These are things that go beyond what GROUPS and ROLES can achieve). In order to do this effeciently we used the session id of the postgresql user to look up our "user" record for the check and that is where the link originally arose from. At the time the documentation informed that we could set the SYSID ourselves, and by doing that we created the logical link between our primary key and a postgresql user - it seemed the most logical and effecient means of managing things at the time. > keeping the currently assigned OID in the aux table, setting the > field to null or zero if the user doesn't currently exist in pg_authid. Our "user" table stores information about the user before, during and after the user is a postgresql user so that we can effectively hold an audit of who has created, edited, deleted what and when. Therefore the primary key (SYSID) in this table, is the foreign key in many other tables that users can access for record creation and modification. Therefore the primary key cannot be null when the user is no longer a postgresql user and idealy we don't want it changing as this could potentially mean 100's if not 1000's of foreign key cascading updates. Our "user" table holds security privilage and access rights information which can change at the drop of a hat. Our approach to managing the mapping of our privillages to postgresql GROUPS etc has been one of brute force. It is much easier to DROP a user and recreate them when their access rights change, as apposed to figuring out what GROUPS they currently belong to and what GROUPS they should now belong to. Therefore under the new scheme of things dropping and recreating a new user would cause a change in the OID and this is not desireable as described above. Yes we could create a new column that mapps to the postgresql OID, but the management of this seems like a lot more work especially since the creation of postgresql users, controlled by our "user" table, are handled in AFTER trigger events. So going back to my original questions, you have answered them, OIDs are now used because 8.1 now handles dependency information on user owned objects. Hence the bottom line for us is that we can no longer use the old SYSID approach for mapping a postgresql user to the same user in our "user" table. Its a case of we have heavy reliance of the internal functions: GetSessionUserId() and GetUserNameFromId() Now we will simpy have to create our own versions of these functions that do the correct mapping of the postgresql user to the same user in our "user" table. > > I take it then that the patching of that feature would cause problems > > because the OID is controlled by postgreql and we could therefore be trying > > to create a user with an OID that could already be in use. > You ran that risk already with the SYSID scheme, no? So far we haven't had a problem with it - we reserved the first 20 numbers for "system" users (e.g. postgres = 1) and started our sequence numbering from 21. It is a long time since I looked at that code (7.1) and my understanding at the time was that SYSID was only used by postgresql for identifying a postgresql user by id rather than by name and was only created and stored in pg_shadow. A "nice" solution for backward compatibility would have been to put the original SYSID column, formally from pg_shadow table, into the pg_authid table. Its only an integer value, so hardly takes up much space, and leave the creation logic of this column value as before. Then all you needed to do was add your own functions that use the OID side of things. For example GetSessionUserId() and GetUserNameFromId() could have been left as is and then create very similar functions for use on the OID side of things e.g. GetSessionUserOID() and GetUserNameFromOID() Could have, would have, should have... but nobody thought of it and probably a bit late now though.... I hope I haven't sounded negative, I do appreciate all the work that goes on from the postgreql team and I think the new features in 8.1 far out way the minor inconveniences that it will cause us in upgrading.... Keep up the good work, and cheers for the feed back. Regards Donald Fraser
