I have dabbled in Postgres on and off a little bit over the years but I don’t really know it in any depth.
In this case I want to link a bunch of stuff about the user to a users’s login.
The way I was thinking to do this is to create the user role, and use it’s oid as the primary key on my user data table…
The reason I was not planning to use the role ID ( user name) was because it can be changed and user login name are usually based on their name (so they can remember it)…
But people do change their names - I can see someone want to change their login name if the change their name after getting married or a bad divorce for example.
So since the user login name (role ID) can be changed I thought using the OID as the link might be the better way…
But then I seemed to remember something way back when about OIDs not staying the same between dump and restores…
Is that still true and does it apply to role OID’s?
If it is, then I guess I would have to go with the role ID as the link even though that is not optimal.
Not sure what an OID is in reference to Postgres, but what I would do is create a “userid” field and assign a UUID when the user is created. This value will be unique , and not change…
And if it is not needed to be exposed to the outside world, then its complexity is not an issue.
if that was OK I i could just as well use an auto-incrementing primary key instead of a UUID. That would guarantee uniqueness and the usr would never need to see it either… and that is what I usually do…
But in this case I want an unbreakable link between my table and the user login (Which is defined in a Postgres system table).
An OID is an an unsigned 4 byte integer Object IDentifier.
Every object Identifier in Postgres (think tables , functions, triggers, user names etc) has a SQL name (which can be changed) and an OID associated with it in the system tables.
The OID during normal operations can not be changed but, as i said the name can be… and of course that can happen outside of the control of my app my a sys admin.
So OIDs sound perfect, BUT at least some time in the past, I seem to recall seeing that OIDs don’t have to persist when you do a database dump and restore. In that case teh objects can get different OIDs assigned.
If that applies to user (role) OIDs, then I should not use it as the link to the login, and will have to rely on teh SQL name and just hope that never get changed outside of my app.
I was informed on Stack overflow that even such system OIDs will not survive a dump and reload… so is there is no way to create an unbreakable link between the loginName and my user table.
So the best I can do is save the login name and maybe use a trigger to check if that is stored in the system table and raise an exception if it is not.
I was not worried about the key to my table changing … If I was worried about THAT, I could create a before update trigger that disables changing any field I want - essentially make it read only…
The issue is the link in my table to the user login in the system table.
I have to use the system role (user login) name as the link to the user login as OIDs can change when the data is restored after a backup or change of machine.
If the OID never changed, as it would be unique to the user, I was originally thinking I could kill 2 birds with one stone by using the OID not just as that link, but also as the primary key of my table.
Now my primary key won’t be doing double duty as I can’t create a trigger to stop the system table change or to update that field in my table if the login name is changed.
As I said, Postgres allows changing the text of the login role name without losing membership in the roles (groups) the user is in, but leaves the OID unchanged.
So using the role name as the link to the system table is not bulletproof… Unfortunately it turns out nothing is in this case, so using the login name is how it has to be done in Postgres.
When you create a new account assign it a UUID (or just about ANY other unique key) that is NOT the OID and use that as your user primary key
Then use that as the column that other tables reference
Then you can change any aspect of the users account and not change the primary key and everything will stay related even after a dump & reload
Using the OID as as the Primary key was red herring as I explained above.
The critical issue was reliably linking the user’s login role to my user table, because role name is mutable, and the system role OIDs don’t persist after a reload.
Unfortunately there is no absolute way to insure link between my user table and login role can’t be broken…
So in terms of the audit trail, the best that can be done is store the login role name in my table and have the audit triggers search my table for the role name and also store the primary key to my table in the audit table, or throw an exception if it does not find a match.