Postgre: Is referencing pg_authid safe?

Databases
1

I want to create a 1:1 relationship between a pg user and a user table I create with additional information.

I was trying do do this:

CREATE TABLE UserTable(
	Key0      INTEGER  PRIMARY KEY 
                      REFERENCES  pg_catalog.pg_user(useSysID) On DELETE CASCADE,

But as pg_user is a view and not a table so I get an error.
It should work if I did:

CREATE TABLE  UserTable(
	Key0      INTEGER  PRIMARY KEY  
                      REFERENCES  pg_catalog.pg_authid(oid) On DELETE CASCADE,

But that I think would mean making pg_authid readable by anyone who needs to read the user table, and so potentially exposing passwords.

If so, is there another good solution besides doing:

CREATE TABLE IF NOT EXISTS UserTable(
	Key0     INTEGER  PRIMARY KEY  ,

and maintaining the link to the System user record though Xojo App code?

That is obviously not ideal.

Thanks
-karen

2

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…

485a3710a4016d59cf27d26555b4f03e009796fd618b800555cbafb898a5f9cd

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

3

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

4

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.

-karen

5

That is what i was originally doing but realized I could wind up with “orphans”
I need a 1:1 correspondence with system users data . There will never be a huge number employees.

-Karen

6

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.

7

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.

8

I’ve not heard of users being setup this way, and it sounds to me like you’re asking for trouble trying to connect to the system users.

Or this is a common technique and you should ignore me.

In any case, postgres is pretty open so you can see the definition of that view to discover the tables it’s pulling it’s data from, then try to reference those columns directly.

1 Like
9

I have no idea what is usual… I’m winging it!

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.

Thanks,
-karen

-Karen