PostgreSQL binding spec

Detailed documentation on the PostgreSQL binding component

Component format

To setup PostgreSQL binding create a component of type bindings.postgresql. See this guide on how to create and apply a binding configuration.

apiVersion: dapr.io/v1alpha1
kind: Component
metadata:
  name: <NAME>
spec:
  type: bindings.postgresql
  version: v1
  metadata:
    # Connection string
    - name: connectionString
      value: "<CONNECTION STRING>"

Warning

The above example uses secrets as plain strings. It is recommended to use a secret store for the secrets as described here.

Spec metadata fields

Authenticate using a connection string

The following metadata options are required to authenticate using a PostgreSQL connection string.

Authenticate using Microsoft Entra ID

Authenticating with Microsoft Entra ID is supported with Azure Database for PostgreSQL. All authentication methods supported by Dapr can be used, including client credentials (“service principal”) and Managed Identity.

Authenticate using AWS IAM

Authenticating with AWS IAM is supported with all versions of PostgreSQL type components. The user specified in the connection string must be an already existing user in the DB, and an AWS IAM enabled user granted the rds_iam database role. Authentication is based on the AWS authentication configuration file, or the AccessKey/SecretKey provided. The AWS authentication token will be dynamically rotated before it’s expiration time with AWS.

Other metadata options

URL format

The PostgreSQL binding uses pgx connection pool internally so the connectionString parameter can be any valid connection string, either in a DSN or URL format:

Example DSN

user=dapr password=secret host=dapr.example.com port=5432 dbname=my_dapr sslmode=verify-ca

Example URL

postgres://dapr:secret@dapr.example.com:5432/my_dapr?sslmode=verify-ca

Both methods also support connection pool configuration variables:

• pool_min_conns: integer 0 or greater
• pool_max_conns: integer greater than 0
• pool_max_conn_lifetime: duration string
• pool_max_conn_idle_time: duration string
• pool_health_check_period: duration string

Binding support

This component supports output binding with the following operations:

• exec
• query
• close

Parametrized queries

This binding supports parametrized queries, which allow separating the SQL query itself from user-supplied values. The usage of parametrized queries is strongly recommended for security reasons, as they prevent SQL Injection attacks.

For example:

-- ❌ WRONG! Includes values in the query and is vulnerable to SQL Injection attacks.
SELECT * FROM mytable WHERE user_key = 'something';
-- ✅ GOOD! Uses parametrized queries.
-- This will be executed with parameters ["something"]
SELECT * FROM mytable WHERE user_key = $1;

exec

The exec operation can be used for DDL operations (like table creation), as well as INSERT, UPDATE, DELETE operations which return only metadata (e.g. number of affected rows).

The params property is a string containing a JSON-encoded array of parameters.

Request

{
  "operation": "exec",
  "metadata": {
    "sql": "INSERT INTO foo (id, c1, ts) VALUES ($1, $2, $3)",
    "params": "[1, \"demo\", \"2020-09-24T11:45:05Z07:00\"]"
  }
}

Response

{
  "metadata": {
    "operation": "exec",
    "duration": "294µs",
    "start-time": "2020-09-24T11:13:46.405097Z",
    "end-time": "2020-09-24T11:13:46.414519Z",
    "rows-affected": "1",
    "sql": "INSERT INTO foo (id, c1, ts) VALUES ($1, $2, $3)"
  }
}

query

The query operation is used for SELECT statements, which returns the metadata along with data in a form of an array of row values.

The params property is a string containing a JSON-encoded array of parameters.

Request

{
  "operation": "query",
  "metadata": {
    "sql": "SELECT * FROM foo WHERE id < $1",
    "params": "[3]"
  }
}

Response

{
  "metadata": {
    "operation": "query",
    "duration": "432µs",
    "start-time": "2020-09-24T11:13:46.405097Z",
    "end-time": "2020-09-24T11:13:46.420566Z",
    "sql": "SELECT * FROM foo WHERE id < $1"
  },
  "data": "[
    [0,\"test-0\",\"2020-09-24T04:13:46Z\"],
    [1,\"test-1\",\"2020-09-24T04:13:46Z\"],
    [2,\"test-2\",\"2020-09-24T04:13:46Z\"]
  ]"
}

close

The close operation can be used to explicitly close the DB connection and return it to the pool. This operation doesn’t have any response.

Request

{
  "operation": "close"
}

Related links

• Basic schema for a Dapr component
• Bindings building block
• How-To: Trigger application with input binding
• How-To: Use bindings to interface with external resources
• Bindings API reference

Last modified October 11, 2024: Fixed typo (#4389) (fe17926)