MIME-Version: 1.0
References: <CAM2F1VNkovvL_56K_6OJiYU8toUdEG1Jk7ABRQ426Geh8LMB+g@mail.gmail.com>
 <CAFCRh-9HTzAU7ia--Uad2-KnZv=zXeFaG0iT2ut0KXGEZhsHuw@mail.gmail.com> <9d1c1ee2-7488-4efd-8451-5d5b3cabe7f2@aklaver.com>
In-Reply-To: <9d1c1ee2-7488-4efd-8451-5d5b3cabe7f2@aklaver.com>
From: Marcelo Fernandes <marcefern7@gmail.com>
Date: Sat, 17 Jan 2026 10:15:18 +1300
Message-ID: <CAM2F1VMb3VyRXGMGC51mo+PLaB=vRZQD=7YGyOo-z=XXpX8MHg@mail.gmail.com>
Subject: Re: Why does TRUNCATE require a special privilege?
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Dominique Devienne <ddevienne@gmail.com>, pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAM2F1VMb3VyRXGMGC51mo%2BPLaB%3DvRZQD%3D7YGyOo-z%3DXXpX8MHg%40mail.gmail.com>
Precedence: bulk

Those operations do different things, sure.

But from a roles/privilege framework perspective, why would you want to giv=
e
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 becau=
se
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=E2=80=AFAM Adrian Klaver <adrian.klaver@aklave=
r.com> wrote:
>
> On 1/16/26 02:32, Dominique Devienne wrote:
> > On Fri, Jan 16, 2026 at 10:13=E2=80=AFAM Marcelo Fernandes <marcefern7@=
gmail.com> wrote:
> >>  From the documentation:
> >>> TRUNCATE quickly removes all rows from a set of tables. It has the sa=
me
> >>> 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 ho=
od, but
> >> why would the TRUNCATE operation require its own specific privilege ra=
ther 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 TRU=
NCATEd
>
> 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.klaver@aklaver.com