public inbox for [email protected]help / color / mirror / Atom feed
Re: Why does TRUNCATE require a special privilege? 5+ messages / 4 participants [nested] [flat]
* Re: Why does TRUNCATE require a special privilege? @ 2026-01-16 10:32 Dominique Devienne <[email protected]> 2026-01-16 16:46 ` Re: Why does TRUNCATE require a special privilege? Adrian Klaver <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Dominique Devienne @ 2026-01-16 10:32 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; +Cc: [email protected] 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 3) "Viral" in the presence of FKs, i.e. related tables must also be TRUNCATEd Just these 3 are HUGE departures from a DELETE. --DD ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why does TRUNCATE require a special privilege? 2026-01-16 10:32 Re: Why does TRUNCATE require a special privilege? Dominique Devienne <[email protected]> @ 2026-01-16 16:46 ` Adrian Klaver <[email protected]> 2026-01-16 21:15 ` Re: Why does TRUNCATE require a special privilege? Marcelo Fernandes <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Adrian Klaver @ 2026-01-16 16:46 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; Marcelo Fernandes <[email protected]>; +Cc: [email protected] 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] ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why does TRUNCATE require a special privilege? 2026-01-16 10:32 Re: Why does TRUNCATE require a special privilege? Dominique Devienne <[email protected]> 2026-01-16 16:46 ` Re: Why does TRUNCATE require a special privilege? Adrian Klaver <[email protected]> @ 2026-01-16 21:15 ` Marcelo Fernandes <[email protected]> 2026-01-16 22:11 ` Re: Why does TRUNCATE require a special privilege? Adrian Klaver <[email protected]> 2026-01-16 23:14 ` Re: Why does TRUNCATE require a special privilege? Tom Lane <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Marcelo Fernandes @ 2026-01-16 21:15 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Dominique Devienne <[email protected]>; [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] ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why does TRUNCATE require a special privilege? 2026-01-16 10:32 Re: Why does TRUNCATE require a special privilege? Dominique Devienne <[email protected]> 2026-01-16 16:46 ` Re: Why does TRUNCATE require a special privilege? Adrian Klaver <[email protected]> 2026-01-16 21:15 ` Re: Why does TRUNCATE require a special privilege? Marcelo Fernandes <[email protected]> @ 2026-01-16 22:11 ` Adrian Klaver <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Adrian Klaver @ 2026-01-16 22:11 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; +Cc: Dominique Devienne <[email protected]>; [email protected] On 1/16/26 13:15, Marcelo Fernandes wrote: > 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?". The thing I see as difference worthy of separation is: "CASCADE Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE. " Now the same end result can be done with: delete from some_table: where the FK's pointing at some_table have ON CASCADE DELETE. The difference being a DBA has the option of creating the FKs with ON CASCADE NO ACTION which would throw an error. In other words you can prevent an unconstrained DELETE on some_table from removing all the child records. With TRUNCATE ... CASCADE, you cannot, the potential for harm is greater. > > Or is the privilege framework simply ruling "Do different things, have > different permissions"? > > Marcelo. > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why does TRUNCATE require a special privilege? 2026-01-16 10:32 Re: Why does TRUNCATE require a special privilege? Dominique Devienne <[email protected]> 2026-01-16 16:46 ` Re: Why does TRUNCATE require a special privilege? Adrian Klaver <[email protected]> 2026-01-16 21:15 ` Re: Why does TRUNCATE require a special privilege? Marcelo Fernandes <[email protected]> @ 2026-01-16 23:14 ` Tom Lane <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Tom Lane @ 2026-01-16 23:14 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Dominique Devienne <[email protected]>; [email protected] Marcelo Fernandes <[email protected]> writes: > 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?". Personally I think that's a plenty good enough reason ;-). The very different locking level is another good reason. TRUNCATE will block all other activity on the table, while DELETE doesn't. However, looking at our git history, it appears that TRUNCATE was originally only permitted to the table owner. The separate permission bit was added (years later) so that the owner could grant out the ability to others, without doing anything as non-backwards-compatible as reinterpreting what operations a DELETE grant allows. regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-01-16 23:14 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-01-16 10:32 Re: Why does TRUNCATE require a special privilege? Dominique Devienne <[email protected]> 2026-01-16 16:46 ` Adrian Klaver <[email protected]> 2026-01-16 21:15 ` Marcelo Fernandes <[email protected]> 2026-01-16 22:11 ` Adrian Klaver <[email protected]> 2026-01-16 23:14 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox