Обсуждение: Suggested way of associating PG user metadata with record
Hello, I'd like to associate the PG user who created certain sorts of records with that record in my application. The obvious way to do this is by creating a text column with a default value of current_user in the record's table, or perhaps something similar with a trigger. However, because what I've described strikes me as a fairly common requirement, I suspect there may be a purpose-built, more direct and efficient feature I could use, such as a referential user datatype or something like that. Is there? If not, does the obvious approach seem reasonable? Regards, Peter Geoghegan
On Wed, May 20, 2009 at 12:28 PM, Peter Geoghegan <peter.geoghegan86@gmail.com> wrote: > Hello, > > I'd like to associate the PG user who created certain sorts of records > with that record in my application. The obvious way to do this is by > creating a text column with a default value of current_user in the > record's table, or perhaps something similar with a trigger. However, > because what I've described strikes me as a fairly common requirement, > I suspect there may be a purpose-built, more direct and efficient > feature I could use, such as a referential user datatype or something > like that. > > Is there? If not, does the obvious approach seem reasonable? > > Regards, > Peter Geoghegan > You should be careful about linking records to PostgreSQL user accounts. If the PostgreSQL accounts are removed, there could be problems with the foreign key references, if you don't implement it carefully. 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). http://www.postgresql.org/docs/8.2/interactive/view-pg-user.html David.
David, > You should be careful about linking records to PostgreSQL user > accounts. If the PostgreSQL accounts are removed, there could be > problems with the foreign key references, if you don't implement it > carefully. > > 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. Thanks, Peter Geoghegan
>> >> 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.
David, > 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. Yes, that had occurred to me. I think I'll go ahead and take the obvious approach. On the whole, it seems very easy to implement and maintain to me, and the alternative approach doesn't buy me any very appreciable advantage. Thanks, Peter Geoghegan