Don Felgar <dfelgar@rainier-infosys.com> writes:
> 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.
Actually usename is a primary key (and is even marked as NOT NULL
in 7.3) ... but the system still won't let you reference it as a
foreign key:
regression=# create table fooey (usr name references pg_shadow(usename));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: CREATE TABLE: relation "pg_shadow" is a system catalog
The reason for this restriction is that foreign keys require triggers,
and we don't support placing triggers on the system catalogs.
While I don't think we'll ever allow BEFORE triggers on the catalogs,
AFTER triggers might be feasible, which would be enough for foreign
key references. Don't hold your breath though (it won't be in 7.3
for sure).
To solve your problem, what I'd suggest is that you create your own
table that is your master list of users, and put triggers on it to issue
CREATE/DROP USER commands when entries are added or deleted.
regards, tom lane