Re: Why does TRUNCATE require a special privilege?

Those operations do different things, sure.

But from a roles/privilege framework perspective, why would you want to give
certain users the DELETE privilege whereas others you want to give them
only the TRUNCATE privilege?

Are we saying to a user that "You need a different level of privilege because
you are about to cause a MVCC-unsafe operation?".

Or is the privilege framework simply ruling "Do different things, have
different permissions"?

Marcelo.

On Sat, Jan 17, 2026 at 5:46 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 1/16/26 02:32, Dominique Devienne wrote:
> > On Fri, Jan 16, 2026 at 10:13 AM Marcelo Fernandes <marcefern7(at)gmail(dot)com> wrote:
> >> From the documentation:
> >>> TRUNCATE quickly removes all rows from a set of tables. It has the same
> >>> effect as an unqualified DELETE on each table, but since it does not actually
> >>> scan the tables it is faster.
> >>> (...)
> >>> You must have the TRUNCATE privilege on a table to truncate it.
> >>
> >> Granted that TRUNCATE and DELETE are different operations under the hood, but
> >> why would the TRUNCATE operation require its own specific privilege rather than
> >> say, use the same privilege as the DELETE operation?
> >
> > It's kinda obvious, when you read the notes.
> >
> > 1) Not MVCC-safe.
> > 2) Do not fire TRIGGERs, thus breaking data-integrity
>
> It will not fire ON DELETE triggers, it will fire ON TRUNCATE triggers.
>
> > 3) "Viral" in the presence of FKs, i.e. related tables must also be TRUNCATEd
>
> Only if you add the CASCADE option, or TRUNCATE them in the same
> command. Otherwise it will fail.
>
> >
> > Just these 3 are HUGE departures from a DELETE. --DD
>
> I would add from:
>
> https://www.postgresql.org/docs/current/sql-truncate.html
>
> "TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates
> on, which blocks all other concurrent operations on the table. When
> RESTART IDENTITY is specified, any sequences that are to be restarted
> are likewise locked exclusively. If concurrent access to a table is
> required, then the DELETE command should be used instead."
>
> and
>
> ""
> When RESTART IDENTITY is specified, the implied ALTER SEQUENCE RESTART
> operations are also done transactionally; that is, they will be rolled
> back if the surrounding transaction does not commit. Be aware that if
> any additional sequence operations are done on the restarted sequences
> before the transaction rolls back, the effects of these operations on
> the sequences will be rolled back, but not their effects on currval();
> that is, after the transaction currval() will continue to reflect the
> last sequence value obtained inside the failed transaction, even though
> the sequence itself may no longer be consistent with that. This is
> similar to the usual behavior of currval() after a failed transaction."
>
>
> >
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com