# PostgreSQL

Type: `bindings.postgres`

Status: stable

Reference: https://docs.dapr.io/reference/components-reference/supported-bindings/postgres/

## Example

```
apiVersion: cra.diagrid.io/v1beta1
kind: Component
metadata:
  name: <name>
spec:
  type: bindings.postgres
  version: v1
  metadata:
    # The connection string for the PostgreSQL database
    - name: connectionString
      value: | 
        user=dapr password=secret host=dapr.example.com port=5432 dbname=dapr sslmode=verify-ca
        or postgres://dapr:secret@dapr.example.com:5432/dapr?sslmode=verify-ca
    # 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"
    # The database of the PostgreSQL database (Optional)
    #- name: database
    #  value: "postgres"
    # The host of the PostgreSQL database (Optional)
    #- name: host
    #  value: "mycompany.com"
    # The host address of the PostgreSQL database (Optional)
    #- name: hostaddr
    #  value: "127.0.0.1"
    # 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"
    # The password of the PostgreSQL database (Optional)
    #- name: password
    #  value: "password"
    # The port of the PostgreSQL database (Optional)
    #- name: port
    #  value: "5432"
    # 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"
    # The path to the SSL root certificate file (Optional)
    #- name: sslRootCert
    #  value: "/path/to/ssl/root/cert.pem"
    # Timeout for all database operations. (Optional)
    #- name: timeout
    #  value: "20s"
    # The user of the PostgreSQL database (Optional)
    #- name: user
    #  value: "postgres"

```

## Binding information

Input Binding: no

Output Binding: yes

### Output Binding operations:

- `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).

- `query`: The query operation is used for SELECT statements, which return both the metadata and the retrieved data in a form of an array of row values.

- `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.

## Authentication profiles

Available authentication profiles:

- Connection string

- Azure AD: Client credentials

- Azure AD: Client certificate

- AWS: Access Key ID and Secret Access Key

- AWS: Assume IAM Role

- AWS: IAM Roles Anywhere

### Connection string

Authenticate using a Connection String

#### `connectionString` (string)

**Required** - The connection string for the PostgreSQL database

Example value:

```
user=dapr password=secret host=dapr.example.com port=5432 dbname=dapr sslmode=verify-ca
or postgres://dapr:secret@dapr.example.com:5432/dapr?sslmode=verify-ca
```

More details: https://docs.dapr.io/reference/components-reference/supported-bindings/postgres/#url-format

### 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 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)

**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 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

#### `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`

#### `accessKey`

AWS access key associated with an IAM account

Example value: `AKIAIOSFODNN7EXAMPLE`

#### `awsAccessKey` (string)

Deprecated as of Dapr 1.17. Use 'accessKey' instead if using AWS IAM. If both fields are set, then 'accessKey' value will be used. AWS access key associated with an IAM account.

Example value: `AKIAIOSFODNN7EXAMPLE`

#### `awsRegion` (string)

This maintains backwards compatibility with existing fields. It will be deprecated as of Dapr 1.17. Use 'region' instead. The AWS Region where the AWS resource is deployed to.

Example value: `us-east-1`

#### `awsSecretKey` (string)

Deprecated as of Dapr 1.17. Use 'secretKey' instead if using AWS IAM. If both fields are set, then 'secretKey' value will be used. The secret key associated with the access key.

Example value: `wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY`

#### `region` (string)

The AWS Region where the AWS resource is deployed to. This will be marked required in Dapr 1.17.

Example value: `us-east-1`

#### `secretKey`

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`

### AWS: Assume IAM Role

Assume a specific IAM role. Note: This is only supported for Kafka and PostgreSQL.

#### `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`

#### `region` (string)

**Required** - The AWS Region where the AWS resource is deployed to.

Example value: `us-east-1`

#### `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`

#### `assumeRoleArn` (string)

IAM role that has access to AWS resource. This is another option to authenticate with MSK and RDS Aurora aside from the AWS Credentials. This will be marked required in Dapr 1.17.

Example value: `arn:aws:iam::123456789:role/mskRole`

#### `sessionName` (string)

The session name for assuming a role.

Default value: `DaprDefaultSession`

Example value: `MyAppSession`

### AWS: IAM Roles Anywhere

Use AWS IAM Roles Anywhere to establish trust between your AWS account and Diagrid.

#### `assumeRoleArn`

**Required** - ARN of the AWS IAM role to assume in the trusting AWS account.

Example value: `arn:aws:iam:012345678910:role/exampleIAMRoleName`

#### `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`

#### `trustAnchorArn`

**Required** - ARN of the AWS Trust Anchor in the AWS account granting trust to the Dapr Certificate Authority.

Example value: `arn:aws:rolesanywhere:us-west-1:012345678910:trust-anchor/01234568-0123-0123-0123-012345678901`

#### `trustProfileArn`

**Required** - ARN of the AWS IAM Profile in the trusting AWS account.

Example value: `arn:aws:rolesanywhere:us-west-1:012345678910:profile/01234568-0123-0123-0123-012345678901`

#### `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`

## Metadata

### `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`

### `database` (string)

The database of the PostgreSQL database

Example value: `postgres`

### `host` (string)

The host of the PostgreSQL database

Example value: `mycompany.com`

### `hostaddr` (string)

The host address of the PostgreSQL database

Example value: `127.0.0.1`

### `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`

### `password` (string)

The password of the PostgreSQL database

Example value: `password`

### `port` (string)

The port of the PostgreSQL database

Example value: `5432`

### `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

### `sslRootCert` (string)

The path to the SSL root certificate file

Example value: `/path/to/ssl/root/cert.pem`

### `timeout` (duration)

Timeout for all database operations.

Default value: `20s`

Example value: `30s`

### `user` (string)

The user of the PostgreSQL database

Example value: `postgres`