Re: ExecutorCheckPerms() hook

* Robert Haas (robertmhaas@gmail.com) wrote:
> I think the relevant case might be where ymj owns fk_tbl but not
> pk_tbl, and has REFERENCES but not SELECT on pk_tbl.
>
> Come to think of it, I wonder if REFERENCES on fk_tbl ought to be
> sufficient to create a foreign key.  Currently, it requires ownership:
>
> rhaas=> ALTER TABLE fk_tbl ADD FOREIGN KEY (x) REFERENCES pk_tbl (a);
> ERROR:  must be owner of relation fk_tbl

Errr, no.  If I grant you REFERENCES on my table, it means you can
create a FK to it from some other table.  That's very different from
saying you can create a FK *on* my table.  Put another way- you can
prevent me from deleting data in my table if you have a FK to it, but
you can prevent me from *inserting* data into my table if you can create
a FK on it.  Those are two distinct and different things and I
definitely don't believe we should have 1 permission be used for both.

Also, REFERENCES is in the spec, and I don't believe you could
interprete it to letting people create FKs on tables they have
REFERENCES on, afaik.  I don't believe it's how other RDBMS' are either,
but I have to admit to not having tested yet.

Let's not add things to an SQL-defined priviledge or we'll end up
seriously suprising people coming from standard-conforming databases,
and in a security way.

All that being said, having more fine-grained control over what can be
done through an ALTER TABLE command is a neat idea, but it's also a
pretty huge can of worms.  I'd rather spend time figuring out the
somewhat smaller set of things which are superuser only right now, and
creating a way to have just non-superuser roles which can do those
things (where it makes sense, anyway).
Thanks,
    Stephen