Using Crunchy PostgreSQL for PCF

This topic describes how to use Crunchy PostgreSQL for Pivotal Cloud Foundry (PCF) Tile.

Crunchy PostgreSQL Service

Crunchy PostgreSQL for Pivotal Cloud Foundry (PCF) Tile is an on-demand, single-tenant PostgreSQL database service.

Crunchy PostgreSQL comes with one service:

  • postgresql-9.5-odb

This service includes:

  • PostgreSQL cluster (primary and replicas)
  • Load balancer
  • pgBackrest dedicated backup
  • Consul cluster

PostgreSQL Plans

The postgresql-9.5-odb service comes with several plans:

  • small
  • medium
  • large
  • extra-large

Each plan corresponds to different server size set up by the operator when the service was deployed.

Review each plan offering and choose the correct database size for the application.

Viewing Service Plans in Cloud Foundry

Using Cloud Foundry CLI

Run cf marketplace -s postgresql-9.5-odb. This will display information about each service plan offered by the Crunchy service. Choose the plan that best fits your application requirements.

Using Pivotal Apps Manager

  1. Select Marketplace on the left navigation menu.
  2. Find and select Crunchy PostgreSQL in the list of available services.
  3. Browse the available service plans and choose the one that best fits your application requirements.

Creating PostgreSQL Service

Service Creation Parameters

The following service configuration parameters are supported during service creation.

Parameter Description Required? Example Value Parameter Limitations Notes
db_name Name of the service database Yes example_db Value must begin with a lowercase alpha character and may contain only lowercase alpha, numeric, and underscore characters.
db_username Name of the service database user Yes john_example Value must begin with a lowercase alpha character and may contain only lowercase alpha, numeric, and underscore characters. This user will be the owner of the service database and be granted all privileges on the service database.
owner_name Contact name for the service database Yes John Example
owner_email Contact email for the service database Yes john.example@fabrikam.com Must be a valid email address
compliance_profile Compliance logging level used in cluster Optional base_compliance Valid values are base_compliance, stig_compliance_full, or pgaudit_disabled. Specifying stig_compliance_full configures PgAudit as specified in the PostgreSQL STIG, which may impact performance. Specifying pgaudit_disabled disables PgAudit logging and is not recommended. The configuration details for each profile can be found here.
db_encoding Database encoding for the service database Optional UTF8
hba_addresses Addresses authorized to connect to the service database Optional 10.244.9.101/32 Value must be space separated string of addresses in CIDR format
postgis Enable PostGIS and related extensions Optional true Value must be boolean true or false Enabling PostGIS will install postgis, postgis_tiger_geocoder, postgis_topology, and fuzzystrmatch
plr Enables PL/R extensions Optional true Value must be boolean true or false
postgres_fdw Enables PostgreSQL Foreign Data Wrapper Optional true Value must be boolean true or false
redis_fdw Enables Redis Foreign Data Wrapper Optional true Value must be boolean true or false
monitoring Installs monitoring extensions and enables Prometheus data store Optional true Value must be boolean true or false
bloat_check_schedule Recurrence of database bloat checking Optional @weekly Value must follow cron-style syntax Only valid if monitoring is true
Optional PostgreSQL Settings Parameters

The default configuration settings for PostgreSQL attempt to apply the most tuned settings for each plan and service instance.

