From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres Permissions Article |
Date: | 2017-03-30 13:57:45 |
Message-ID: | 20170330135745.GA4612@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On 2017-03-29 08:05:23 -0700, Paul Jungwirth wrote:
> On 03/29/2017 06:36 AM, Tom Lane wrote:
> >Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> >>Being able to create foreign keys may allow to indirectly
> >>discover whether certain values exists in a table which I
> >>don't otherwise have access to (by means of failure or
> >>success to create a judiciously crafted FK).
> >
> >Aside from that, an FK can easily be used to cause effective
> >denial-of-service, for example preventing rows from being deleted
> >within a table, or adding enormous overhead to such a deletion.
>
> Thank you both for taking a look! I agree those are both worthwhile
> concerns. It still seems a little strange it is not just part of the CREATE
> permission (for example). I understand why not everyone can create a foreign
> key, I just have trouble imagining a use case where it is helpful to
> separate it from other DDL commands.
A foreign key affects not only the table on which it is defined but also
the table it references.
If Alice creates a table “master” and Bob creates a table “detail”
referencing “master”, Bob can prevent Alice from deleting entries from
her own table. So Alice must be able to decide whom she allows to
reference her tables.
I don't see how how this could be part of the create privilege - I
certainly want different roles to be able to create their own tables (or
views, or whatever) without being able to DOS each other (accidentally
or intentionally).
(Also I don't understand why you wrote “You need the permission on both
tables”: Only the owner of a table can add constraints to it - this
privilege cannot be granted to other roles at all. So to create a
foreign key constraint you need to be the owner of the referencing table
and have the references privilege on the referenced table. It's not
symmetrical.)
hp
--
_ | Peter J. Holzer | A coding theorist is someone who doesn't
|_|_) | | think Alice is crazy.
| | | hjp(at)hjp(dot)at | -- John Gordon
__/ | http://www.hjp.at/ | http://downlode.org/Etext/alicebob.html
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Tempelmeier | 2017-03-30 13:58:36 | Keycloak and Postgres |
Previous Message | Adrian Klaver | 2017-03-30 13:27:58 | Re: PostgreSQL and Kubernetes |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2017-03-30 13:57:57 | Re: Patch: Write Amplification Reduction Method (WARM) |
Previous Message | Robert Haas | 2017-03-30 13:53:09 | Re: [POC] A better way to expand hash indexes. |