Re: PostgreSQL 9.1 "database system was interrupted; last known up at" - Mailing list pgsql-admin

Thanks for your reply.
1. Yes, I updated to 9.1.16.
2. Changing the permission of the snakeoil file makes produced a similar error about the private key.
Changing the permission of the private key produced an error that the key no longer had valid permissions.
3. So is the problem reading the certificate my only real problem?

Details:
1. Since security backports do not necessarily include all upstream changes, here's the changelog.Debian entry for the
lastupdate: 

postgresql-9.1 (9.1.16-0+deb7u1) wheezy-security; urgency=medium

  * New upstream version.

    + Avoid possible crash when client disconnects just before the
      authentication timeout expires (Benkocs Norbert Attila)

      If the timeout interrupt fired partway through the session shutdown
      sequence, SSL-related state would be freed twice, typically causing a
      crash and hence denial of service to other sessions.  Experimentation
      shows that an unauthenticated remote attacker could trigger the bug
      somewhat consistently, hence treat as security issue. (CVE-2015-3165)

    + Improve detection of system-call failures (Noah Misch)

      Our replacement implementation of snprintf() failed to check for errors
      reported by the underlying system library calls; the main case that
      might be missed is out-of-memory situations. In the worst case this
      might lead to information exposure, due to our code assuming that a
      buffer had been overwritten when it hadn't been. Also, there were a few
      places in which security-relevant calls of other system library
      functions did not check for failure.

      It remains possible that some calls of the *printf() family of functions
      are vulnerable to information disclosure if an out-of-memory error
      occurs at just the wrong time.  We judge the risk to not be large, but
      will continue analysis in this area. (CVE-2015-3166)

    + In contrib/pgcrypto, uniformly report decryption failures as Wrong key
      or corrupt data (Noah Misch)

      Previously, some cases of decryption with an incorrect key could report
      other error message texts.  It has been shown that such variance in
      error reports can aid attackers in recovering keys from other systems.
      While it's unknown whether pgcrypto's specific behaviors are likewise
      exploitable, it seems better to avoid the risk by using a
      one-size-fits-all message. (CVE-2015-3167)

  * Repository moved to git, update Vcs headers.

 -- Christoph Berg <christoph.berg@credativ.de>  Thu, 21 May 2015 16:35:28 +0200

2. Note Debian has a wrapper to support running multiple clusters of postgres at multiple versions of the database.
So the command below has the version (9.1) and cluster name (main) as arguments.
root@tempserver:/etc# date; pg_ctlcluster 9.1 main start
Mon May 25 21:25:29 PDT 2015
The PostgreSQL server failed to start. Please check the log output:
2015-05-25 21:25:29 PDT LOG:  database system was interrupted; last known up at 2015-05-22 09:22:25 PDT
2015-05-25 21:25:30 PDT LOG:  incomplete startup packet
2015-05-25 21:25:30 PDT FATAL:  could not open file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
2015-05-25 21:25:30 PDT LOG:  startup process (PID 28524) exited with exit code 1
2015-05-25 21:25:30 PDT LOG:  aborting startup due to startup process failure
root@tempserver:/etc# ls -l /etc/ssl/certs/ssl-cert-snakeoil.pem  # previously changed group and group write
-rw-rw-r-- 1 root postgres 1005 Apr 22  2013 /etc/ssl/certs/ssl-cert-snakeoil.pem
root@tempserver:/etc# ls -ld /etc/ssl/certs
drwxr-xr-x 3 root root 22528 Oct 18  2014 /etc/ssl/certs
# Realize that the "could not open file" is different from original error.  Try to adjust.
root@tempserver:/etc# ls -l /etc/ssl/private/ssl-cert-snakeoil.key
-rw-r----- 1 root ssl-cert 1704 Apr 22  2013 /etc/ssl/private/ssl-cert-snakeoil.key
root@tempserver:/etc# grep ssl-cert /etc/group
ssl-cert:x:118:postgres
root@tempserver:/etc# date; chmod g+w /etc/ssl/private/ssl-cert-snakeoil.key
Mon May 25 21:37:45 PDT 2015
root@tempserver:/etc# ls -l /etc/ssl/private/ssl-cert-snakeoil.key
-rw-rw---- 1 root ssl-cert 1704 Apr 22  2013 /etc/ssl/private/ssl-cert-snakeoil.key
root@tempserver:/etc# date; pg_ctlcluster 9.1 main start
Mon May 25 21:37:58 PDT 2015
The PostgreSQL server failed to start. Please check the log output:
2015-05-25 21:37:58 PDT FATAL:  private key file "server.key" has group or world access
2015-05-25 21:37:58 PDT DETAIL:  File must be owned by the database user or root, must have no write permission for
"group",and must have no permissions for "oth\ 
er".


Ross


________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: Monday, May 25, 2015 7:15 PM
To: Boylan, Ross
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PostgreSQL 9.1 "database system was interrupted; last known up at"

"Boylan, Ross" <Ross.Boylan@ucsf.edu> writes:
> I had to power cycle my system because it became unresponsive.  Now PosgtreSQL will not start.  I would like advice
abouthow to proceed; I think pg_resetxlog is my next step.  I have made a copy of the current database files. 
> <log file="postgresql-9.1-main.log">
> 2015-05-25 10:44:21 PDT LOG:  database system was interrupted; last known up at 2015-05-22 09:22:25 PDT
> 2015-05-25 10:44:21 PDT LOG:  incomplete startup packet
> 2015-05-25 10:44:21 PDT FATAL:  could not open file "/etc/ssl/certs/ssl-cert-snakeoil.pem": Permission denied

You did not say, but I'm betting you recently updated to last week's
release, 9.1.16?

If so, this is an unforeseen side-effect of a patch to forcibly fsync
everything in the PG data directory after a crash, see
http://www.postgresql.org/message-id/flat/20150523172627.GA24277@msg.df7cb.de

We're debating how to fix it for real, but in the meantime the easiest
workaround is to change the permissions on that file so it's writable by
Postgres.  (Yeah, I know, ick.  Make a note to undo that after we fix
the fsync'ing logic.)

                        regards, tom lane