However, if further configuration is found to be necessary, the following settings can be overridden manually:

  • pgc_autovacuum_analyze_scale_factor
  • pgc_autovacuum_analyze_threshold
  • pgc_autovacuum_freeze_max_age
  • pgc_autovacuum_max_workers
  • pgc_autovacuum_vacuum_cost_delay
  • pgc_autovacuum_vacuum_scale_factor
  • pgc_autovacuum_vacuum_threshold
  • pgc_checkpoint_completion_target
  • pgc_data_directory
  • pgc_effective_io_concurrency
  • pgc_external_pid_file
  • pgc_hba_file
  • pgc_hot_standby
  • pgc_ident_file
  • pgc_listen_addresses
  • pgc_log_checkpoints
  • pgc_log_connections
  • pgc_log_destination
  • pgc_log_directory
  • pgc_log_disconnections
  • pgc_log_duration
  • pgc_log_filename
  • pgc_log_hostname
  • pgc_log_line_prefix
  • pgc_log_min_duration_statement
  • pgc_log_min_error_statement
  • pgc_log_min_messages
  • pgc_log_rotation_age
  • pgc_log_rotation_size
  • pgc_log_timezone
  • pgc_logging_collector
  • pgc_maintenance_work_mem
  • pgc_max_locks_per_transaction
  • pgc_max_wal_senders
  • pgc_max_wal_size
  • pgc_max_worker_processes
  • pgc_random_page_cost
  • pgc_ssl
  • pgc_stats_temp_directory
  • pgc_timezone
  • pgc_unix_socket_directories
  • pgc_wal_buffers
  • pgc_wal_keep_segments
  • pgc_wal_level
  • pgc_work_mem

Creating a New Service

Follow the steps below to create and bind a service instance of Crunchy PostgreSQL to use with your app.

Note: With an on-demand service, new servers are built and configured when a new service is created. Therefore, this step takes some time to complete. You may check the status of your service from the space dashboard in the Services tab. Ensure that the status of the service is create succeeded before proceeding.

  1. Create a service instance using the following command as a template:
cf create-service postgresql-9.5-odb small myService -c '
{
    "db_name": "exampledb",
    "db_username": "exampleuser",
    "owner_name": "Example User",
    "owner_email": "example.user@company.com"
}'
  1. Bind the service instance to your app:
cf bind-service <APP_NAME> <SERVICE_INSTANCE_NAME>
  1. Run the following commands to restage your app so that it can use the service:
cf restage <APP_NAME>

The service will then be available for use within any bound application.

Updating an On-Demand Service

Configuration Parameters

Standard Service Plans

The following service configuration parameters are supported during service update:

  • add_hba_addresses: A space separated list of IP addresses and/or networks in CIDR format that will be authorized to connect to the service database as the service user.
  • compliance_profile: The compliance level to use for the database cluster.
    • Accepted compliance_profile values are:
      • base_compliance
      • stig_compliance_full
      • pgaudit_disabled
    • The default and recommended value is base_compliance.
    • Specifying stig_compliance_full configures PgAudit as specified in the PostgreSQL STIG , which may impact performance.
    • Specifying pgaudit_disabled disables PgAudit logging and is not recommended.
    • The configuration details for each profile can be found here.
  • delete_hba_addresses: A space separated list of IP addresses and/or networks in CIDR format that will be removed from list of authorized addresses.

You can only remove addresses that have been added to the service through hba_addresses and/or add_hba_addresses.

Updating an Existing Service

Using Cloud Foundry CLI

To update an existing on-demand service instance, run cf update-service SERVICE_INSTANCE -c SERVICE_PARAMS, substituting SERVICE_INSTANCE with the name of your service, and SERVICE_PARAMS with a JSON object containing the parameters to update. For example:

cf update-service myService -c '{
    "add_hba_addresses": "192.168.0.24/32 192.168.1.0/24"
}'

New servers may be created and/or reconfigured when an on-demand service is updated. Therefore, this step will take some time to complete. You can check the status of your service my running cf services and looking for SERVICE_INSTANCE or with cf service SERVICE_INSTANCE.

Using Pivotal Apps Manager UI

  1. From the space dashboard, select the Services tab, then select your service.
  2. Select the Settings tab, then add any service update supported parameters in the fields under the Configure Instance section; click the + to create additional fields.
  3. Once the desired parameters have been added, click the Update button.

New servers may be created and/or reconfigured when an on-demand service is updated. Therefore, this step will take some time to complete. You can check the status service from the space dashboard, in the Services tab.

Integrating Applications

