pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedfile corruption goes undetected
3+ messages / 3 participants
[nested] [flat]
* file corruption goes undetected
@ 2026-08-06 14:11 Bram van der Vos <bram.van.der.vos@axisinto.nl>
0 siblings, 1 reply; 3+ messages in thread
From: Bram van der Vos @ 2026-08-06 14:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
*ISSUE*
External modified datafile not detected, no error given but result produced;
*REPRODUCTION*
script.sh
---
psql <<DO_CLEAN_1
drop database corruption;
DO_CLEAN_1
rm -rf /tmp/oscmd.sh
rm rf /tmp/output_1.txt
rm rf /tmp/output_2.txt
psql <<END_SCRIPT
\o '/tmp/output_1.txt'
select version();
create database corruption;
\c corruption
show data_checksums;
show ignore_checksum_failure;
create table demo ( a serial, t timestamp with time zone);
insert into demo (t) values (now());
select max(t) from demo;
select pg_relation_filepath('demo');
copy (select command || setting ||'/'||file
from (select * from (select 1 as my_order, 'rm -rf ' as command,
pg_relation_filepath('demo') as file
union all
select 2 as my_order , 'touch ' as command ,
pg_relation_filepath('demo') as file )
cross join (select setting from pg_settings where
name='data_directory')) order by my_order) to '/tmp/oscmd.sh';
END_SCRIPT
cat /tmp/output_1.txt
chmod 700 /tmp/oscmd.sh
/tmp/oscmd.sh
psql -d corruption <<END_SCRIPT_2
\o '/tmp/output_2.txt'
select max(t) from demo;
END_SCRIPT_2
cat /tmp/output_2.txt
---
*ETC*
Result in tmp/output_1.txt en tmp/output_2.txt. After file has been
removed and recreated with touch a NULL result is returned. The same
happens when the has been replaced with dd -if /dev/zero of=<file>
bs=1024 count=64000
When replacing file with bogus data (/dev/random) an error is being
returned. Result when the datafile is being nullified
side info: pg_backrest does recognise the file as not valid an replaces
is during restore
regards
Bram
--
vrijdags afwezig
LOGO <https://www.axisintoict.nl;
Bram van der Vos
bram.van.der.vos@axisinto.nl
06 127 27 547
Albert Schweitzerlaan 10b
3451 EC Vleuten
Twitter <https://twitter.com/AxisintoICT>Linkedin
<https://nl.linkedin.com/in/bramvandervos;
Attachments:
[image/png] LOGO.png (4.1K, ../../943a83bf-78d1-4231-a9de-c24740e09d7a@axisinto.nl/3-LOGO.png)
download | view image
[image/png] TWITTER.png (966B, ../../943a83bf-78d1-4231-a9de-c24740e09d7a@axisinto.nl/4-TWITTER.png)
download | view image
[image/png] LINKEDIN.png (951B, ../../943a83bf-78d1-4231-a9de-c24740e09d7a@axisinto.nl/5-LINKEDIN.png)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: file corruption goes undetected
@ 2026-08-06 22:01 Tomas Vondra <tomas@vondra.me>
parent: Bram van der Vos <bram.van.der.vos@axisinto.nl>
0 siblings, 1 reply; 3+ messages in thread
From: Tomas Vondra @ 2026-08-06 22:01 UTC (permalink / raw)
To: Bram van der Vos <bram.van.der.vos@axisinto.nl>; pgsql-bugs@lists.postgresql.org
This is expected behavior, not a bug. It'd be great to detect these
kinds of data corruption, but data checksums can't do that (and we don't
have other protections).
regards
On 8/6/26 16:11, Bram van der Vos wrote:
> *ISSUE*
>
> External modified datafile not detected, no error given but result produced;
>
> *REPRODUCTION*
>
> script.sh
> ---
>
> psql <<DO_CLEAN_1
> drop database corruption;
> DO_CLEAN_1
>
> rm -rf /tmp/oscmd.sh
> rm rf /tmp/output_1.txt
> rm rf /tmp/output_2.txt
>
> psql <<END_SCRIPT
>
> \o '/tmp/output_1.txt'
> select version();
> create database corruption;
> \c corruption
> show data_checksums;
> show ignore_checksum_failure;
> create table demo ( a serial, t timestamp with time zone);
> insert into demo (t) values (now());
> select max(t) from demo;
> select pg_relation_filepath('demo');
> copy (select command || setting ||'/'||file
> from (select * from (select 1 as my_order, 'rm -rf ' as command,
> pg_relation_filepath('demo') as file
> union all
> select 2 as my_order , 'touch ' as command ,
> pg_relation_filepath('demo') as file )
> cross join (select setting from pg_settings where
> name='data_directory')) order by my_order) to '/tmp/oscmd.sh';
> END_SCRIPT
>
>
> cat /tmp/output_1.txt
> chmod 700 /tmp/oscmd.sh
> /tmp/oscmd.sh
>
> psql -d corruption <<END_SCRIPT_2
>
> \o '/tmp/output_2.txt'
> select max(t) from demo;
> END_SCRIPT_2
>
> cat /tmp/output_2.txt
> ---
>
> *ETC*
> Result in tmp/output_1.txt en tmp/output_2.txt. After file has been
> removed and recreated with touch a NULL result is returned. The same
> happens when the has been replaced with dd -if /dev/zero of=<file>
> bs=1024 count=64000
>
> When replacing file with bogus data (/dev/random) an error is being
> returned. Result when the datafile is being nullified
>
> side info: pg_backrest does recognise the file as not valid an replaces
> is during restore
>
>
> regards
>
>
> Bram
>
> --
> vrijdags afwezig
> LOGO <https://www.axisintoict.nl;
> Bram van der Vos
> bram.van.der.vos@axisinto.nl
> 06 127 27 547
> Albert Schweitzerlaan 10b
> 3451 EC Vleuten
> Twitter <https://twitter.com/AxisintoICT>Linkedin <https://
> nl.linkedin.com/in/bramvandervos>
>
--
Tomas Vondra
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: file corruption goes undetected
@ 2026-08-06 22:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tomas Vondra <tomas@vondra.me>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2026-08-06 22:09 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: Bram van der Vos <bram.van.der.vos@axisinto.nl>; pgsql-bugs@lists.postgresql.org
Tomas Vondra <tomas@vondra.me> writes:
> This is expected behavior, not a bug. It'd be great to detect these
> kinds of data corruption, but data checksums can't do that (and we don't
> have other protections).
I suspect what's really happening in this example is that the query
result is produced entirely from pages in shared buffers, so we don't
notice that the underlying disk files have been clobbered. We would
notice once we have occasion to actually read the junk data.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-08-06 22:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-06 14:11 file corruption goes undetected Bram van der Vos <bram.van.der.vos@axisinto.nl>
2026-08-06 22:01 ` Tomas Vondra <tomas@vondra.me>
2026-08-06 22:09 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox