PostgreSQL

Detailed information on the PostgreSQL configuration store component

Component format

To set up an PostgreSQL configuration store, create a component of type configuration.postgresql

  1. apiVersion: dapr.io/v1alpha1
  2. kind: Component
  3. metadata:
  4. name: <NAME>
  5. spec:
  6. type: configuration.postgresql
  7. version: v1
  8. metadata:
  9. # Connection string
  10. - name: connectionString
  11. value: "host=localhost user=postgres password=example port=5432 connect_timeout=10 database=config"
  12. # Name of the table which holds configuration information
  13. - name: table
  14. value: "[your_configuration_table_name]"
  15. # Timeout for database operations, in seconds (optional)
  16. #- name: timeoutInSeconds
  17. # value: 20
  18. # Name of the table where to store the state (optional)
  19. #- name: tableName
  20. # value: "state"
  21. # Name of the table where to store metadata used by Dapr (optional)
  22. #- name: metadataTableName
  23. # value: "dapr_metadata"
  24. # Cleanup interval in seconds, to remove expired rows (optional)
  25. #- name: cleanupIntervalInSeconds
  26. # value: 3600
  27. # Maximum number of connections pooled by this component (optional)
  28. #- name: maxConns
  29. # value: 0
  30. # Max idle time for connections before they're closed (optional)
  31. #- name: connectionMaxIdleTime
  32. # value: 0
  33. # Controls the default mode for executing queries. (optional)
  34. #- name: queryExecMode
  35. # value: ""
  36. # Uncomment this if you wish to use PostgreSQL as a state store for actors (optional)
  37. #- name: actorStateStore
  38. # value: "true"

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.

FieldRequiredDetailsExample
connectionStringYThe connection string for the PostgreSQL database. See the PostgreSQL documentation on database connections for information on how to define a connection string.“host=localhost user=postgres password=example port=5432 connect_timeout=10 database=my_db”

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.

FieldRequiredDetailsExample
useAzureADYMust be set to true to enable the component to retrieve access tokens from Microsoft Entra ID.“true”
connectionStringYThe 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 Microsoft Entra ID identity; this is often the name of the corresponding principal (e.g. the name of the Microsoft Entra ID application). This connection string should not contain any password.
“host=mydb.postgres.database.azure.com user=myapplication port=5432 database=my_db sslmode=require”
azureTenantIdNID of the Microsoft Entra ID tenant“cd4b2887-304c-…”
azureClientIdNClient ID (application ID)“c7dd251f-811f-…”
azureClientSecretNClient secret (application password)“Ecy3X…”

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.

FieldRequiredDetailsExample
useAWSIAMYMust 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.“true”
connectionStringYThe connection string for the PostgreSQL database.
This must contain an already existing 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.
“host=mydb.postgres.database.aws.com user=myapplication port=5432 dbname=my_db sslmode=require”
awsRegionYThe AWS Region where the AWS Relational Database Service is deployed to.“us-east-1”
awsAccessKeyYAWS access key associated with an IAM account“AKIAIOSFODNN7EXAMPLE”
awsSecretKeyYThe secret key associated with the access key“wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY”
awsSessionTokenNAWS session token to use. A session token is only required if you are using temporary security credentials.“TOKEN”

Other metadata options

FieldRequiredDetailsExample
tableYTable name for configuration information, must be lowercased.configtable
timeoutNTimeout for operations on the database, as a Go duration. Integers are interpreted as number of seconds. Defaults to 20s“30s”, 30
maxConnsNMaximum 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.“4”
connectionMaxIdleTimeNMax 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.“5m”
queryExecModeNControls 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.“simple_protocol”

Set up PostgreSQL as Configuration Store

  1. Start the PostgreSQL Database

  2. Connect to the PostgreSQL database and setup a configuration table with following schema:

    FieldDatatypeNullableDetails
    KEYVARCHARNHolds “Key” of the configuration attribute
    VALUEVARCHARNHolds Value of the configuration attribute
    VERSIONVARCHARNHolds version of the configuration attribute
    METADATAJSONYHolds Metadata as JSON
    1. CREATE TABLE IF NOT EXISTS table_name (
    2. KEY VARCHAR NOT NULL,
    3. VALUE VARCHAR NOT NULL,
    4. VERSION VARCHAR NOT NULL,
    5. METADATA JSON
    6. );
  3. Create a TRIGGER on configuration table. An example function to create a TRIGGER is as follows:

    1. CREATE OR REPLACE FUNCTION notify_event() RETURNS TRIGGER AS $$
    2. DECLARE
    3. data json;
    4. notification json;
    5. BEGIN
    6. IF (TG_OP = 'DELETE') THEN
    7. data = row_to_json(OLD);
    8. ELSE
    9. data = row_to_json(NEW);
    10. END IF;
    11. notification = json_build_object(
    12. 'table',TG_TABLE_NAME,
    13. 'action', TG_OP,
    14. 'data', data);
    15. PERFORM pg_notify('config',notification::text);
    16. RETURN NULL;
    17. END;
    18. $$ LANGUAGE plpgsql;
  4. Create the trigger with data encapsulated in the field labeled as data:

    1. notification = json_build_object(
    2. 'table',TG_TABLE_NAME,
    3. 'action', TG_OP,
    4. 'data', data
    5. );
  5. The channel mentioned as attribute to pg_notify should be used when subscribing for configuration notifications

  6. Since this is a generic created trigger, map this trigger to configuration table

    1. CREATE TRIGGER config
    2. AFTER INSERT OR UPDATE OR DELETE ON configtable
    3. FOR EACH ROW EXECUTE PROCEDURE notify_event();
  7. In the subscribe request add an additional metadata field with key as pgNotifyChannel and value should be set to same channel name mentioned in pg_notify. From the above example, it should be set to config

Note

When calling subscribe API, metadata.pgNotifyChannel should be used to specify the name of the channel to listen for notifications from PostgreSQL configuration store.

Any number of keys can be added to a subscription request. Each subscription uses an exclusive database connection. It is strongly recommended to subscribe to multiple keys within a single subscription. This helps optimize the number of connections to the database.

Example of subscribe HTTP API:

  1. curl -l 'http://<host>:<dapr-http-port>/configuration/mypostgresql/subscribe?key=<keyname1>&key=<keyname2>&metadata.pgNotifyChannel=<channel name>'

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