Message-ID: <42AEC0C1.7090305@dltmedia.nl>
Date: Tue, 14 Jun 2005 13:34:25 +0200
From: "M.D.G. Lange" <mlange@dltmedia.nl>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: foreign key on pg_shadow
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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