Обсуждение: Pg_catalog reference

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.

What you consider to be a solution in thi case?

Any help appriciated. Thanks.

Best regards,
Anton Nikiforov

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

It is not possible to assign triggers to system tables hence it is impossible to reference system table in FK constraint. Obviously, users that don't exist in the database cannot modify anything, you can use "session_user" and "current_user" functions for logging purposes, see http://www.postgresql.org/docs/8.2/interactive/functions-info.html