Обсуждение: Postgres Permissions Article

Hi All,

I wrote a blog post about the Postgres permissions system, and I thought
I'd share:

http://illuminatedcomputing.com/posts/2017/03/postgres-permissions/

The main point I wanted to convey, which I somehow never grasped
confidently from reading the docs or other articles, is how permissions
are purely additive. Also I wanted to gather in one place how to *see
the current permissions*, which seems to be missing/incomplete in many
other articles. Anyway, maybe it will be helpful for some of you! Or for
the true experts, if you see any errors, I'd be happy to know so I can
correct them.

I also shared a few opinions amidst the facts (like that `USAGE` for
schemas doesn't add much), so I am very pleased to have those
challenged. You can consider them my own outstanding questions. I'd be
especially grateful for any feedback there.

Yours,
Paul

On Tue, Mar 28, 2017 at 09:47:40AM -0700, Paul Jungwirth wrote:

> I wrote a blog post about the Postgres permissions system, and I thought I'd
> share:
>
> http://illuminatedcomputing.com/posts/2017/03/postgres-permissions/

> I also shared a few opinions amidst the facts (like that `USAGE` for schemas
> doesn't add much), so I am very pleased to have those challenged. You can
> consider them my own outstanding questions. I'd be especially grateful for
> any feedback there.

Not that I am an expert in any way but here's a thought on
why a permission on foreign key creation might be useful:

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).

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> On Tue, Mar 28, 2017 at 09:47:40AM -0700, Paul Jungwirth wrote:
>> I wrote a blog post about the Postgres permissions system, and I thought I'd
>> share:
>> http://illuminatedcomputing.com/posts/2017/03/postgres-permissions/

> Not that I am an expert in any way but here's a thought on
> why a permission on foreign key creation might be useful:

> 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.

            regards, tom lane

On 03/29/2017 06:36 AM, Tom Lane wrote:
> Karsten Hilbert <Karsten.Hilbert@gmx.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. Anyway, I didn't
write the article to nitpick details like that, but sometimes by asking
"why" you learn new things. I really appreciate your offering your thoughts!

Paul

On 2017-03-29 08:05:23 -0700, Paul Jungwirth wrote:
> On 03/29/2017 06:36 AM, Tom Lane wrote:
> >Karsten Hilbert <Karsten.Hilbert@gmx.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@hjp.at         | -- John Gordon
__/   | http://www.hjp.at/ |    http://downlode.org/Etext/alicebob.html

> 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

Ah, this piece was really helpful for me in making it click. Thanks so
much! I added a couple new paragraphs to my post with a link back to
this thread. I feel like it all makes sense now! :-)

FYI "You need this permission on both tables" is what the docs say
(https://www.postgresql.org/docs/9.6/static/sql-grant.html):

 > To create a foreign key constraint, it is necessary to have this
privilege on both the referencing and referenced columns.

Maybe it would be worth clarifying there that you need to *own* the
referencing table, and you need REFERENCES on the referenced table?

In any case, thanks again to you all for your help figuring this out!

Paul