Re: Suggested way of associating PG user metadata with record

On Wed, May 20, 2009 at 12:28 PM, Peter Geoghegan
<peter(dot)geoghegan86(at)gmail(dot)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.