PostgreSQL Cloud SQL
This provisions a new PostgreSQL database in an existing Google Cloud SQL Postgres instance. The instance must be reachable from Humanitec IPs and be configured to accept connections via the Google Cloud SQL Auth Proxy. Any workload that has a dependency on this postgres
resource will automatically have the Google Cloud SQL Auth Proxy installed as a sidecar container.
Property | Description |
---|---|
Resource type | postgres |
Account type | gcp |
Inputs
Values
Name | Type | Description |
---|---|---|
instance |
string | The fully qualified instance ID of the Cloud SQL instance. Must be of the form <project-id>:<region>:<id> . |
name |
string | [Optional] The name of the maintenance database to connect to. Defaults to postgres . |
template_name |
string | [Optional] If provided, the Driver will use this database name as a template. See CREATE DATABASE. |
Secrets
Name | Type | Description |
---|---|---|
dbcredentials |
object | An object holding username and password properties for the PostgreSQL superuser. |
account |
object | [Optional] Only required if not using a Google Cloud Platform Cloud Account configured in the Platform Orchestrator. An object holding the properties of service account credentials, as generated by the gcloud iam service-accounts keys create command. |
Notes
Service Account for Cloud SQL Auth Proxy
Before the Driver can be used, a GCP Service Account must be added as a Humanitec Cloud Account.
This GCP Service Account requires at least the Cloud SQL Client (roles/cloudsql.client
) role.
The ID of this account must be used for the Driver Account that will be used both by the Driver and by the subsequent sidecar proxy that is installed. Alternatively, place the credentials in a secret store and provide a secret reference for the Driver Cloud Account.
Automatic population of database
PostgreSQL supports creating databases based on templates. (See CREATE DATABASE) This functionality can be used to effectively pre-populate a database as it is provisioned.
It is important to be familiar with the constraints of template databases in PostgreSQL. The documentation states:
Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose “COPY DATABASE” facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes.
For more information on managing template DBs, see Template Databases.
Example
Set the following environment variables for the CLI and API commands:
Variable | Example | Description |
---|---|---|
HUMANITEC_TOKEN |
my-token |
The authentication token for accessing the Humanitec API. |
HUMANITEC_ORG |
my-org-id |
The unique identifier for the organization in Humanitec. |
Use the command below for the interface of your choice to create a fresh PostgreSQL database in an instance example-project:europe-west-1:postgres-db
using a Cloud Account pre-configured in Humanitec called example-gcp-account
.
To use your own secret store for providing the Cloud Account credentials, omit the "driver_account"
and provide a secret reference as shown here.
- Create a file defining the Resource Definition you want to create:
cat << EOF > dev-postgres-cloudsql.yaml
apiVersion: entity.humanitec.io/v1b1
kind: Definition
metadata:
id: dev-postgres-cloudsql
entity:
driver_type: humanitec/postgres-cloudsql
name: Dev PostgreSQL CloudSQL
type: postgres
driver_account: example-gcp-account
driver_inputs:
secrets:
dbcredentials:
username: root
password: 53cr3t-P455w0rd
values:
instance: example-project:europe-west-1:postgres-db
name: postgres
criteria:
- env_type: development
EOF
- Use the
humctl create
command to create the Resource Definition in the Organization defined by your configured context:
humctl create -f dev-postgres-cloudsql.yaml
rm dev-postgres-cloudsql.yaml
curl https://api.humanitec.io/orgs/${HUMANITEC_ORG}/resources/defs \
-X POST \
-H "Authorization: Bearer ${HUMANITEC_TOKEN}" \
-H "Content-Type: application/json" \
--data-binary '
{
"id": "dev-postgres-cloudsql",
"name": "Dev PostgreSQL CloudSQL",
"type": "postgres",
"criteria": [
{
"env_type": "development"
}
],
"driver_account": "example-gcp-account",
"driver_type": "humanitec/postgres-cloudsql",
"driver_inputs": {
"values": {
"instance": "example-project:europe-west-1:postgres-db",
"name": "postgres"
},
"secrets": {
"dbcredentials": {
"username": "postgres",
"password": "53cr3t-P455w0rd"
}
}
}
}'
Google CloudSQL uses a slightly different Humanitec Driver to other Postgres providers, but this is transparent outside the resource definitions.
Prerequisites
- You must have a CloudSQL instance running.
- You must have a Service Account with the Cloud SQL Client role.
- You must create a key for this service account in JSON format. See Google’s account keys documentation for more information. The access key must use the GCP Console/gcloud format (i.e., the first example in the linked documentation).
- You must have the CloudSQL Admin API enabled for your project.
- You must have a user defined on the instance for Workloads to use when connecting to the database.
Add a GCP Account
To integrate a CloudSQL account you need to add a Cloud Account (if you haven’t already).
- From the Cloud Accounts screen, click Google Cloud Platform in the Add accounts section.
- In the modal dialog provide a unique name for the account in the Account name field.
- In the Service account access key(JSON) field paste the account key for the service account that you wish to use.
- Click Authorize.
Add a Resource Definition
- From the Resource Management screen, click Add resource definition.
- In the modal dialog click Postgres.
- Next, select the postgres-cloudsql Driver.
- Finally, provide the following information, then click Add Postgress.
- In the ID field provide a unique ID for the resource.
- In the Credentials field select the Cloud Account you created earlier.
- In the Fully qualified CloudSQL instance name provide the instance name in the format
<project-id>:<region>:<id>
. - If the maintenance database name is not
postgres
provide that in the Database name field. - In the User / Role and Password fields provide the credentials that Workloads should use when connecting to the database.
Resource Matching
Now that the resource is defined you will need to add matching criteria.
- Click on the relevant row in the Resource Definition table.
- Then switch to the Matching Criteria tab.
- Click + Add new Criteria.
- Configure the matching rules as needed:
- Environment Type:
development
.
- Environment Type:
- Click Save.