This topic gives you reference information about the Google PostgreSQL service (csb-google-postgres). It details the plans, configuration parameters, and binding credentials.

Plans

A default plan becomes available when installing the tile. This plan is for reference only, and is not intended for production use. Remove this default plan and create plans that fit your requirements.

Plan Configuration Parameters

When configuring Cloud Service Broker for GCP you can add additional plans. For how to configure plans, see Configure Services with Cloud Service Broker for GCP.

The following table lists parameters which can be configured at a plan level:

Parameter Name Values Default Required
name The plan name n/a Yes
id A unique GUID n/a Yes
description Description of the new plan n/a Yes
display_name Name to use when displaying plan in Marketplace n/a No
free When false, Service Instances of this Service Plan have a cost true No
bindable Specifies whether Service Instances of the Service Plan can bind to applications true No
plan_updateable Whether the Plan supports upgrade/downgrade/sidegrade to another version true No

You can also add any of the configuration parameters listed in the parameters section to your plan.

To create plans with specific sizes and versions, set the tier, storage_gb, and postgres_version properties.

If you set a parameter at plan level, developers cannot change the value when creating or updating service instances.

Caution When editing a plan configuration, consider the implications for existing services. If you change a default value, this value is applied to existing service instances when they are updated. For example, if you change the region, then when a service instance is updated, an attempt is made to change the region. This is not a supported operation in GCP and might result in data loss.

Configuration Parameters

You can provision a service by running:

cf create-service csb-google-postgres PLAN-NAME SERVICE-INSTANCE-NAME -c '{"PARAMETER-NAME": "PARAMETER-VALUE"}'

You can update the configuration parameters for a service instance by running:

cf update-service SERVICE-INSTANCE-NAME -c '{"PARAMETER-NAME": "PARAMETER-VALUE"}'

The following table lists the parameters that you can configure, by using the -c flag, when provisioning or updating a csb-google-postgres service. The Operation column displays whether a parameter is supported for both provision and update, or for provision only:

Parameter Name Type Description Default Operation
tier string Required unless defined in plan. Google machine tier. This determines the CPU and RAM resource. It can be a legacy machine type, such as db-f1-micro, db-g1-small, db-n1-standard-8, db-n1-highmem-16, or a custom machine type such as db-custom-8-8192. For more information about machine types, see the Google Cloud documentation. None provision and update
postgres_version string Required unless defined in the plan. The version for the PostgreSQL instance. PostgreSQL 15 is not supported. POSTGRES_11, POSTGRES_12, POSTGRES_13, or POSTGRES_14. POSTGRES_13 provision
storage_gb number Required unless defined in the plan. Size of storage volume for service instance. 10–4096 GB. 10 provision and update
instance_name string Name for the PostgreSQL instance. csb-postgres-INSTANCE-ID provision
db_name string Name for the database. csb-db provision
region string The region of the PostgreSQL instance. The value the operator entered for Region in Ops Manager. provision
require_ssl boolean Require TLS for the connection between PostgreSQL and the apps bound to the service. true provision and update
authorized_network string The name of the Google Compute Engine network to which the instance is connected. The authorized_network_id takes precedence if set. default provision and update
authorized_network_id string The ID of the Google Compute Engine network to which the instance is connected. The value the operator entered for authorized_network_id in Ops Manager. If that is empty then authorized_network is used. provision and update
authorized_networks_cidrs array CIDR notation IPv4 or IPv6 addresses that are allowed to access this instance. [] provision and update
public_ip Boolean Assigns a static public IPv4 IP to the database. You must configure authorized_networks_cidrs on the selected network to enable access. false provision and update
backups_retain_number integer The number of backups to retain. Set this to zero to deactivate backups. 7 provision and update
backups_location string The location where backups are stored. us provision and update
backups_start_time string Start of the backup time window in UTC. 07:00 provision and update
backups_point_in_time_log_retain_days integer The number of days to retain point in time logs. Set to zero to deactivate point in time logging. Before you set this property, you must first enable backups using the backups_retain_number property. 7 provision and update
credentials string The GCP credentials. The value the operator entered for Credentials in Ops Manager. provision and update
project string The GCP project to use. The value the operator entered for Project in Ops Manager. provision

Binding

You can bind a service by running:

cf bind-service APP-NAME SERVICE-INSTANCE-NAME --binding-name BINDING-NAME

A binding or service key corresponds to a user in PostgreSQL. By default, PostgreSQL users do not have have access to data written by other users.

For bindings to have access to the same data, the public schema can be used, or a schema can be created and access can be granted to other users.

When a binding or service key is deleted, data that the PostgreSQL user owns is re-assigned to a role called binding_user_group before the user is deleted. This ensures that other bindings still have access to the data.

Binding Process

For each new binding, the Google PostgreSQL service creates a new user with the role binding_user_group. When the binding is deleted, all the objects the user owns are re-assigned to the role binding_user_group, and the user is deleted.

The client certificate exposed through the binding credentials is created during provisioning, and is shared between all binding users. The client certificate is managed in this manner for the following reasons:

  • Each CloudSQL instance is limited to 10 certificates. For more information, see the Google Cloud documentation.
  • The lifetime of a client certificate is set to 10 years.
  • The client certificate is used to authorize only the connection and never the user.

Currently, the Google PostgreSQL service does not provide a mechanism for TLS certificate rotation.

Binding Process Known Issue

When an app creates objects with one binding, depending on the permissions granted, other bindings might not have permissions to see these objects. This can cause errors in apps, and it can cause errors creating additional bindings. For example:

Binding service instance csb-google-postgres-small to app my-app in org \
my-org / space my-space as my-user...
Job (dccc89be-b91f-11ed-a525-3b487b0bd45d) failed: bind could not be completed: \
Service broker error: error performing bind: error waiting for result: bind failed: Error: pq: \
permission denied for table flyway_schema_history with csbpg_binding_user.new_user, on main.tf line 15,\
in resource "csbpg_binding_user" "new_user": 15: resource "csbpg_binding_user" "new_user" { exit status 1
FAILED

If this occurs, it is possible to grant access to the object so that it can be accessed through other bindings.

  1. Look up the GUID of the service instance by running:

    cf service SERVICE-NAME
    

    Where SERVICE-NAME is the name of the service

  2. In the Google Cloud Console, locate the Google SQL instance by using the GUID you learned earlier.

  3. Set a password for the postgres user.
  4. Log in to the database by running the psql command as the postgres user with the password you set.
  5. Grant access to the binding_user_group role: grant binding_user_group to postgres;
  6. Grant public access to the object in question: grant all on table public.flyway_schema_history to public;
  7. Repeat the previous step for all objects that subsequent bindings need access to.

After this, subsequent binding operations succeed and apps using these bindings can detect the objects.

Binding Credentials

The format for binding credentials for Google PostgreSQL is as follows:

{
    "name" : "DATABASE-NAME",
    "hostname" : "DATABASE-SERVER-HOST",
    "port" : "DATABASE-SERVER-PORT",
    "username" : "AUTHENTICATION-USERNAME",
    "password" : "AUTHENTICATION-PASSWORD",
    "require_ssl" : true,
    "uri" : "DATABASE-CONNECTION-URI",
    "jdbcUrl" : "JDBC-FORMAT-CONNECTION-URL",
    "sslcert" : "SSL_CLIENT_CERT",
    "sslkey" : "SSL_CLIENT_CERT_KEY",
    "sslrootcert" : "SSL_ROOT_CERT"
}
check-circle-line exclamation-circle-line close-line
Scroll to top icon