The following sections provide information on integrating an application with a newly created service.

Service Bindings and Service Keys

Service bindings are used for Cloud Foundry applications and services. When an application is bound to a service, the service credentials are automatically added to the VCAP_SERVICES environment variable within the application container. Unique credentials are generated for each service binding and are removed when the binding is deleted.

WARNING: Applications must obtain the service binding credentials dynamically from the application container environment. Do not use service binding credentials outside of a Cloud Foundry application. Instead, use service keys for this use case.

Service keys are used for external applications that need to use Cloud Foundry services. Creating a service key returns the service credentials JSON object, which can be used by the external application as appropriate.

Service Credentials

The following keys are available in the service credentials JSON object:

  • db_host: IP address for database write commands
  • db_name: database name
  • db_port: TCP port for database write commands
  • jdbc_read_uri: JDBC connection URI for database read commands; jdbc:postgresql://db_host:db_port/db_name
  • jdbc_uri: JDBC connection URI for database write commands; jdbc:postgresql://read_host:read_port/db_name
  • password: password for database user
  • read_host: IP address for database read commands
  • read_port: TCP port for database read commands
  • read_uri: standard postgresql connection URI for database read commands; postgresql://username:password@db_host:db_port/db_name
  • service_role: the database role that is granted privileges to the service instance database; this role can not be used to connect to the database directly
  • uri: standard postgresql connection URI for database write commands; postgresql://username:password@read_host:read_port/read_name
  • username: the database role used to connect to the service instance database; this role inherits privileges from service_role and is unique for each binding

The following keys are available if monitoring parameter was set to true during provisioning:

  • prometheus_host: host of the Prometheus server for monitoring data
  • prometheus_port: connection port for the Prometheus server
  • prometheus_username: username for accessing Prometheus
  • prometheus_password: password for accessing Prometheus

Database Roles

Service Role

The service role is created at the same time as the service instance. The name of the database role matches the value of the db_username parameter given to cf create-service. This role is granted ownership of the service instance database and the public schema. The service role is identified by the service_role key of the binding credentials object.

Note: The service role has the NOLOGIN attribute, therefore it can not be used directly to connect to the service instance database. The binding role should be used to connect to the database.

Binding Role

A binding role is created for each service binding. This ensures each binding or service key is a unique set of credentials. It is granted the service role and inherits the service role’s privileges. The binding role is identified by the username key of the binding credentials object.

Database Object Ownership

A database object is owned by the role that creates it. Database objects may only be altered or dropped by the owner or a role granted the owner’s role.

The binding role will own any database objects it creates. Privileges for these objects are granted to the service role. This allows subsequent binding roles to access an object with the same privileges as the binding role that created the object.

When a binding is destroyed, object ownership is transferred from the binding role to the service role. This allows subsequent binding roles to alter or drop database objects created by previous binding roles.

Services with multiple active bindings that create database objects may have objects owned by multiple roles. In this scenario, an attempt to alter or drop an object that is not owned by the current binding role or the service role may fail with an error similar to:

ERROR:  must be owner of ...

Therefore, it is recommended to create database objects as the service role instead of the binding role. This can be accomplished by using the SET ROLE command. For example:

SET ROLE <service_role>; CREATE TABLE t (id SERIAL, text TEXT);

If this is not possible, SET ROLE may be used when an object needs to be altered or dropped:

SET ROLE <service_role>; ALTER TABLE t ADD COLUMN moretext TEXT;

Accessing the Primary Database Server

To access the primary database server, which should be used for database write operations, use the db_host and db_port keys to construct a connection string. The uri and jdbc_uri may also be used to connect to the primary database server when supported by the client application or library.

Accessing the Replica Database Server(s)

To access the replica database server(s), which should be used for database read operations, use the read_host and read_port keys to construct a connection string. The read_uri and jdbc_read_uri may also be used to connect to the replica database server(s) when supported by the client application or library.

Binding a PCF App to a Service

