postgresql_request plugin

The postgresql_request plugin sends a SQL query to an Azure PostgreSQL Server network endpoint and returns the first rowset in the results. The query may return more than one rowset, but only the first rowset is made available for the rest of the Kusto query.

The plugin is invoked with the evaluate operator.

Syntax

evaluate postgresql_request ( ConnectionString , SqlQuery [, SqlParameters] ) [: OutputSchema]

Parameters

Authentication and authorization

To authorize a PostgreSQL Server network endpoint, you must specify the authorization information in the connection string. The supported authorization method is via username and password.

Set callout policy

The plugin makes callouts to the PostgreSQL database. Make sure that the cluster’s callout policy enables calls of type postgresql to the target PostgreSqlDbUri.

The following example shows how to define the callout policy for PostgreSQL databases. We recommend restricting the callout policy to specific endpoints (my_endpoint1, my_endpoint2).

[
  {
    "CalloutType": "postgresql",
    "CalloutUriRegex": "my_endpoint1\\.postgres\\.database\\.azure\\.com",
    "CanCall": true
  },
  {
    "CalloutType": "postgresql",
    "CalloutUriRegex": "my_endpoint2\\.postgres\\.database\\.azure\\.com",
    "CanCall": true
  }
]

The following example shows a .alter callout policy command for postgresql CalloutType:

.alter cluster policy callout @'[{"CalloutType": "postgresql", "CalloutUriRegex": "\\.postgresql\\.database\\.azure\\.com", "CanCall": true}]'

Username and password authentication

The postgresql_request plugin only supports username and password authentication to the PostgreSQL server endpoint and doesn’t integrate with Microsoft Entra authentication.

The username and password are provided as part of the connections string using the following parameters:

User ID=...; Password=...;

Encryption and server validation

For security, SslMode is unconditionally set to Required when connecting to a PostgreSQL server network endpoint. As a result, the server must be configured with a valid SSL/TLS server certificate.

Specify the network endpoint

Specify the PostgreSQL network endpoint as part of the connection string.

Syntax:

Host = FQDN [Port = Port]

Where:

• FQDN is the fully qualified domain name of the endpoint.
• Port is the TCP port of the endpoint.

Examples

SQL query to Azure PostgreSQL DB

The following example sends a SQL query to an Azure PostgreSQL database. It retrieves all records from public."Table", and then processes the results.

evaluate postgresql_request(
    'Host=contoso.postgres.database.azure.com; Port = 5432;'
    'Database=Fabrikam;'
    h'User Id=USERNAME;'
    h'Password=PASSWORD;',
    'select * from public."Table"') : (Id: int, Name: string)
| where Id > 0
| project Name

SQL query to an Azure PostgreSQL database with modifications

The following example sends a SQL query to an Azure PostgreSQL database retrieving all records from public."Table", while appending another datetime column, and then processes the results. It specifies a SQL parameter (@param0) to be used in the SQL query.

evaluate postgresql_request(
    'Server=contoso.postgres.database.azure.com; Port = 5432;'
    'Database=Fabrikam;'
    h'User Id=USERNAME;'
    h'Password=PASSWORD;',
    'select *, @param0 as dt from public."Table"',
    dynamic({'param0': datetime(2020-01-01 16:47:26.7423305)})) : (Id: int, Name: string, dt: datetime)
| where Id > 0
| project Name

SQL query to an Azure PostgreSQL database without a query-defined output schema

The following example sends a SQL query to an Azure PostgreSQL database without an output schema. This is not recommended unless the schema is unknown, as it may impact the performance of the query

evaluate postgresql_request(
    'Host=contoso.postgres.database.azure.com; Port = 5432;'
    'Database=Fabrikam;'
    h'User Id=USERNAME;'
    h'Password=PASSWORD;',
    'select * from public."Table"')
| where Id > 0
| project Name