Обсуждение: 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