Re: Suggested way of associating PG user metadata with record

>>
>> That said, you could also use the value in pg_user.usesysid as a
>> unique ID, rather than the account name (if you want to distinguish
>> between different users with the same login name, over a period of
>> time where users were removed and re-added).
>
> Perhaps I should just not use a foreign key, and accept that users may
> be removed, and do a LEFT JOIN to pg_user and SELECT
> COALESCE(pg_use.username, 'user removed'). Since I imagine that
> removing users will rarely or never occur in practice, this may be the
> thing to do. Then again, perhaps I'm "prematurely pessimising" by not
> just doing the simple and obvious thing and using a text column as
> described in my original mail. I'd like to do whatever is considered
> canonical, but I'm not sure what that is in this case.
>

A text column should be perfectly fine, and is the simplest
implementation. But it does have a few possible issues that you need
to keep in mind (besides things like users being removed). For
instance, if PostgreSQL users are renamed (eg: ALTER USER name RENAME
TO newname), then your text column becomes incorrect.

If you want to record additional user info (besides what PostgreSQL
already tracks), I'd suggest creating your own users table, and inside
there add a (non foreign key reference) entry for pg_user.usesysid, as
well as any other application-specific details. Then, you link other
tables to your table (with foreign key reference), instead of using
text columns.