Re: PostgreSQL 12.1 patch for "private_modify" table creation option for data validation reinforcement

Abdul Yadi AH-2 <abdulyadi(dot)datatrans(at)gmail(dot)com> writes:
> As I have published on
> https://abdulyadi.wordpress.com/2019/12/26/reinforce-data-validation-prevent-direct-table-modification/,
> the patch is to have "private_modify" option in table creation. For example:
> CREATE TABLE mytable (id integer) WITH (private_modify=true);

> Having the option set, even superuser can not insert/update/delete the
> table outside SQL or SPI-based function where complex data validation takes
> place.

I do not actually see the point of this. It seems randomly inconsistent
with the normal SQL permissions mechanisms, and it's not very flexible,
nor does it add any meaningful security AFAICS. Anybody who can
execute SQL can create a function. For that matter, you don't even
need to create a persistent function: you can just wrap the command
in a DO block, and that'll bypass this restriction.

In what way is this better than the usual technique of putting the
table update logic into SECURITY DEFINER functions, and then not
granting update rights to anybody other than the owner of those
functions? (Please don't say "because it blocks superusers too".
That's an anti-feature.)

I'm also slightly astonished by your choice to tie the implementation
to snapshots. If we do accept something like this, we most certainly
aren't going to do it like that.

regards, tom lane