From: M.D.G. Lange <mlange@dltmedia.nl>
To: pgsql-sql@postgresql.org
Subject: foreign key on pg_shadow
Date: Tue, 14 Jun 2005 13:34:25 +0200
Message-ID: <42AEC0C1.7090305@dltmedia.nl> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mlange@dltmedia.nl
Subject: Re: foreign key on pg_shadow
In-Reply-To: <42AEC0C1.7090305@dltmedia.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox