From: | "M(dot)D(dot)G(dot) Lange" <mlange(at)dltmedia(dot)nl> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | foreign key on pg_shadow |
Date: | 2005-06-14 11:34:25 |
Message-ID: | 42AEC0C1.7090305@dltmedia.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Dear list,
Upon designing our application we thought that the following setup would
be a good idea to implement security:
Using the pg_shadow table as it is:
| pg_shadow |
+-------------+
| usename |
| usesysid |
| usecreatedb |
| usesuper |
| usecatupd |
| passwd |
| valuntil |
| useconfig |
We also wanted an extension on it tblusersettings:
| tblusersettings |
+-----------------+
| userid |
| language |
| address |
| birthdate |
| department |
| etc... |
Where userid should reference to pg_shadow.usesysid. Making it so, that
the usersettings for a user would be deleted on a DROP USER.
So I tried to create a foreign key constraint with ON DELETE CASCADE.
No matter what ON DELETE constraint I created, the system will not allow
me to create a foreign key, as pg_shadow is a system catalog.
Yet using the database user with this extention would be awesome. I
could try to inherit the table, altough I am not certain if that would
be allowed...
Anyway: is there a way to get this setup working, or should I give up
and try it completely different?
I am using PostgreSQL 8.0.3
TIA,
Michiel
From | Date | Subject | |
---|---|---|---|
Next Message | PFC | 2005-06-14 12:26:21 | Re: SELECT very slow |
Previous Message | Vilinski Vladimir | 2005-06-14 11:12:13 | WHY transaction waits for another transaction? |