PostgreSQL over SSH

This provisions a new PostgreSQL database in an existing PostgreSQL instance. The Driver will first connect to an SSH server acting as a bastion host and then port forward to the PostgreSQL instance. The SSH server must be accessible from Humanitec IPs and the PostgreSQL instance must be directly accessible from the SSH server.

Property Description
Resource type postgres
Account type None

Inputs

Values

Name Type Description
host string The IP Address or hostname that the instance is available on.
port integer The port the instance is listening on.
name string [Optional] The name of the maintenance database to connect to. Defaults to postgres.
append_host_to_user boolean [Optional] Azure Databases for Postgres and MySQL require usernames to have @servername appended to them. Set this to true for the Driver to append this automatically. (See: Azure Database connection strings)
template_name string [Optional] If provided, the Driver will use this database name as a template. See CREATE DATABASE.
ssh_host string Hostname or IP address of the SSH server.
ssh_port integer The port to connect to on the SSH server.
ssh_user string The username to use to connect to the SSH server.

Secrets

Name Type Description
dbcredentials object An object holding username and password properties for the PostgreSQL superuser.

Notes

Configuring the SSH server

In order for the Driver to use the SSH server as a bastion host, the following needs to be configured:

  • The Humanitec Database Server Public Key must be added to the authorized_keys file on the server.
  • The user that is used for connecting on must have permissions to perform a port forward.
  • The SSH server must be configured to allow port forwarding - typically via an entry in the configuration file similar to AllowTcpForwarding yes

The Humanitec Database Server Public Key is:

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQC81rM8WC50rYoQvn0brj+iONhgs8GyAospZofqN+rPOu9dJTN6movAqmCRYbfZxaDpE86I5+HzAMW206znhxzLw/7ips709U4jkUzpTTpRm+6Q3draO6Kj6tRhhvGyUCCcBeG2E1ZpL+5CrYOQe6R2BTgrRS/3JhKA6A4VkN/HycrwQORJ/AtMWoNlh5hn0IOB5H9BMg7sBLYyX26tFQNdubJFIPDkMDzXbyS6SSVzqQMUZCR7ksCaPj/9eZMBWtNvklrkAsI9pgvFawT+yTOxIKzmJ4j1JTd7y+HjTxO1AGaddhkrxXcyVTt9Rz/8J+/BlvrTlSp4gOyKudIhAvk7HHr+nBPdBOM9pc1zOIfB8pcj6+tNm+Rwae/i9uK6PiTIefRkIxX3yVprwQawjg0FbNOAd4wtsMZU3XtKc3ScXtFhqPUkyKLefTJjRWwvS86NrKTO0fsewZnrvNuH94rXvBFPBACnfImtN6rC3oeqXmbZshq32z07LAdR5SLU+iU= [email protected]

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:

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 available at dev-postgres.internal.example.com accessed via a bastion host on bastion.example.com as the user example-ssh-user.

  1. Create a file defining the Resource Definition you want to create:
cat << EOF > dev-postgres-ssh.yaml
apiVersion: entity.humanitec.io/v1b1
kind: Definition
metadata:
  id: dev-postgres-ssh
entity:
  name: Dev PostgreSQL SSH
  type: postgres
  driver_type: humanitec/postgres-ssh
  driver_inputs:
    secrets:
      dbcredentials:
        username: postgres
        password: 53cr3t-P455w0rd
    values:
      host: dev-postgres.example.com
      name: postgres
      port: 5432
      ssh_host: bastion.example.com
      ssh_port: 22
      ssh_user: example-ssh-user
  criteria:
  - env_type: development
EOF
  1. Use the humctl create command to create the Resource Definition in the Organization defined by your configured context:
humctl create -f dev-postgres-ssh.yaml
rm dev-postgres-ssh.yaml



curl https://api.humanitec.io/orgs/${HUMANITEC_ORG}/resources/defs \
  -X POST \
  -H "Authorization: Bearer ${HUMANITEC_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '
{
  "id": "dev-postgres-ssh",
  "name": "Dev PostgreSQL SSH",
  "type": "postgres",
  "criteria": [
    {
      "env_type": "development"
    }
  ],
  "driver_type": "humanitec/postgres-ssh",
  "driver_inputs": {
    "values": {
      "host": "dev-postgres.example.com",
      "port": 5432,
      "name": "postgres",
      "ssh_host": "bastion.example.com",
      "ssh_port": 22,
      "ssh_user": "example-ssh-user"
    },
    "secrets": {
      "dbcredentials": {
        "username": "postgres",
        "password": "53cr3t-P455w0rd"
      }
    }
  }
}'

Most PostgresSQL implementations use a single shared Driver. For Google CloudSQL implementations, see the Google CloudSQL Driver.

Prerequisites

  • You must have a database instance/server running.
  • You must have a user defined on the instance for Workloads to use when connecting to the database.

Add a Resource Definition

  1. From the Resource Management screen, click Add resource definition.
  2. In the modal dialog click Postgres.
  3. Next, select the postgres-ssh Driver.
  4. Finally, provide the following information, then click Add Postgres.
    1. In the ID field provide a unique ID for the Resource.
    2. Enter the SSH username, hostname, and port.
    3. Enter the database hostname, and port.
    4. Provide a database name (defaults to postgres).
    5. Provide the user or role of the database.
    6. Provide the password for the database.
    7. Choose whether to have @servername appended to the username.

Resource Matching

Now that the Resource is defined you will need to add matching criteria.

  1. Click on the relevant row in the Resource Definition table.
  2. Then switch to the Matching Criteria tab.
  3. Click + Add new Criteria.
  4. Configure the matching rules as needed:
    • Environment Type: development.
  5. Click Save.

PostgreSQL Cloud SQL