file corruption goes undetected

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&gt;
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&gt;Linkedin <https://
nl.linkedin.com/in/bramvandervos>

--
Tomas Vondra

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