gem-hunter

This is a BOSH release for PostgreSQL.

• Deploying
• Customizing
• Contributing
• Known Limitation
• Upgrading
  • Migrating password authentication from MD5 to SCRAM-SHA-256
• CI

In order to deploy the postgres-release you must follow the standard steps for deploying software with BOSH.

1. Deploy and run a BOSH director. Please refer to BOSH documentation for instructions on how to do that. Bosh-lite specific instructions can be found here.

2. Install the BOSH command line Interface (CLI) v2+. Please refer to BOSH CLI documentation. Use the CLI to target your director.

3. Upload the desired stemcell directly to bosh. bosh.io provides a resource to find and download stemcells.

   # Example for bosh-lite
   bosh upload-stemcell https://bosh.io/d/stemcells/bosh-warden-boshlite-ubuntu-xenial-go_agent
   

4. Upload the latest release from bosh.io:

   bosh upload-release https://bosh.io/d/github.com/cloudfoundry/postgres-release
   

   or create and upload a development release:

   cd ~/workspace/postgres-release
   bosh -n create-release --force && bosh -n upload-release
   

5. Generate the manifest. You can provide in input an operation file to customize the manifest:

   ~/workspace/postgres-release/scripts/generate-deployment-manifest \
   -o OPERATION-FILE-PATH > OUTPUT_MANIFEST_PATH
   

   You can use the operation file to specify postgres job properties or to override the configuration if your BOSH director cloud-config is not compatible.

   This example operation file is a great starting point. Note: when using this operation file, you will need to inject pgadmin_database_password at bosh deploy-time, which is a good pattern for keeping credentials out of manifests.

   You are also provided with options to enable TLS in the PostgreSQL server or to use static ips.

6. Deploy:

   bosh -d DEPLOYMENT_NAME deploy OUTPUT_MANIFEST_PATH
   

   Example, injecting the pgadmin_database_password variable:

   bosh -d DEPLOYMENT_NAME deploy -v pgadmin_database_password=foobarbaz OUTPUT_MANIFEST_PATH
   

The table below shows the most significant properties you can use to customize your PostgreSQL installation. The complete list of available properties can be found in the spec.

Note

• Removing a database from databases.databases list and deploying again does not trigger a physical deletion of the database in PostgreSQL.
• Removing a role from databases.roles list and deploying again does not trigger a physical deletion of the role in PostgreSQL.

PostgreSQL has native support for using TLS connections to encrypt client/server communications for increased security. You can enable it by setting the databases.tls.certificate and the databases.tls.private_key properties.

A script is provided that creates a CA, generates a key pair, and signs it with the CA:

./scripts/generate-postgres-certs -n HOSTNAME_OR_IP_ADDRESS

The common name for the server certificate must be set to the DNS hostname if any or to the ip address of the PostgreSQL server. This because in TLS mode verify-full, the hostname is matched against the common-name. Refer to PostgreSQL documentation for more details.

You can also use BOSH variables to generate the certificates. See by way of example the operation file used by the manifest generation script.

~/workspace/postgres-release/scripts/generate-deployment-manifest \
   -s -h HOSTNAME_OR_IP_ADDRESS \
   -o OPERATION-FILE-PATH > OUTPUT_MANIFEST_PATH

In order to perform authentication using TLS client certificates, you must not specify a user password and you must configure the following properties:

• databases.tls.certificate
• databases.tls.private_key
• databases.tls.ca

The cn (Common Name) attribute of the certificate will be compared to the requested database user name, and if they match the login will be allowed.

Optionally you can map the common_name to a different database user by specifying property databases.roles[n].common_name.

A script is provided that creates a client certificates:

./scripts/generate-postgres-client-certs --ca-cert <PATH-TO-CA-CERT> --ca-key <PATH-TO-CA-KEY> --client-name <USER_NAME>

You can run custom code before or after PostgreSQL starts or stops or periodically. For details, see hooks documentation.

You can enable backup and restore through bbr by adding the bbr-postgres-db job with the postgres job and by setting its release_level_backup option to true. If enabled, a backup is collected using pg_dump for each database specified in the databases.databases property.

If you don't colocate the bbr-postgres-db with postgres then you must specify in the postgres.dbuser property a database user with enough permissions to run backup and restore.

If your PostgreSQL is configured with TLS, by default backup and restore are run with sslmode=verify-full. You can change it to sslmode=verify-ca by setting postgres.ssl_verify_hostname to false.

Caveats:

