Re: fsync bug faq for publication?

On 05/25/2015 11:09 PM, Magnus Hagander wrote:
>
> On May 26, 2015 07:31, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>>
>> Josh Berkus <josh(at)agliodbs(dot)com <mailto:josh(at)agliodbs(dot)com>> writes:
>> > We need to get a notice out to our users who might update their servers
>> > and get stuck behind the fsync bug. As such, I've prepared a FAQ.
>> > Please read, correct and improve this FAQ so that it's fit for us to
>> > announce to users as soon as possible:
>>
>> > https://wiki.postgresql.org/wiki/May_2015_Fsync_Permissions_Bug
>>
>> Judging by Ross Boylan's report at
>>
> http://www.postgresql.org/message-id/F1F13E14A610474196571953929C02096D0E97@ex08.net.ucsf.edu
>> it's not sufficient to just recommend "changing permissions" on the
>> problematic files. It's not entirely clear from here whether there is a
>> solution that both allows fsync on referenced files and keeps OpenSSL
>> happy; but if there is, it probably requires making the cert files be
>> owned by the postgres user, as well as adjusting their permissions to
>> be 0640 or thereabouts. I'm worried about whether that breaks other
>> services using the same cert files.
>>
>
> It almost certainly will.
>
> I think the recommendation has to be that if it's a symlink, it should
> be replaced with a copy of the file, and that copy be chown and chmod
> the right way.

Where did we get the idea that this issue only affects symlinked files?
On testing, any file which "postgres" doesn't have write permissions on
is affected:

root(at)d623471b11ee:/var/lib/postgresql/9.3/main# touch root_file.txt
root(at)d623471b11ee:/var/lib/postgresql/9.3/main# ls -l
total 60
-rw------- 1 postgres postgres 4 May 26 17:46 PG_VERSION
drwx------ 5 postgres postgres 4096 May 26 17:46 base
drwx------ 2 postgres postgres 4096 May 26 17:46 global
drwx------ 2 postgres postgres 4096 May 26 17:46 pg_clog
drwx------ 4 postgres postgres 4096 May 26 17:46 pg_multixact
drwx------ 2 postgres postgres 4096 May 26 17:46 pg_notify
drwx------ 2 postgres postgres 4096 May 26 17:46 pg_serial
drwx------ 2 postgres postgres 4096 May 26 17:46 pg_snapshots
drwx------ 2 postgres postgres 4096 May 26 17:47 pg_stat
drwx------ 2 postgres postgres 4096 May 26 17:46 pg_stat_tmp
drwx------ 2 postgres postgres 4096 May 26 17:46 pg_subtrans
drwx------ 2 postgres postgres 4096 May 26 17:46 pg_tblspc
drwx------ 2 postgres postgres 4096 May 26 17:46 pg_twophase
drwx------ 3 postgres postgres 4096 May 26 17:46 pg_xlog
-rw------- 1 postgres postgres 133 May 26 17:46 postmaster.opts
-rw-r--r-- 1 root root 0 May 26 17:49 root_file.txt
root(at)d623471b11ee:/var/lib/postgresql/9.3/main# service postgresql start
* Starting PostgreSQL 9.3 database server

[ OK ]
root(at)d623471b11ee:/var/lib/postgresql/9.3/main# ps aux | grep postgres
postgres 4627 0.2 0.4 244880 16100 ? S 17:49 0:00
/usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c
config_file=/etc/postgresql/9.3/main/postgresql.conf
postgres 4629 0.0 0.0 244880 1868 ? Ss 17:49 0:00
postgres: checkpointer process

postgres 4630 0.0 0.0 244880 1872 ? Ss 17:49 0:00
postgres: writer process

postgres 4631 0.0 0.0 244880 1648 ? Ss 17:49 0:00
postgres: wal writer process

postgres 4632 0.0 0.0 245632 2956 ? Ss 17:49 0:00
postgres: autovacuum launcher process

postgres 4633 0.0 0.0 100556 1768 ? Ss 17:49 0:00
postgres: stats collector process

root 4647 0.0 0.0 8860 648 ? S+ 17:49 0:00 grep
--color=auto postgres
root(at)d623471b11ee:/var/lib/postgresql/9.3/main# kill -9 4627
root(at)d623471b11ee:/var/lib/postgresql/9.3/main# service postgresql start
* Starting PostgreSQL 9.3 database server

* Removed
stale pid file.
The PostgreSQL server failed to start. Please check the log output:
2015-05-26 17:49:36 UTC [4676-1] LOG: database system was interrupted;
last known up at 2015-05-26 17:49:16 UTC
2015-05-26 17:49:36 UTC [4676-2] FATAL: could not open file
"/var/lib/postgresql/9.3/main/root_file.txt": Permission denied
2015-05-26 17:49:36 UTC [4675-1] LOG: startup process (PID 4676) exited
with exit code 1
2015-05-26 17:49:36 UTC [4675-2] LOG: aborting startup due to startup
process failure

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com