Default SSL certificates installed as symlinks and incompatible with pg_basebackup

[Stuart Bishop]

A default PostgreSQL installation creates server.crt and server.key symlinks in the datadir, referencing the default snakeoil SSL certificate.

When making a filesystem level backup of the database using pg_basebackup, these symlinks are not backed up and pg_basebackup emits the following warnings:

    WARNING: skipping special file "./server.crt"
    WARNING: skipping special file "./server.key"

Recovering the filesystem level backup thus requires the extra platform specific step of repairing the two missing files:

    cd ~/9.1/main
    ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem .
    ln -s /etc/ssl/private/ssl-cert-snakeoil.key .

To fix this, the locations of these files could be specified with the ssl_cert_file and ssl_key_file options in postgresql.conf.

Alternatively, the files could be copied rather than symlinked. However, the SSL certificate and private key should probably not be part of the backup.

ProblemType: Bug
DistroRelease: Ubuntu 13.04
Package: postgresql 9.1+140
ProcVersionSignature: Ubuntu 3.8.0-29.42-generic 3.8.13.5
Uname: Linux 3.8.0-29-generic x86_64
NonfreeKernelModules: nvidia
ApportVersion: 2.9.2-0ubuntu8.3
Architecture: amd64
Date: Thu Aug 29 15:40:03 2013
EcryptfsInUse: Yes
InstallationDate: Installed on 2013-02-26 (184 days ago)
InstallationMedia: Ubuntu 13.04 "Raring Ringtail" - Alpha amd64 (20130225)
MarkForUpload: True
PackageArchitecture: all
SourcePackage: postgresql-common
UpgradeStatus: No upgrade log present (probably fresh install)

[Martin Pitt (pitti)]

> To fix this, the locations of these files could be specified with the ssl_cert_file and ssl_key_file options in postgresql.conf.

This is actually what happens since 9.2 and later. But older versions didn't have these options yet, so 9.1 and earlier still use symlinks. So this got fixed in

postgresql-common (142) unstable; urgency=low

  [ Christoph Berg ]
  * Make all scripts honor PGSYSCONFDIR (defaulting to
    /etc/postgresql-common).
  * The default behavior of pg_createcluster can be configured in
    /etc/postgresql-common/createcluster.conf. This also allows to disable the
    creation of "main" clusters when postgresql server packages are installed,
    and to set parameters in the new postgresql.conf.
  * pg_createcluster: Move setting of log_line_prefix to createcluster.conf.
  * debian/postgresql-common.postgresql.init: Do not die of one cluster fails
    to start. (Closes: #699911)
  * pg_checksystem: Suppress error message for unavailable filesystems.
    (Closes: #705219)
  * pg_upgradecluster: Use a distinct name (pg_hba.conf.pg_upgradecluster) for
    the pg_hba.conf backup, and handle the case where this file already exists
    gracefully.
  * pg_upgradecluster: On upgrades to 9.3, rename unix_socket_directory to
    unix_socket_directories.
  * pg_upgradecluster, t/043_upgrade_ssl_cert.t: Copy server.crt and friends
    in the data directory on upgrade. (Closes: #698958)
  * pg_ctlcluster: Set LANG so non-ascii chars in the server log are not
    replaced by '?'. Thanks to Adrian Vondendriesch for help debugging this.
    (Closes: #671915)

  [ Martin Pitt ]
  * debian/supported-versions: Add 9.3 for testing/unstable.
  * debian/supported-versions: Add Ubuntu 13.10.
  * Bump Standards-Versio to 3.9.4 (no changes necessary).
  * pg_upgradecluster: For upgrades to 9.3, migrate "replication_timeout" to
    "wal_sender_timeout".
  * t/060_obsolete_confparams.t: Add full 9.2 configuration, to test 9.2 → 9.3
    upgrades.

 -- Martin Pitt <email address hidden> Tue, 07 May 2013 11:11:58 +0200