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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
