ExecutorCheckPerms() hook
in [PgSql]
|
From: Stephen Frost on 21 Jul 2010 21:02 * Robert Haas (robertmhaas(a)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
From: Stephen Frost on 21 Jul 2010 21:04 * KaiGai Kohei (kaigai(a)ak.jp.nec.com) wrote: > We can find out a similar case in CreateTrigger(). > If I was granted TRIGGER privilege on a certain table, I can create a new > trigger on the table without its ownership. More commonly, it allows us > to modify a certain property of the table without its ownership. TRIGGER is hardly the same as REFERENCES. If we invented a new priv, it would be more like 'FK_CREATE'. > Perhaps, if SQL permission would be more fine-grained, for example, > "RENAME" permission might control RENAME TO statement, rather than > its ownership. This wouldn't actually be any more fine-grained, it'd just be adding rights on to an existing priv, which I think is a wholly *bad* idea. > What is the reason why we check its ownership here, although we already > have REFERENCES permission to control ADD FOREIGN KEY? REFERENCES is needed on the REFERENCED table, ownership is needed on the REFERENCING table. They're not the same.. We only allow owners of objects to change the structure of those objects. Adding a FK to another table doesn't really change the structure of the referenced table. Adding a FK does though, imv. Thanks, Stephen
From: Robert Haas on 21 Jul 2010 21:08 On Wed, Jul 21, 2010 at 9:02 PM, Stephen Frost <sfrost(a)snowman.net> wrote: > * Robert Haas (robertmhaas(a)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. Well, in that case, we should fix the fine documentation: To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced columns. The privilege may be granted for all columns of a table, or just specific columns. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Stephen Frost on 21 Jul 2010 21:15 * Robert Haas (robertmhaas(a)gmail.com) wrote: > On Wed, Jul 21, 2010 at 9:02 PM, Stephen Frost <sfrost(a)snowman.net> wrote: > > Errr, no. If I grant you REFERENCES on my table, it means you can > > create a FK to it from some other table. > > Well, in that case, we should fix the fine documentation: > > To create a foreign key constraint, it is > necessary to have this privilege on both the referencing and > referenced columns. The privilege may be granted for all columns > of a table, or just specific columns. Technically that's true.. You just *also* have to own the referencing table. :) I agree though, if my claims are correct (which I'd like to think they are, but perusing the SQL spec just now didn't make it as abundently clear as I would have hoped...), and it's how PG acts today anyway, we should definitely fix the docs. Also, we do document that to use ALTER TABLE you have to own the table you're calling ALTER TABLE on, and obviously if you're calling CREATE TABLE you're "owner" of the object.. Have we got another way to add a FK to an existing table? If so, we should make sure they're all consistant in any case. Thanks, Stephen
From: KaiGai Kohei on 21 Jul 2010 21:18
(2010/07/22 10:04), Stephen Frost wrote: > * KaiGai Kohei (kaigai(a)ak.jp.nec.com) wrote: >> We can find out a similar case in CreateTrigger(). >> If I was granted TRIGGER privilege on a certain table, I can create a new >> trigger on the table without its ownership. More commonly, it allows us >> to modify a certain property of the table without its ownership. > > TRIGGER is hardly the same as REFERENCES. If we invented a new priv, it > would be more like 'FK_CREATE'. > >> Perhaps, if SQL permission would be more fine-grained, for example, >> "RENAME" permission might control RENAME TO statement, rather than >> its ownership. > > This wouldn't actually be any more fine-grained, it'd just be adding > rights on to an existing priv, which I think is a wholly *bad* idea. > >> What is the reason why we check its ownership here, although we already >> have REFERENCES permission to control ADD FOREIGN KEY? > > REFERENCES is needed on the REFERENCED table, ownership is needed on the > REFERENCING table. They're not the same.. > > We only allow owners of objects to change the structure of those > objects. Adding a FK to another table doesn't really change the > structure of the referenced table. Adding a FK does though, imv. > However, existing ATAddForeignKeyConstraint() checks REFERENCES permission on both of the referencing and referenced table/columns. Is it unexpected behavior??? It is an agreeable interpretation that we need ownership on the referencing table because creating a new FK equals to change a certain property of the referencing table. If so, why REFERENCES permissions are necessary on the referencing side, not only referenced side? Thanks, -- KaiGai Kohei <kaigai(a)ak.jp.nec.com> -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |