Обсуждение: foreign key on pg_shadow

Поиск
Список
Период
Сортировка

foreign key on pg_shadow

От
"M.D.G. Lange"
Дата:
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


Re: foreign key on pg_shadow

От
Tom Lane
Дата:
"M.D.G. Lange" <mlange@dltmedia.nl> writes:
> 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.

We do not support foreign keys (or indeed triggers of any kind) on
system catalogs.  I don't foresee that happening in the near future
either, though I think there is something about it on the TODO list.
        regards, tom lane