Re: WITH SYSID feature dropped - Mailing list pgsql-admin

----- 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