How to Secure PostgreSQL Managed Database Clusters

Validated on 8 Aug 2019 • Last edited on 7 Jun 2023 luks | tag

PostgreSQL is an open source, object-relational database built for extensibility, data integrity, and speed. Its concurrency support makes it fully ACID-compliant, and it supports dynamic loading and catalog-driven operations to let users customize its data types, functions, and more.

Data in PostgreSQL database clusters is encrypted at rest with LUKS (Linux Unified Key Setup) and in transit with SSL. However, there are additional steps you can take to ensure that your data is safe.

Restrict Incoming Connections

You can greatly decrease the likelihood of a security breach by restricting which DigitalOcean resources or external IP addresses are allowed to access the nodes in a cluster. This prevents brute force password and denial-of-service attacks from any server not explicitly permitted to connect.

Typically, only the application servers are allowed to connect to the database cluster. Users access the public-facing site, and the public-facing server authenticates and manages database connections in turn.

Add a Trusted Source Using the CLI

How to Add a Trusted Source Using the DigitalOcean CLI

  1. Install doctl, the DigitalOcean command-line tool.

  2. Create a personal access token and save it for use with doctl.

  3. Use the token to grant doctl access to your DigitalOcean account.

              doctl auth init

  4. Finally, run doctl databases firewalls append. Basic usage looks like this, but you can read the usage docs for more details:

                doctl databases firewalls append <database-cluster-id> --rule <type>:<value> [flags]

    The following example appends a firewall rule to a database cluster with the ID ca9f591d-f38h-5555-a0ef-1c02d1d1e35 that allows any resources with the example-tag to access the database:

                  doctl databases firewalls append ca9f591d-f38h-5555-a0ef-1c02d1d1e35 --rule tag:example-tag

Add a Trusted Source Using the API

How to Add or Remove a Trusted Source Using the DigitalOcean API

  1. Create a personal access token and save it for use with the API.

  2. Send a PUT request to https://api.digitalocean.com/v2/databases/{database_cluster_uuid}/firewall

    cURL

    Using cURL:

                    curl -X PUT \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  -d '{"rules": [{"type": "ip_addr","value": "192.168.1.1"},{"type": "droplet","value": "163973392"},{"type": "k8s","value": "ff2a6c52-5a44-4b63-b99c-0e98e7a63d61"},{"type": "tag","value": "backend"}]}' \
  "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/firewall"

    Go

    Using Godo, the official DigitalOcean V2 API client for Go: 

                    import (
    "context"
    "os"

    "github.com/digitalocean/godo"
)

func main() {
    token := os.Getenv("DIGITALOCEAN_TOKEN")

    client := godo.NewFromToken(token)
    ctx := context.TODO()

    req := godo.DatabaseUpdateFirewallRulesRequest{
      Rules: []*godo.DatabaseFirewallRule{
        {
         Type:  "ip_addr",
         Value: "192.168.1.1",
       },
        {
         Type:  "droplet",
         Value: "163973392",
       },
        {
         Type:  "k8s",
         Value: "ff2a6c52-5a44-4b63-b99c-0e98e7a63d61",
        },
      },
    }
    _, err := client.Databases.UpdateFirewallRules(ctx, dbID, &req)
}

    Python

                    import os
from pydo import Client

client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN"))

req = {
  "rules": [
    {
      "type": "ip_addr",
      "value": "192.168.1.1"
    },
    {
      "type": "k8s",
      "value": "ff2a6c52-5a44-4b63-b99c-0e98e7a63d61"
    },
    {
      "type": "droplet",
      "value": "163973392"
    },
    {
      "type": "tag",
      "value": "backend"
    }
  ]
}
update_resp = client.databases.update_firewall_rules(database_cluster_uuid="a7a8bas", body=req)

Add a Trusted Source using the Control Panel

To restrict access to a database cluster, click the name of the cluster in the control panel to go to its Overview page, then click the Settings tab.

Screenshot of cluster settings page

In the section titled Trusted Sources, click Edit to open the Add trusted sources text box.

The open Trusted Sources section of the settings page

You can enter Droplets, Kubernetes clusters, tags, apps, or specific IP addresses. Entering a tag provides access to the database for any Droplets or Kubernetes nodes containing that tag. At this time, DigitalOcean Cloud Firewalls are not supported.

Warning
You currently cannot add IPv6 rules to a database cluster’s trusted sources.

Increase the SSL Mode Verification Level

By default, you must use SSL to transmit data because it prevents eavesdropping on administrative usernames and passwords as well as the data itself as it is transmitted. However, SSL doesn’t protect against man-in-the-middle (MITM) attacks or impersonation.

You can enable the verify-ca and verify-full SSL modes for PostgreSQL to check for these attacks. They’re disabled by default because they can affect performance.

To enable verify-ca or verify-full, update the value for sslmode in the --set flag when connecting. For example, to connect using the verify-full option, your connection string would look like this:

PGPASSWORD=paakjnfe10rsrsmf psql -U doadmin -h source-do-user-6607903-0.b.db.ondigitalocean.com -p 25060 -d defaultdb --set=sslmode=verify-full

Learn more in the PostgreSQL documentation on SSL support.

Control Panel Blog Pricing Careers Terms Privacy Status API Docs Tutorials Support