Re: Truncate Triggers

"Simon Riggs" <simon@2ndquadrant.com> writes:

> On Fri, 2008-01-25 at 14:00 -0500, Tom Lane wrote:
>> Simon Riggs <simon@2ndquadrant.com> writes:
>> > On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote:
>> >> There are way too many table privilege bits already; to add more you
>> >> need something a lot stronger than a "might be nice" argument.
>> 
>> > People use TRUNCATE whatever we say. If you force people to be table
>> > owners or superusers you merely restrict their security options.
>> 
>> By that argument you could justify a separate privilege bit for anything
>> at all, eg, each sub-variant of ALTER TABLE.  
>
> I already made the argument that TRUNCATE is not similar to DDL in its
> effects or usage.
> http://archives.postgresql.org/pgsql-sql/2008-01/msg00093.php
>
>> Please present an actual
>> argument why TRUNCATE should get its own bit.
>
> Claiming my argument doesn't exist is itself a fairly weak defence...

Uhm, your argument seems to consist of:

| 1. Commands that alter the rows in the table
| e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group

I don't think saying "clearly" constitutes an argument.

The fact is that TRUNCATE is a big of a screw case. It can be seen either way.
A lot of users use it as a "cheaper form of delete" which would make it DML.
Except the whole reason it's cheaper is precisely because of the way it works
which is by being a closer analogy to a DROP and CREATE sequence of DDL.

This two-faced personality is just why we're facing this problem. It looks to
users like DML but it under the hood it behaves just like DDL.

I think there are two strategies here. Either we paper over the DDLishness by
making it look as much as possible like DML. It'll never be perfect but we'll
be as friendly as we can for users. That might just make the DDLishness pop up
at all the more surprising moments and be all that much harder to explain
though.

Or we can just declare it DDL and put a warning in the documentation that
while it may sound like it's DML it is in fact better thought of as a shortcut
for doing DROP and CREATE and should be used as such.

On the other hand perhaps it would be nice to have per-statement DDL triggers
in general. So you could, for example, update a data dictionary automatically
for simple cases like DROP TABLE.

Per-statement DDL triggers might also provide a more extensible way to enforce
unusual security policies. "Nobody's allowed to do any DDL except during a
maintenance window" for example.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!