Using the Multi-Tenant Service

The multi-tenant service is a cluster of pre-built and pre-configured servers. All databases created within the same multi-tenant service share the same system resources, i.e. disk space, CPU, and memory. Applications are only limited by the number of connections granted to the database. Please be conscientious of your database and application resource usage.

Since the systems are deployed prior to creating the service database, creating a multi-tenant service is significantly faster than an on-demand service, providing a quick test for integrating with the DBaaS interface.

Choosing a Service and Service Plan

Viewing Services in Cloud Foundry

Using Cloud Foundry CLI

Run cf marketplace -s SERVICE_NAME, where SERVICE_NAME is one of the following values:

• crunchy-postgresql-9.5-code-a-thon
• crunchy-postgresql-9.5-development
• crunchy-postgresql-9.5-production

The output of the above command will display information about the service plans offered by each 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 the select Service Name in the list of available services, where SERVICE_NAME is one of the following values:
   • Crunchy PostgreSQL 9.5 Shared Code-A-Thon Cluster
   • Crunchy PostgreSQL 9.5 Shared Development Cluster
   • Crunchy PostgreSQL 9.5 Shared Production Cluster
3. Browse the available service plans and choose the one that best fits your application requirements.

Creating a Multi-Tenant Service

Configuration Parameters

The following service configuration parameters are supported during service creation.

Required Parameters

• db_name: Name of the service database. Value must begin with a lowercase alpha character and contain only lowercase alpha, numeric, and underscore characters.
• db_username: Name of the service database user. This user will be the owner of the service database and be granted all privileges on the service database. Value must begin with a lowercase alpha character and contain only lowercase alpha, numeric, and underscore characters.
• owner_name: Contact name for the service database.
• owner_email: Contact email for the service database.

Optional Parameters

• db_encoding: Database encoding for the service database. Default is UTF8.
• postgis: Enable PostGIS extensions, true or false. Extensions include postgis, postgis_tiger_geocoder, postgis_topology, and fuzzystrmatch. Default is false.

Creating a New Service

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

1. Create a service instance named myService using the following command as a template: cf create-service crunchy-postgresql-9.5-code-a-thon small-test-10 myService -c '{ "db_name": "exampledb", "db_username": "exampleuser", "owner_name": "Example User", "owner_email": "example.user@company.com" }'

2. Bind the service instance to your app: cf bind-service <APP_NAME> myService

3. 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.

A more advanced example, using custom plans:

cf create-service crunchy-postgresql-9.5-code-a-thon custom customService -c '{
    "db_name": "exampledb",
    "db_username": "exampleuser",
    "owner_name": "Example User",
    "owner_email": "example.user@company.com",
    "server_type": "crunchy-small",
    "num_of_replicas": 2,
    "disk_size": "64GB",
    "allow_remote_replication": true,
    "db_connection_limit": 200
}'

Further, in instances where " chars might cause issues in the command prompt (such as with Windows CMD), instances where consistent service parameters are used, or in instances where private data would be passed in the parameters, it is possible to create a separate JSON file with all of the parameters to be used in service creation.

Create a JSON file with required values: param.json

{
        "db_name": "testdb",
        "db_username": "testdb",
        "owner_name": "Example User",
        "owner_email": "example.user@company.com",
        "super_secret_value": "dont_tell_anyone"
}

Then use this file as the JSON argument in the service creation:

cf create-service crunchy-postgresql-9.5-code-a-thon small-test-10 myService -c param.json

Using Pivotal Apps Manager

1. After selecting the appropriate plan, fill in the Instance Name, Add to Space, and Bind to App fields under Configure Instance.
2. Click Show Advanced Options, then enter the required parameters and values for the service plan you selected; click the + to create additional fields. Optional parameters and values may also be entered at this time.
3. Once you have entered all parameters, click Add to create your service instance.

Integrating Applications with an On-Demand Service

Service Binding vs. Service Key

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_id: service instance identifier
• 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 in cluster plans:

• dashboard_user: username for the dashboard application
• dashboard_password: password for the dashboard application

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, so it cannot 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 like the one below:

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. For more information, see Selectively Accessing Clusters.

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. For more information, see Selectively Accessing Clusters.

Binding a PCF App to an On-Demand 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

Binding parameters can be passed in as the binding is created. For example:

cf bind-service myApp myService -c '{"parameter": value}'
cf restage myApp

The acceptable parameters are as follows:

Optional Binding Parameters

Parameter	Description	Example Value	Parameter Limitations	Notes
use_md5_auth	Enables MD5 auth methods	true	Value must be boolean true or false	Can be used if the app does not support SCRAM-SHA-256 authentication
stats_access	Allow users to create service bindings with superuser visibility of pg_stat_activity	true	Value must be boolean true or false	Access must also be enabled in the service instance
remote_replica	Properly configure connection for a standalone remote replica instance	true	Value must be boolean true or false	Only valid on standalone-replica instances

If access to stats is enabled in your service instance, you can optionally enable this feature at binding time. With stats enabled you will be able to access the pg_stat_activity and pg_stat_statements views that are created in your service database. You can access these tables by calling SELECT * FROM pg_stat_activity in the Crunchy PostgreSQL 10 tile and SELECT * FROM monitor.pg_stat_activity() in the Crunchy PostgreSQL 9.5 tile.

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

Note: MD5 Authentication is considered cryptographically broken. Any and all efforts should be made to utilize SCRAM-SHA-256 authentication where possible.

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 an On-Demand 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, go to the Services tab.
2. Select your service.
3. Go to the Overview tab.
4. Click Bind Apps.
5. Under the Bound Apps section, click the Edit Bindings button and disable the checkbox for the app you want to unbind from your service.
6. 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, such as 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

Service Keys presently accept one optional parameter:

• “remote_replica”: true

The create-service-key command expects these parameters as JSON:

cf create-service-key myService myService_sk -c '{"remote_replica": true}'

Note: Service key creation is not currently supported for `standalone-replica` plan types.

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 Multi-Tenant 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.