Anton Nikiforov wrote:
> Dear all,
> i'm trying to create tables using pg_user (pg_authid) as a foreign key
> for my table. I need to log and control that only registered users can
> modify data and i want to control data changes via logging triggers. I
> need to know who exactly was modifying data. To be more exact i want to
> create a trigger that can log user information into the a table. I want
> to have a way to make rollbacks of high level data (documents...whatever).
>
> But when i'm trying to create a constraint referencing pg_catalog tables
> - i'm getting an error.
Irritating, isn't it? You can't attach triggers to system tables, and
that means no foreign-key references.
> What you consider to be a solution in thi case?
The best I've come up with is to have an app_user table that you *can*
have foreign keys referencing and have triggers on that keep pg_user
up-to-date.
It's not perfect - as a sysadmin you can go in and delete pg_user rows
while app_user assumes they're still there. In practice, it seems to
work well enough though.
--
Richard Huxton
Archonet Ltd