pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedforeign key on pg_shadow
2+ messages / 2 participants
[nested] [flat]
* foreign key on pg_shadow
@ 2005-06-14 11:34 M.D.G. Lange <mlange@dltmedia.nl>
0 siblings, 1 reply; 2+ messages in thread
From: M.D.G. Lange @ 2005-06-14 11:34 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: foreign key on pg_shadow
@ 2005-06-14 13:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: M.D.G. Lange <mlange@dltmedia.nl>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-06-14 13:37 UTC (permalink / raw)
To: M.D.G. Lange <mlange@dltmedia.nl>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-14 13:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-14 11:34 foreign key on pg_shadow M.D.G. Lange <mlange@dltmedia.nl>
2005-06-14 13:37 ` Tom Lane <tgl@sss.pgh.pa.us>
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