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