• Restore does not drop the database, the extensions, or the schema; therefore the schema of the restored and existing databases must be the same.
• If a backup is not present for one of the configured databases in the databases.databases property, the restore issues a message and continues.

Contributors must sign the Contributor License Agreement before their contributions can be merged. Follow the directions here to complete that process.

1. Fork the repository and make a local clone

2. Create a feature branch from the development branch

   cd postgres-release
   git checkout develop
   git checkout -b feature-branch

3. Make changes on your branch

4. Test your changes by running acceptance tests

5. Push to your fork (git push origin feature-branch) and submit a pull request selecting develop as the target branch. PRs submitted against other branches will need to be resubmitted with the correct branch targeted.

The postgres-release does not directly support high availability. Even if you deploy more instances, no replication is configured.

Refer to versions.yml in order to assess if a postgres-release version upgrades the PostgreSQL version.

⚠ Upcoming breaking change

The current default for databases.password_authentication_algorithm is md5 for backwards compatibility. A future release will change the default to scram-sha-256, which is the modern, cryptographically stronger algorithm recommended by the PostgreSQL project.

Why this matters

databases.password_authentication_algorithm controls both the password_encryption setting in postgresql.conf and the auth-method column in pg_hba.conf. If the value is changed to scram-sha-256 while existing role passwords are still stored as MD5 hashes, those roles will immediately fail to authenticate — clients will receive an error until every affected role's password has been reset so PostgreSQL can re-hash it with SCRAM.

Migration procedure (perform these steps in order)

1. Set the property in your deployment manifest:

   databases.password_authentication_algorithm: scram-sha-256

2. Re-deploy so the new postgresql.conf and pg_hba.conf are applied:

   bosh -d DEPLOYMENT_NAME deploy MANIFEST_PATH

3. Reset every role password so PostgreSQL re-hashes it with SCRAM. Connect as a superuser and run for each role:

   ALTER ROLE <name> WITH PASSWORD '<password>';

   Roles whose passwords are still stored as MD5 hashes cannot authenticate via SCRAM and will be locked out until this step is completed.

4. Verify that no MD5 hashes remain:

   SELECT rolname, rolpassword
   FROM pg_authid
   WHERE rolpassword NOT LIKE 'SCRAM-SHA-256$%'
     AND rolpassword IS NOT NULL;

   The query must return zero rows before the migration is complete.

The maintainers of the postgres-release test the following upgrade paths:

• From the previous postgres-release
• From the latest postgres-release that bumps the previous PostgreSQL version
• From the latest cf-deployment that bumps the previous PostgreSQL version

1. A copy of the database is made for the upgrade, you may need to adjust the persistent disk capacity of the postgres job.
   • For major upgrades the copy is always created
   • For minor upgrades the copy is created unless the databases.skip_data_copy_in_minor is set to true.
2. The upgrade happens as part of the pre-start and its duration may vary basing on your env.
   • In case of a PostgreSQL minor upgrade a simple copy of the old data directory is made.
   • In case of a PostgreSQL major upgrade the pg_upgrade utility is used.
3. Postgres will be unavailable during this upgrade.

PostgreSQL upgrade may require some post-upgrade processing. The administrator should check the /var/vcap/store/postgres/pg_upgrade_tmp directory for the generated script files and eventually run them. See pg_upgrade post-upgrade processing for more details.

In case a copy of the old database is kept (see considerations above), the old database is moved to /var/vcap/store/postgres/postgres-previous. The postgres-previous directory will be kept until the next postgres upgrade is performed in the future. You are free to remove this if you have verified the new database works and you want to reclaim the space.

In case of a long upgrade, the deployment may time out; anyway, bosh would not stop the actual upgrade process. In this case you can just wait for the upgrade to complete and, only when postgres is up and running, rerun the bosh deploy.

If the upgrade fails:

• The old data directory is still available at /var/vcap/store/postgres/postgres-x.x.x where x.x.x is the old PostgreSQL version
• The new data directory is at /var/vcap/store/postgres/postgres-y.y.y where y.y.y is the new PostgreSQL version
• If the upgrade is a PostgreSQL major upgrade:
  • A marker file is kept at /var/vcap/store/postgres/POSTGRES_UPGRADE_LOCK to prevent the upgrade from happening again.
  • pg_upgrade logs that may have details of why the migration failed can be found in /var/vcap/sys/log/postgres/postgres_ctl.log

If you want to attempt the upgrade again or to roll back to the previous release, you should remove the new data directory and, if present, the marker file.

The CI pipeline runs:

• the postgres-release acceptance tests
• the supported upgrade paths