While the REFERENCES privilege controls who can create foreign keys
referring to one's tables, it seems you can evade it by using CREATE
CONSTRAINT TRIGGER directly.

This is the "slave" portion of a FK constraint I got from pg_dump:

CREATE CONSTRAINT TRIGGER "$1"
    AFTER INSERT OR UPDATE ON "slave"
    FROM master
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_check_ins" ('$1', 'slave', 'master', 'UNSPECIFIED', 'x', 'a');

To create this you only need to have a privilege on "slave", but it
creates a fully functional way to "query" the primary key of the master
table by brute force, and probably also to lock the table up, although I
haven't checked that.

It seems we need to check the privilege on the table mentioned in the FROM
"foo" clause as well.  Is that correct and sufficient?

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 14 Aug 2002 19:09:25 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
CREATE CONSTRAINT TRIGGER appears to be a security hole
Peter Eisentraut <peter_e@gmx.net> writes:
> While the REFERENCES privilege controls who can create foreign keys
> referring to one's tables, it seems you can evade it by using CREATE
> CONSTRAINT TRIGGER directly.

Good point.

> It seems we need to check the privilege on the table mentioned in the FROM
> "foo" clause as well.  Is that correct and sufficient?

It is if we assume that every CREATE CONSTRAINT TRIGGER is used for
something that should require REFERENCES privilege.  Given that the
command is not really intended for user use anyway, this is probably
okay to assume.

One might try to evade the check by mentioning something different in
FROM than is mentioned in the trigger arguments, but as of CVS tip
that doesn't work --- the RI triggers don't look at the relation-name
arguments anymore, only at the FROM link.

			regards, tom lane

Wed, 14 Aug 2002 13:34:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CREATE CONSTRAINT TRIGGER appears to be a security hole 