Thread: No primary key in pg_shadow

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

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