Hello,
I feel like I must be missing something obvious. I have what I am
sure must be a common problem, yet I can't find a good solution on the
web or in the PostgreSQL docs.
I recently tore out a front-end based user authentication system,
because I switched to PostgreSQL 7.2 and found that it contains
functionality similar to what I had implemented.
I reference users from other tables, for instance a table of sessions.
My problem is, there seems to be no way to have a foreign key
reference into pg_shadow as it has no unique, non-null field.
I tried creating a table that inherits from pg_shadow and has a
primary key besides, but the problem with this is that INSERT into
this table does not fully duplicate CREATE USER. I also considered
using OID's, but these are not defined to be UNIQUE.
So it seems that my choices are either to 1) create a table inheriting
from pg_shadow, and a companion a function that re-implmenets CREATE
USER when I do an INSERT into it, or 2) use the pg_shadow.usename
elsewhere, and always watch out for situations where inconsistencies
might occur. Neither seems very good.
Any advice?
Thanks
Don