Re: [GENERAL] Pg_catalog reference

2007-12-12 Thread Vyacheslav Kalinin
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


Re: [GENERAL] Pg_catalog reference

2007-12-10 Thread Richard Huxton

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 [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


[GENERAL] Pg_catalog reference

2007-12-07 Thread Anton Nikiforov

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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster