-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Mon, 30 May 2016 19:49:36 +0200 (CEST), Daniel Westermann
(daniel.westermann@dbi-services.com) wrote about "Re: [GENERAL]
Deleting a table file does not raise an error when the table is
touched afterwards, why?" (in
<1247360337.5599235.1464630576430.JavaMail.zimbra@dbi-services.com>):
[snip]
> Thanks all for your answers. Maybe I should have provided more
> background information: We had an internal workshop today and one
> of the topics was backup/restore. One of the questions was what
> will happen if (for any reason) a file gets deleted so we tested
> it. I am aware that this is not the common use case. But still I
> want to understand why PostgreSQL works the way described. From the
> answers I understand this:
>
> - the file is not really deleted because PostgreSQL is still using
> it => correct?
It is correct.
> - if the above is correct why does PostgreSQL only write a partial
> file back to disk/wal?
PG only writes modified pages to WAL, even if another process has
requested the deletion of the tablespace file. In fact, PG is not even
aware of the deletion request.
> For me this still seems dangerous as potentially nobody will notice
> it
It is intrinsically dangerous, which is why only root and postgres
userids have write permissions on physical filesystems to be used by PG.
Indeed, if you use an alternative security model even root will not
have write permissions, only postgres.
> - PostgreSQL assumes that someone with write access to the files
> knows what she/he is doing. ok, but still, in the real world cases
> like this happen (for whatever reason)
It is very unlikely to happen once; it should never happen twice. No
competent DBA would do that once, let alone twice; if a DBA does that
once, he/she ceases to be a DBA.
The issue you are raising here is the misuse of filesystem
permissions. There is nothing PG can do here beyond what it already
does: check that the permissions mask and ownership are as tight as
possible during start-up of each tablespace.
This reminds me of the old music hall joke: a patient goes to the
doctor, raises his arm and says "Doc, it hurts when I do this!", to
which the doctor replies "Well don't do that."
Basically, it behoves your support staff to manage the physical
filesystems correctly and not damage them.
- --
Regards,
Dave [RLU #314465]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
david.w.noon@googlemail.com (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2
iEYEARECAAYFAldMiSMACgkQogYgcI4W/5T4UgCgzQQGvhdo+yxr7VSUPyzTJMa8
xAwAn3vPHQ4UOOhSL4kjCtl6Cq5sVeb0
=/lld
-----END PGP SIGNATURE-----