I have dabbled in Postgres on and off a little bit over teh 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.
I use the ID as PrimaryKey for fast access, but use their email address field as a non-Nil, unique field (so you cannot have two accounts with the same email address) to separate one from another.
This way they can change any of their personal attributes without harm. I allow them to change their email address, but them must re-verify it with their password, an emailed PIN plus two security questions-answer pairs.
For audit trail purposes I want to be able to save the Postgres current_User on the DB side outside of my app all handled by PG triggers . Think of different apps (maybe mobile, web and desktop) all running off that DB.
I want the audit trail to be app independent but still want it traceable to the user table. To use that I still need an unbreakable link between the current_user role name and my User table.
Email address do change (I’ve see it) when women get married and sometimes when they get divorced.
To keep finding all the activity of a user in the audit trail simple, it would be nice to have a non-mutable link between my user table and the system login role
That is what why I am thinking using the role OID in my table and the audit trail (if i never changes!) as the role name can be changed.
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 user table and rate an exception if it is not.
It is not best practice to grant database user accounts to individual users as it represents both a security risk and can create a performance overhead.
If so, without each user being assigned their own login role, how could you reliably have individual passwords for users and enforce having an audit trail via triggers that is traceable to the user?
On the security. side their login role will have minimal privileges, but they will be assigned to group rolles that have the requisite requisite privileges they need for their job that they will inhert from such job specific group roles.
No it is a standard pattern intended to defend the company’s data resources and common requirement to deploy software in most production environments such as a bank, insurance company, airline and so on.
For example if you grand a user a database account then they can happily by pass an business or security logic you have in your application and directly manipulate or dump out your data.
If you do some searching on this site, there are a few good discussions and papers on good approaches to implementing user level security.
Are you assuming the key controls/business logic is the client app?
That is not what I was planning to do. I want an architecture where at least the important parts of that are built into the DB structure.
Doing it that way the client is really only about the UI, and it would be easier to create separate consistent Desktop, web and mobile apps if needed.
So I was planning to use the control features built into postgres for roles, by granting or revoking privileges for the relevant DB Tables, functions (stored procedures), views on the roles , as well as the use of triggers that can vary behavior based on roles.
BTW Postgres also supports row level security if needed.
Does that you say still apply in that case?
If one needs additional security for outside threats one, could create a middle app with an API that the client connects to, and only allow that App to connect to the DB. Building the business logic into the DB would are that simpler I think , but this app would only be available on our intranet and not have super sensitive information so I don’t think that is necessary.
I searched for “user level security” and all I found was stuff for web apps about using roles the way I am suggesting. Is there a better search term?