Using Cloud Foundry CLI

When the status of your service is create succeeded you can bind an app to the service by running cf bind-service APP_NAME SERVICE_INSTANCE, substituting APP_NAME with the name of your PCF app and SERVICE_INSTANCE with the name of your service. For example:

cf bind-service myApp myService
cf restage myApp

You must restage or restart your app before it will have access to the service credentials.

Using Pivotal Apps Manager

  1. From the space dashboard, select the Services tab, then select your service.
  2. Select the Overview tab, then click the Bind Apps button.
  3. Under the Bound Apps section, tick the checkbox for the app you would like to bind to your service, then click Save.

Unbinding a PCF App from a Service

Using Cloud Foundry CLI

To unbind your app from your service, run cf unbind-service APP_NAME SERVICE_INSTANCE, substituting APP_NAME with the name of your PCF app and SERVICE_INSTANCE with the name of your service. For example:

cf unbind-service myApp myService

Using Pivotal Apps Manager

  1. From the space dashboard, select the Services tab, then select your service.
  2. Select the Overview tab, then click the Bind Apps button.
  3. Under the Bound Apps section, click the Edit Bindings button, untick the checkbox for the app you would like to unbind from your service, then click Save.

Managing Service Keys

Creating a Service Key

To create a service key for an external application, run cf create-service-key SERVICE_NAME SERVICE_KEY_NAME, substituting SERVICE_NAME with the name of your service and SERVICE_KEY_NAME with a name for the service key, e.g. SERVICE_NAME_sk. The service credentials JSON object can be viewed by running cf service-key SERVICE_NAME SERVICE_KEY_NAME. For example:

cf create-service-key myService myService_sk
cf service-key myService myService_sk

Deleting a Service Key

To delete a service key, run cf delete-service-key SERVICE_NAME SERVICE_KEY_NAME, substituting SERVICE_NAME with the name of your service and SERVICE_KEY_NAME with the name of the service key. For example:

cf delete-service-key myService myService_sk

Deleting a Service

WARNING: This is a destructive process and will destroy the entire service instance, including your database and backups. Ensure that you export any data you need before performing this step.

Using Cloud Foundry CLI

Note: You are required to unbind a service before the service can be deleted.

To delete your service instance, run cf delete-service SERVICE_INSTANCE, substituting SERVICE_INSTANCE with the name of your service. For example:

cf delete-service myService

Using Pivotal Apps Manager

  1. From the space dashboard, select the Services tab, then select your service.
  2. Select the Settings tab, then click the Delete Service Instance button.

Appendix

Security and Compliance

The PostgreSQL Audit Extension (PgAudit) provides detailed session and/or object audit logging via the standard PostgreSQL logging facility. The goal is to provide Crunchy PostgreSQL users with capability to produce audit logs that comply with ICS 500-27 requirements.

WARNING: Depending on settings, it is possible for PgAudit to generate an enormous volume of logging. If log levels are set too high, disk space and other system resources may become overutilized, which can have a negative impact on PostgreSQL performance and stability. Therefore, it is important to determine your exact audit requirements to avoid logging too much.

The following table provides more information on the configuration provided for each compliance profile.

  • Profile is the name of the compliance_profile.
  • pgaudit.log is the value(s) for pgaudit.log, which determine the type of statements that are captured in the audit log.
    • READ: SELECT and COPY when the source is a relation or a query.
    • WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
    • FUNCTION: Function calls and DO blocks.
    • ROLE: Statements related to roles and privileges: GRANT, REVOKE, or CREATE, ALTER, and DROP ROLE.
    • DDL: All DDL that is not included in the ROLE class.
    • MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM.
Profile pgaudit.log
base_compliance ddl, role, misc
stig_compliance_full ddl, role, misc, read, write
pgaudit_disabled N/A

pgBackrest Documentation

For more information about pgBackrest restorations, please refer to the official PGBackRest documentation.

Create a pull request or raise an issue on the source for this page in GitHub