public inbox for [email protected]  

help / color / mirror / Atom feed

From: Marcelo Fernandes <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Dominique Devienne <[email protected]>
Cc: [email protected]
Subject: Re: Why does TRUNCATE require a special privilege?
Date: Sat, 17 Jan 2026 10:15:18 +1300
Message-ID: <CAM2F1VMb3VyRXGMGC51mo+PLaB=vRZQD=7YGyOo-z=XXpX8MHg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAM2F1VNkovvL_56K_6OJiYU8toUdEG1Jk7ABRQ426Geh8LMB+g@mail.gmail.com>
	<CAFCRh-9HTzAU7ia--Uad2-KnZv=zXeFaG0iT2ut0KXGEZhsHuw@mail.gmail.com>
	<[email protected]>

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 <[email protected]> wrote:
>
> On 1/16/26 02:32, Dominique Devienne wrote:
> > On Fri, Jan 16, 2026 at 10:13 AM Marcelo Fernandes <[email protected]> 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
> [email protected]

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Why does TRUNCATE require a special privilege?
  In-Reply-To: <CAM2F1VMb3VyRXGMGC51mo+PLaB=vRZQD=7YGyOo-z=XXpX8MHg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox