PostgreSQL

Type: state.postgresql

Status: stable

Reference: https://docs.dapr.io/reference/components-reference/supported-state-stores/setup-postgresql/

Example

apiVersion: cra.diagrid.io/v1beta1
kind: Component
metadata:
  name: <name>
spec:
  type: state.postgresql
  version: v2
  metadata:
    # The connection string for the PostgreSQL database
    - name: connectionString
      value: "host=mycompany.com user=postgres password=example port=5432 connect_timeout=10 database=dapr_test"
    # Interval to clean up rows with an expired TTL. Setting this to values `<=0` disables the periodic cleanup. (Optional)
    #- name: cleanupInterval
    #  value: "1h"
    # Max idle time before unused connections are automatically closed in the connection pool. By default, there's no value and this is left to the database driver to choose. (Optional)
    #- name: connectionMaxIdleTime
    #  value: "5m"
    # Use this state store as the Workflows Store, it will enable the Workflows API. Defaults to `false`. (Optional)
    #- name: enableWorkflow
    #  value: "false"
    # Prefix added to keys in the state store. (Optional)
    #- name: keyPrefix
    #  value: "appid"
    # Maximum number of connections pooled by this component. Set to 0 or lower to use the default value, which is the greater of 4 or the number of CPUs. (Optional)
    #- name: maxConns
    #  value: "0"
    # Name of the table Dapr uses to store a few metadata properties. Can optionally have the schema name as prefix, such as `public.dapr_metadata` (Optional)
    #- name: metadataTableName
    #  value: "dapr_metadata"
    # By setting outboxDiscardWhenMissingState to true, Dapr discards the transaction if it cannot find the state in the database and does not retry. This setting can be useful if the state store data has been deleted for any reason before Dapr was able to deliver the message and you would like Dapr to drop the items from the pub/sub and stop retrying to fetch the state (Optional)
    #- name: outboxDiscardWhenMissingState
    #  value: "false"
    # For outbox. Sets the name of the pub/sub component to deliver the notifications when publishing state changes (Optional)
    #- name: outboxPublishPubsub
    #  value: ""
    # For outbox. Sets the topic that receives the state changes on the pub/sub configured with "outboxPublishPubsub". The message body will be a state transaction item for an insert or update operation (Optional)
    #- name: outboxPublishTopic
    #  value: ""
    # For outbox. Sets the pub/sub component used by Dapr to coordinate the state and pub/sub transactions. If not set, the pub/sub component configured with "outboxPublishPubsub" is used. This is useful if you want to separate the pub/sub component used to send the notification state changes from the one used to coordinate the transaction (Optional)
    #- name: outboxPubsub
    #  value: "outboxPublishPubsub"
    # Controls the default mode for executing queries. By default Dapr uses the extended protocol and automatically prepares and caches prepared statements. However, this may be incompatible with proxies such as PGBouncer. In this case it may be preferrable to use `exec` or `simple_protocol`. (Optional)
    #- name: queryExecMode
    #  value: "cache_describe"
    # Prefix for the tables where the data is stored. Can optionally have the schema name as prefix, such as `public.` (Optional)
    #- name: tablePrefix
    #  value: "my_ (name prefix) or public. (schema name)"
    # Timeout for all database operations. (Optional)
    #- name: timeout
    #  value: "20s"

Authentication profiles

Available authentication profiles:

Connection string
Azure AD: Client credentials
Azure AD: Client certificate
AWS: Access Key ID and Secret Access Key

Connection string

Authenticate using a Connection String

`connectionString` (string)

Required - The connection string for the PostgreSQL database

Example value: host=mycompany.com user=postgres password=example port=5432 connect_timeout=10 database=dapr_test

Azure AD: Client credentials

Authenticate using Azure AD with client credentials, also known as "service principals".

`azureClientId`

Required - Client ID (application ID)

Example value: c7dd251f-811f-4ba2-a905-acd4d3f8f08b

`azureClientSecret`

Required - Client secret (application password)

Example value: Ecy3XG7zVZK3/vl/a2NSB+a1zXLa8RnMum/IgD0E

`azureTenantId`

Required - ID of the Azure AD tenant

Example value: cd4b2887-304c-47e1-b4d5-65447fdd542a

`connectionString` (string)

Required - The connection string for the PostgreSQL database This must contain the user, which corresponds to the name of the user created inside PostgreSQL that maps to the Azure AD identity; this is often the name of the corresponding principal (e.g. the name of the Azure AD application). This connection string should not contain any password.

Example value: host=mydb.postgres.database.azure.com user=myapplication port=5432 database=dapr_test password=masterpassword sslmode=require

`useAzureAD` (bool)

Required - Must be set to true to enable the component to retrieve access tokens from Azure AD. This authentication method only works with Azure Database for PostgreSQL databases.

Default value: true

Example value: true

`azureEnvironment`

Optional name for the Azure environment if using a different Azure cloud

Default value: AzurePublicCloud

Example value: AzurePublicCloud

Allowed values:

AzurePublicCloud
AzureChinaCloud
AzureUSGovernmentCloud

Azure AD: Client certificate

Authenticate using Azure AD with a client certificate. "azureCertificate" is required.

`azureClientId`

Required - Client ID (application ID)

Example value: c7dd251f-811f-4ba2-a905-acd4d3f8f08b

`azureTenantId`

Required - ID of the Azure AD tenant

Example value: cd4b2887-304c-47e1-b4d5-65447fdd542a

`connectionString` (string)

Example value: host=mydb.postgres.database.azure.com user=myapplication port=5432 database=dapr_test password=masterpassword sslmode=require

`useAzureAD` (bool)

Required - Must be set to true to enable the component to retrieve access tokens from Azure AD. This authentication method only works with Azure Database for PostgreSQL databases.

Default value: true

Example value: true

`azureCertificate`

