You should never be saving passwords directly in a database, rather a hashed version of a password (not encrypted). An irreversible hash prevents a possible breach by use of password, as it would take supercomputers years just to crack a single account (assuming users are required 8 character minimum, uppercase, lowercase, number, and a special character).
Say our password is “XojoRocks1@”…
We could then run a SHA256 hash for example and save that result to our database as…
Now, even if someone obtains this hash, it’s really useless without knowing the actual password. In cases where users recycle passwords across platforms, this can help secure their other accounts … Especially if you mix hashes or come up with your own “proprietary” hashing mechanism. Check out the crypto classes.
Anytime a user logs in, hash their entry, lookup the username, and compare the hashes. Or, lookup the hash and username, to see if a result is returned (best practices).
**I’ve always found cascading data to slow things down as the database grows. Generally, I’ll create a main table, with a column that has an identifier unique to a user, then in all subsequent tables, reference that unique ID for records belonging, related to that user. Down the road, for adding features or advanced data manipulation, this can also be beneficial. WordPress actually adopted a similar strategy as it allowed the most flexibility… Plus you can easily join data when needed by referencing the unique ID, and pull data from multiple tables simultaneously.
PostgreSQL Does that… I’m hooking into the system tables…
in any case My main concern was maintaining referential integrity and what i thought were tables are actually views so can’t be used in foreign Key definitions) and I don’t know what actual system table to link to, or if Postgre allows that.
Try making a single column (“UserID”) primary key, unique, auto-increment, with another column that’s “username”, unique, not null.
In this case, UserID would become your primary key by which you can reference in all other tables, a user’s associated record… Thus in the main user table, the ID and USERNAME would remain unique, and can only occur once. In other tables of course, the user’s unique ID can occur more than once, for many records.
To solve orphans, add triggers after creating main table to handle “on delete” for other tables. In practice, sometimes orphans are wanted… Let’s say in a forum with comments… If a user deleted their account and all their responses get removed, the threads now seem pointless? You can also programmatically drop user records from other tables, in a single line of code, without triggers. This allows select record drops instead of “everything associated” in all cases.
The reasons I am thinking of doing it this way are:
1)the system login name is what controls privileges
2) the login name is both human readable (not just a #) and as well is guaranteed to be unique when actual names are not necessarily,
3) is what will be recorded in log files (via current_user)
That is why I thought a link to the role system table OID for the user would be best. Then I can with creating a view I set up on my user table combined which combined relevant system table fields, and allow some user management from within my app.
In any case I found the docs on the system tables, as well as the fact the referencing them in a table definition (or view) uses the privileges of the able creator not the end user, and all have to do is give read access to the view.
Given most of the DB work I had done was either SQLite or with DS where I did not have to worry about system security or managing the DB server as a whole, all the Postgres privilege/security stuff has had me a bit confused/nervous about how I should approach things.