foreign key on pg_shadow

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

Responses

Browse pgsql-sql by date

  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?