Certificate and private key (in either a PEM file containing both the certificate and key, or in PFX/PKCS#12 format)

Example value:

-----BEGIN PRIVATE KEY-----\n MIIEvgI... \n -----END PRIVATE KEY-----
\n -----BEGIN CERTIFICATE----- \n MIICoTC... \n -----END CERTIFICATE----- \n

`azureCertificatePassword`

Password for the certificate if encrypted.

Example value: password

`azureEnvironment`

Optional name for the Azure environment if using a different Azure cloud

Default value: AzurePublicCloud

Example value: AzurePublicCloud

Allowed values:

AzurePublicCloud
AzureChinaCloud
AzureUSGovernmentCloud

AWS: Access Key ID and Secret Access Key

Authenticate using an Access Key ID and Secret Access Key included in the metadata

`awsRegion` (string)

Required - The AWS Region where the AWS Relational Database Service is deployed to.

Example value: us-east-1

`connectionString` (string)

Required - The connection string for the PostgreSQL database This must contain the user, which corresponds to the name of the user created inside PostgreSQL that maps to the AWS IAM policy. This connection string should not contain any password. Note that the database name field is denoted by dbname with AWS.

Example value: host=mydb.postgres.database.aws.com user=myapplication port=5432 dbname=dapr_test sslmode=require

`useAWSIAM` (bool)

Required - Must be set to true to enable the component to retrieve access tokens from AWS IAM. This authentication method only works with AWS Relational Database Service for PostgreSQL databases.

Example value: true

`awsAccessKey` (string)

AWS access key associated with an IAM account.

Example value: AKIAIOSFODNN7EXAMPLE

`awsSecretKey` (string)

The secret key associated with the access key.

Example value: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

`sessionToken` (string)

AWS session token to use. A session token is only required if you are using temporary security credentials.

Example value: TOKEN

Metadata

`cleanupInterval` (duration)

Interval to clean up rows with an expired TTL. Setting this to values <=0 disables the periodic cleanup.

Default value: 1h

Example value: 10m, -1

`connectionMaxIdleTime` (duration)

Max idle time before unused connections are automatically closed in the connection pool. By default, there's no value and this is left to the database driver to choose.

Example value: 5m

`enableWorkflow` (bool)

Use this state store as the Workflows Store, it will enable the Workflows API. Defaults to false.

Example value: false

`keyPrefix` (string)

Prefix added to keys in the state store.

Default value: appid

Example value: appid

Allowed values:

appid
name
none

Documentation: https://docs.dapr.io/developing-applications/building-blocks/state-management/howto-share-state/

`maxConns` (number)

Maximum number of connections pooled by this component. Set to 0 or lower to use the default value, which is the greater of 4 or the number of CPUs.

Default value: 0

Example value: 4

`metadataTableName` (string)

Name of the table Dapr uses to store a few metadata properties. Can optionally have the schema name as prefix, such as public.dapr_metadata

Default value: dapr_metadata

Example value: public.dapr_metadata

`outboxDiscardWhenMissingState` (bool)

By setting outboxDiscardWhenMissingState to true, Dapr discards the transaction if it cannot find the state in the database and does not retry. This setting can be useful if the state store data has been deleted for any reason before Dapr was able to deliver the message and you would like Dapr to drop the items from the pub/sub and stop retrying to fetch the state

Default value: false

`outboxPublishPubsub` (string)

For outbox. Sets the name of the pub/sub component to deliver the notifications when publishing state changes

`outboxPublishTopic` (string)

For outbox. Sets the topic that receives the state changes on the pub/sub configured with "outboxPublishPubsub". The message body will be a state transaction item for an insert or update operation

`outboxPubsub` (string)

For outbox. Sets the pub/sub component used by Dapr to coordinate the state and pub/sub transactions. If not set, the pub/sub component configured with "outboxPublishPubsub" is used. This is useful if you want to separate the pub/sub component used to send the notification state changes from the one used to coordinate the transaction

Default value: outboxPublishPubsub

`queryExecMode`

Controls the default mode for executing queries. By default Dapr uses the extended protocol and automatically prepares and caches prepared statements. However, this may be incompatible with proxies such as PGBouncer. In this case it may be preferrable to use exec or simple_protocol.

Example value: cache_describe

Allowed values:

cache_statement
cache_describe
describe_exec
exec
simple_protocol

`tablePrefix` (string)

Prefix for the tables where the data is stored. Can optionally have the schema name as prefix, such as public.

Example value: my_ (name prefix) or public. (schema name)

`timeout` (duration)

Timeout for all database operations.

Default value: 20s

Example value: 30s

PostgreSQL

Example​

Authentication profiles​

Connection string​

connectionString (string)​

Azure AD: Client credentials​

azureClientId​

azureClientSecret​

azureTenantId​

connectionString (string)​

useAzureAD (bool)​

azureEnvironment​

Azure AD: Client certificate​

azureClientId​

azureTenantId​

connectionString (string)​

useAzureAD (bool)​

azureCertificate​

azureCertificatePassword​

azureEnvironment​

AWS: Access Key ID and Secret Access Key​

awsRegion (string)​

connectionString (string)​

useAWSIAM (bool)​

awsAccessKey (string)​

awsSecretKey (string)​

sessionToken (string)​

Metadata​

cleanupInterval (duration)​

connectionMaxIdleTime (duration)​

enableWorkflow (bool)​

keyPrefix (string)​

maxConns (number)​

metadataTableName (string)​

outboxDiscardWhenMissingState (bool)​

outboxPublishPubsub (string)​

outboxPublishTopic (string)​

outboxPubsub (string)​

queryExecMode​

tablePrefix (string)​

timeout (duration)​

Example

Authentication profiles

Connection string

`connectionString` (string)

Azure AD: Client credentials

`azureClientId`

`azureClientSecret`

`azureTenantId`

`connectionString` (string)

`useAzureAD` (bool)

`azureEnvironment`

Azure AD: Client certificate

`azureClientId`

`azureTenantId`

`connectionString` (string)

`useAzureAD` (bool)

`azureCertificate`

`azureCertificatePassword`

`azureEnvironment`

AWS: Access Key ID and Secret Access Key

`awsRegion` (string)

`connectionString` (string)

`useAWSIAM` (bool)

`awsAccessKey` (string)

`awsSecretKey` (string)

`sessionToken` (string)

Metadata

`cleanupInterval` (duration)

`connectionMaxIdleTime` (duration)

`enableWorkflow` (bool)

`keyPrefix` (string)

`maxConns` (number)

`metadataTableName` (string)

`outboxDiscardWhenMissingState` (bool)

`outboxPublishPubsub` (string)

`outboxPublishTopic` (string)

`outboxPubsub` (string)

`queryExecMode`

`tablePrefix` (string)

`timeout` (duration)